remote view - MySQL add records?

Hello, I've been using remote views for a variety of DB formats for
years....but never had to write back to the DB....until now that is.

Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP 9.0,
have remote connection and remote view established. But when I add records
to the view (cursor) it doesn't actually write it back to the MySqlDB. Can
I create a remote view, add records then write them back to the MySqlDB or
is there a different process required?

TYIA

=B

Jack
Mon Apr 02 20:02:38 CDT 2007

On Mon, 2 Apr 2007 19:29:33 -0500, "Ben" <nospam@thankyou.com> wrote:

>Hello, I've been using remote views for a variety of DB formats for
>years....but never had to write back to the DB....until now that is.
>
>Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP 9.0,
>have remote connection and remote view established. But when I add records
>to the view (cursor) it doesn't actually write it back to the MySqlDB. Can
>I create a remote view, add records then write them back to the MySqlDB or
>is there a different process required?

You need to call TABLEUPDATE() to cause the records to be written
back.

Also, you need to set various properties of the remote view (via
DBSETPROP(), see Help for DBGETPROP(), the sections for 'Field
properties for views' and 'View properties') to allow VFP to figure
out how to do the update.

Check for an error from TABLEUPDATE() and call AERROR() to get the
reason.

Bernhard
Tue Apr 03 05:24:37 CDT 2007

Hi Ben,

> Hello, I've been using remote views for a variety of DB formats for
> years....but never had to write back to the DB....until now that is.
>
> Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP 9.0,
> have remote connection and remote view established. But when I add records
> to the view (cursor) it doesn't actually write it back to the MySqlDB. Can
> I create a remote view, add records then write them back to the MySqlDB or
> is there a different process required?
If you defined the views with the view designer, just have a look on the page
"Update Criteria".

Regards
Bernhard Sander

Ben
Tue Apr 03 09:51:44 CDT 2007


"Jack Jackson" <jacknospam@pebbleridge.com> wrote in message
news:ji9313hhmlq8ddspuo2m9tkut3nn66e34m@4ax.com...
> On Mon, 2 Apr 2007 19:29:33 -0500, "Ben" <nospam@thankyou.com> wrote:
>
>>Hello, I've been using remote views for a variety of DB formats for
>>years....but never had to write back to the DB....until now that is.
>>
>>Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP 9.0,
>>have remote connection and remote view established. But when I add records
>>to the view (cursor) it doesn't actually write it back to the MySqlDB.
>>Can
>>I create a remote view, add records then write them back to the MySqlDB or
>>is there a different process required?
>
> You need to call TABLEUPDATE() to cause the records to be written
> back.
>
> Also, you need to set various properties of the remote view (via
> DBSETPROP(), see Help for DBGETPROP(), the sections for 'Field
> properties for views' and 'View properties') to allow VFP to figure
> out how to do the update.
>
> Check for an error from TABLEUPDATE() and call AERROR() to get the
> reason.

Maybe I'm missing the trees standing here in the forest but can not get this
to work. Can you have a look at this code and see an issue?

Create Connection conSpider;

datasource Alltrim('Spider');

userid Alltrim('USER');

password Alltrim('PASSWORD')



Create Sql View 'vSpider' ;

remote Connection 'conSpider' ;

as Select * from tinput


If !Used('vSpider')

Use vSpider In 0

Endif

Select vSpider

append blank

replace vSpider.textfield with 'TEXT'

TABLEUPDATE(0,.t.,'vSpider',aError)

use in vSpider

Dan
Tue Apr 03 11:08:13 CDT 2007

That code won't work because you haven't set the keyfieldslist,
updateablefields, etc. in the view's properties. The view doesn't know WHAT
to send to the back end because you haven't told it.

The quickest way to see this requirement is to create a simple view in the
view designer, making sure you set the update criteria properly, and then
run GENDBC.PRG (in the tools directory) on it. There's quite a lot of code
that gets generated for even the simplest of views.

Dan

"Ben" <nospam@thankyou.com> wrote in message
news:461269fe$0$25337$4c368faf@roadrunner.com...
>
> "Jack Jackson" <jacknospam@pebbleridge.com> wrote in message
> news:ji9313hhmlq8ddspuo2m9tkut3nn66e34m@4ax.com...
>> On Mon, 2 Apr 2007 19:29:33 -0500, "Ben" <nospam@thankyou.com> wrote:
>>
>>>Hello, I've been using remote views for a variety of DB formats for
>>>years....but never had to write back to the DB....until now that is.
>>>
>>>Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP
>>>9.0,
>>>have remote connection and remote view established. But when I add
>>>records
>>>to the view (cursor) it doesn't actually write it back to the MySqlDB.
>>>Can
>>>I create a remote view, add records then write them back to the MySqlDB
>>>or
>>>is there a different process required?
>>
>> You need to call TABLEUPDATE() to cause the records to be written
>> back.
>>
>> Also, you need to set various properties of the remote view (via
>> DBSETPROP(), see Help for DBGETPROP(), the sections for 'Field
>> properties for views' and 'View properties') to allow VFP to figure
>> out how to do the update.
>>
>> Check for an error from TABLEUPDATE() and call AERROR() to get the
>> reason.
>
> Maybe I'm missing the trees standing here in the forest but can not get
> this to work. Can you have a look at this code and see an issue?
>
> Create Connection conSpider;
>
> datasource Alltrim('Spider');
>
> userid Alltrim('USER');
>
> password Alltrim('PASSWORD')
>
>
>
> Create Sql View 'vSpider' ;
>
> remote Connection 'conSpider' ;
>
> as Select * from tinput
>
>
> If !Used('vSpider')
>
> Use vSpider In 0
>
> Endif
>
> Select vSpider
>
> append blank
>
> replace vSpider.textfield with 'TEXT'
>
> TABLEUPDATE(0,.t.,'vSpider',aError)
>
> use in vSpider
>
>
>
>

Ben
Tue Apr 03 11:29:12 CDT 2007


"Dan Freeman" <spam@microsoft.com> wrote in message
news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
> That code won't work because you haven't set the keyfieldslist,
> updateablefields, etc. in the view's properties. The view doesn't know
> WHAT to send to the back end because you haven't told it.
>
> The quickest way to see this requirement is to create a simple view in the
> view designer, making sure you set the update criteria properly, and then
> run GENDBC.PRG (in the tools directory) on it. There's quite a lot of code
> that gets generated for even the simplest of views.
>
> Dan

Looked at GenDBC and very confused, so I have to write like 1k lines of code
to write one record to a view?



>
> "Ben" <nospam@thankyou.com> wrote in message
> news:461269fe$0$25337$4c368faf@roadrunner.com...
>>
>> "Jack Jackson" <jacknospam@pebbleridge.com> wrote in message
>> news:ji9313hhmlq8ddspuo2m9tkut3nn66e34m@4ax.com...
>>> On Mon, 2 Apr 2007 19:29:33 -0500, "Ben" <nospam@thankyou.com> wrote:
>>>
>>>>Hello, I've been using remote views for a variety of DB formats for
>>>>years....but never had to write back to the DB....until now that is.
>>>>
>>>>Have a local MySQL installed, hooked up to ODBC (3.51 driver) and VFP
>>>>9.0,
>>>>have remote connection and remote view established. But when I add
>>>>records
>>>>to the view (cursor) it doesn't actually write it back to the MySqlDB.
>>>>Can
>>>>I create a remote view, add records then write them back to the MySqlDB
>>>>or
>>>>is there a different process required?
>>>
>>> You need to call TABLEUPDATE() to cause the records to be written
>>> back.
>>>
>>> Also, you need to set various properties of the remote view (via
>>> DBSETPROP(), see Help for DBGETPROP(), the sections for 'Field
>>> properties for views' and 'View properties') to allow VFP to figure
>>> out how to do the update.
>>>
>>> Check for an error from TABLEUPDATE() and call AERROR() to get the
>>> reason.
>>
>> Maybe I'm missing the trees standing here in the forest but can not get
>> this to work. Can you have a look at this code and see an issue?
>>
>> Create Connection conSpider;
>>
>> datasource Alltrim('Spider');
>>
>> userid Alltrim('USER');
>>
>> password Alltrim('PASSWORD')
>>
>>
>>
>> Create Sql View 'vSpider' ;
>>
>> remote Connection 'conSpider' ;
>>
>> as Select * from tinput
>>
>>
>> If !Used('vSpider')
>>
>> Use vSpider In 0
>>
>> Endif
>>
>> Select vSpider
>>
>> append blank
>>
>> replace vSpider.textfield with 'TEXT'
>>
>> TABLEUPDATE(0,.t.,'vSpider',aError)
>>
>> use in vSpider
>>
>>
>>
>>
>
>

Dan
Tue Apr 03 13:21:08 CDT 2007

"Ben" <nospam@thankyou.com> wrote in message
news:461280e4$0$9000$4c368faf@roadrunner.com...
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
>> That code won't work because you haven't set the keyfieldslist,
>> updateablefields, etc. in the view's properties. The view doesn't know
>> WHAT to send to the back end because you haven't told it.
>>
>> The quickest way to see this requirement is to create a simple view in
>> the view designer, making sure you set the update criteria properly, and
>> then run GENDBC.PRG (in the tools directory) on it. There's quite a lot
>> of code that gets generated for even the simplest of views.
>>
>> Dan
>
> Looked at GenDBC and very confused, so I have to write like 1k lines of
> code to write one record to a view?
>

Or use the view designer and let *it* do all that work for you.

SOMETHING has to tell VFP what needs to be updated and how it should be
updated.

Dan

Ben
Tue Apr 03 13:35:35 CDT 2007


"Dan Freeman" <spam@microsoft.com> wrote in message
news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
> "Ben" <nospam@thankyou.com> wrote in message
> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
>>> That code won't work because you haven't set the keyfieldslist,
>>> updateablefields, etc. in the view's properties. The view doesn't know
>>> WHAT to send to the back end because you haven't told it.
>>>
>>> The quickest way to see this requirement is to create a simple view in
>>> the view designer, making sure you set the update criteria properly, and
>>> then run GENDBC.PRG (in the tools directory) on it. There's quite a lot
>>> of code that gets generated for even the simplest of views.
>>>
>>> Dan
>>
>> Looked at GenDBC and very confused, so I have to write like 1k lines of
>> code to write one record to a view?
>>
>
> Or use the view designer and let *it* do all that work for you.
>
> SOMETHING has to tell VFP what needs to be updated and how it should be
> updated.
>
> Dan
>
Thanks Dan but I tried that. In addition to the coding, I have set up
another view using the designer it I'll be darned if the record won't write.

I would like a code walk-thru on how to query a record from a table in a
MySQL DB using VFP 9.0, append a blank row, write to a field and send the
change back through ODBC. If this is too complicated a task to describe
here let me know a way I can pay you for your time to train (if you'd be
willing). I have spent over 8 hours trying to make this simple task happen
and nothing to show. I don't care about *all* contingencies that need to be
considered, just the basics for a write.

Steps I have down: create connection, create view, manipulate view

Steps I do not understand at all: write the changes back through ODBC.

I fear I may be left hangin on this one and needing to post to guru.com soon
=)

TYIA

- Ben

Ben
Tue Apr 03 13:44:54 CDT 2007


"Ben" <nospam@thankyou.com> wrote in message
news:46129e78$0$27089$4c368faf@roadrunner.com...
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
>> "Ben" <nospam@thankyou.com> wrote in message
>> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>>
>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
[snip]

Actually I think posting to Guru is what I'll do, I learn by watching and
speaking to people much faster than books. Will follow up with a link for
anyone here who might be interested.

Thanks again,

=B

Dan
Tue Apr 03 14:03:08 CDT 2007


"Ben" <nospam@thankyou.com> wrote in message
news:46129e78$0$27089$4c368faf@roadrunner.com...
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
>> "Ben" <nospam@thankyou.com> wrote in message
>> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>>
>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
>>>> That code won't work because you haven't set the keyfieldslist,
>>>> updateablefields, etc. in the view's properties. The view doesn't know
>>>> WHAT to send to the back end because you haven't told it.
>>>>
>>>> The quickest way to see this requirement is to create a simple view in
>>>> the view designer, making sure you set the update criteria properly,
>>>> and then run GENDBC.PRG (in the tools directory) on it. There's quite a
>>>> lot of code that gets generated for even the simplest of views.
>>>>
>>>> Dan
>>>
>>> Looked at GenDBC and very confused, so I have to write like 1k lines of
>>> code to write one record to a view?
>>>
>>
>> Or use the view designer and let *it* do all that work for you.
>>
>> SOMETHING has to tell VFP what needs to be updated and how it should be
>> updated.
>>
>> Dan
>>
> Thanks Dan but I tried that. In addition to the coding, I have set up
> another view using the designer it I'll be darned if the record won't
> write.
>
> I would like a code walk-thru on how to query a record from a table in a
> MySQL DB using VFP 9.0, append a blank row, write to a field and send the
> change back through ODBC. If this is too complicated a task to describe
> here let me know a way I can pay you for your time to train (if you'd be
> willing). I have spent over 8 hours trying to make this simple task
> happen and nothing to show. I don't care about *all* contingencies that
> need to be considered, just the basics for a write.
>
> Steps I have down: create connection, create view, manipulate view
>
> Steps I do not understand at all: write the changes back through ODBC.
>
> I fear I may be left hangin on this one and needing to post to guru.com
> soon =)
>
> TYIA
>

If you created a view with the view designer, and it isn't updating the back
end when you call TableUpdate(), you probably have the update criteria set
wrong. You MUST identify the primary key field, and check other fields as
updateable.

It really is as easy as that.

Dan

Ben
Tue Apr 03 14:28:46 CDT 2007


"Dan Freeman" <spam@microsoft.com> wrote in message
news:Otou4KidHHA.3408@TK2MSFTNGP03.phx.gbl...
>
> "Ben" <nospam@thankyou.com> wrote in message
> news:46129e78$0$27089$4c368faf@roadrunner.com...
>>
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
>>> "Ben" <nospam@thankyou.com> wrote in message
>>> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>>>
>>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
[snip]
>
> If you created a view with the view designer, and it isn't updating the
> back end when you call TableUpdate(), you probably have the update
> criteria set wrong. You MUST identify the primary key field, and check
> other fields as updateable.
>
> It really is as easy as that.
>
> Dan
>

Well somehow I managed to screw it up. Anyway I will need to automate this
and the code is needed. I have posted this training project on Guru dot com
at 250 US, project ID is 286541 if you or anyone here is interested.

You can enter the ID number here:

http://www.guru.com/pro/search_ID.cfm

Thanks again for trying Dan.

=B

Anders
Tue Apr 03 14:48:52 CDT 2007

Open the Update Criteria page in the View Designer.
You have checked the primary or candidate key field(s). And chancked them as
updatable too?
You have checked the other fields as Updatable (unless you specifically want
to exclude any of them from being updatable).
And finally checked the one thing that so often is overlooked, down there
in the bottom leftside corner: SendUpdates.
Save and close the View Designer. You may want to check out "View SQL"
first.
SET MULTILOCKS ON
USE the_view
CURSORSETPROP('Buffering', 3, 'the_view')
BROWSE
*Make changes. As you move to a new row the changes are automatically sent
to the backend, *because of the row-buffering. You can also use TableRevert
or TableUpdate while still on a *modified row. Look up GETFLDSTATE() in
Help.
USE IN the_view && closes
USE the_view && opens and lets you check whethere your modifications are
there.
CURSORSETPROP('Buffering', 5, 'the_view') && table bufferenig
BROWSE
* modify a few rews
?TableRevert(.T., "the_view")
* Your mods are gone!
*make new modifications
?TABLEUPDATE(2, .T. 'the_view')
* .T. for True is returned if succces
*You can apply Xbase or SQL DML to a view. E.g.
UPDATE the_view SET price = price * 1.1 WHERE UPPER(country) = 'FRANCE'
? _TALLY
TableUpdate(2, .T., 'the_view')

-Anders
VFP MVP





"Ben" <nospam@thankyou.com> wrote in message
news:46129e78$0$27089$4c368faf@roadrunner.com...
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
>> "Ben" <nospam@thankyou.com> wrote in message
>> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>>
>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
>>>> That code won't work because you haven't set the keyfieldslist,
>>>> updateablefields, etc. in the view's properties. The view doesn't know
>>>> WHAT to send to the back end because you haven't told it.
>>>>
>>>> The quickest way to see this requirement is to create a simple view in
>>>> the view designer, making sure you set the update criteria properly,
>>>> and then run GENDBC.PRG (in the tools directory) on it. There's quite a
>>>> lot of code that gets generated for even the simplest of views.
>>>>
>>>> Dan
>>>
>>> Looked at GenDBC and very confused, so I have to write like 1k lines of
>>> code to write one record to a view?
>>>
>>
>> Or use the view designer and let *it* do all that work for you.
>>
>> SOMETHING has to tell VFP what needs to be updated and how it should be
>> updated.
>>
>> Dan
>>
> Thanks Dan but I tried that. In addition to the coding, I have set up
> another view using the designer it I'll be darned if the record won't
> write.
>
> I would like a code walk-thru on how to query a record from a table in a
> MySQL DB using VFP 9.0, append a blank row, write to a field and send the
> change back through ODBC. If this is too complicated a task to describe
> here let me know a way I can pay you for your time to train (if you'd be
> willing). I have spent over 8 hours trying to make this simple task
> happen and nothing to show. I don't care about *all* contingencies that
> need to be considered, just the basics for a write.
>
> Steps I have down: create connection, create view, manipulate view
>
> Steps I do not understand at all: write the changes back through ODBC.
>
> I fear I may be left hangin on this one and needing to post to guru.com
> soon =)
>
> TYIA
>
> - Ben
>

Ben
Tue Apr 03 15:12:15 CDT 2007

AHHH HA!! It was that pesky "Send Updates" check box I overlooked. Well if
you want to cash in I'll stay true to my word, you can apply for the job
posting at Guru and I'll award and pay right off =) Might have another few
questions we could discuss off-line too. Otherwise I'll likely leave it up
there and use this as a starting point but still have specific questions too
much to usenet.

Sometimes it's the simplest things JEEZE.

Thank you * 1,000,0000!!!!!


"Anders Altberg" <anders.altberg> wrote in message
news:uva6hkidHHA.4032@TK2MSFTNGP02.phx.gbl...
> Open the Update Criteria page in the View Designer.
> You have checked the primary or candidate key field(s). And chancked them
> as updatable too?
> You have checked the other fields as Updatable (unless you specifically
> want to exclude any of them from being updatable).
> And finally checked the one thing that so often is overlooked, down there
> in the bottom leftside corner: SendUpdates.
> Save and close the View Designer. You may want to check out "View SQL"
> first.
> SET MULTILOCKS ON
> USE the_view
> CURSORSETPROP('Buffering', 3, 'the_view')
> BROWSE
> *Make changes. As you move to a new row the changes are automatically sent
> to the backend, *because of the row-buffering. You can also use
> TableRevert or TableUpdate while still on a *modified row. Look up
> GETFLDSTATE() in Help.
> USE IN the_view && closes
> USE the_view && opens and lets you check whethere your modifications are
> there.
> CURSORSETPROP('Buffering', 5, 'the_view') && table bufferenig
> BROWSE
> * modify a few rews
> ?TableRevert(.T., "the_view")
> * Your mods are gone!
> *make new modifications
> ?TABLEUPDATE(2, .T. 'the_view')
> * .T. for True is returned if succces
> *You can apply Xbase or SQL DML to a view. E.g.
> UPDATE the_view SET price = price * 1.1 WHERE UPPER(country) = 'FRANCE'
> ? _TALLY
> TableUpdate(2, .T., 'the_view')
>
> -Anders
> VFP MVP
>
>
>
>
>
> "Ben" <nospam@thankyou.com> wrote in message
> news:46129e78$0$27089$4c368faf@roadrunner.com...
>>
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:%23GYjazhdHHA.1244@TK2MSFTNGP04.phx.gbl...
>>> "Ben" <nospam@thankyou.com> wrote in message
>>> news:461280e4$0$9000$4c368faf@roadrunner.com...
>>>>
>>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>>> news:e9RNJpgdHHA.4784@TK2MSFTNGP06.phx.gbl...
>>>>> That code won't work because you haven't set the keyfieldslist,
>>>>> updateablefields, etc. in the view's properties. The view doesn't know
>>>>> WHAT to send to the back end because you haven't told it.
>>>>>
>>>>> The quickest way to see this requirement is to create a simple view in
>>>>> the view designer, making sure you set the update criteria properly,
>>>>> and then run GENDBC.PRG (in the tools directory) on it. There's quite
>>>>> a lot of code that gets generated for even the simplest of views.
>>>>>
>>>>> Dan
>>>>
>>>> Looked at GenDBC and very confused, so I have to write like 1k lines of
>>>> code to write one record to a view?
>>>>
>>>
>>> Or use the view designer and let *it* do all that work for you.
>>>
>>> SOMETHING has to tell VFP what needs to be updated and how it should be
>>> updated.
>>>
>>> Dan
>>>
>> Thanks Dan but I tried that. In addition to the coding, I have set up
>> another view using the designer it I'll be darned if the record won't
>> write.
>>
>> I would like a code walk-thru on how to query a record from a table in a
>> MySQL DB using VFP 9.0, append a blank row, write to a field and send the
>> change back through ODBC. If this is too complicated a task to describe
>> here let me know a way I can pay you for your time to train (if you'd be
>> willing). I have spent over 8 hours trying to make this simple task
>> happen and nothing to show. I don't care about *all* contingencies that
>> need to be considered, just the basics for a write.
>>
>> Steps I have down: create connection, create view, manipulate view
>>
>> Steps I do not understand at all: write the changes back through ODBC.
>>
>> I fear I may be left hangin on this one and needing to post to guru.com
>> soon =)
>>
>> TYIA
>>
>> - Ben
>>
>
>