SPT & CA Sending Updates & Limiting Selects 1

[Ed Andres]

I think I have read almost every post here about SQL Passthrough and cursor adapters and I am still left with questions on the best way to do certain things. I understand that many times the answer will be "it depends" but here are a few questions to help me better understand. First, I use SPT very often to get data to populate controls on a form using something like this (keep in mind the code samples will be just examples):
lnConn = GetConnection() && function to get conection
SQLSelect = "SELECT * FROM OEhdr WHERE salesorder = '" + PADL(ALLTRIM(thisform.txtSono.Value),6,' ') + "'"
RetVal = SQLEXEC(lnConn, SQLSelect, 'OE_data')
IF RECCOUNT() > 0
.... Code to fill controls ....
If I make changes to the data in the OE_data cursor, is there an easy way to send updates back to the SQL server or do I have to do an Update transaction? I currently use SQLEXEC to do an Update of all the fields and set them equal to the values in the controls.

I really like using cursor adapters for data, they handle the updates very well but there are instances where the table has a lot of records. Is there a way to parameterize a CA to limit the returned records?

I know these seem like basic questions and I have been using SPT & CA's for quite some time now but, I think I may be doing things the hard way. I am using VFP9sp2 on WindowsXP.

TIA for any input or guidance.

Ed

 

[DougHennig]

Hi Ed.

Yes, you can parameterize CAs the same way you do a SPT statement: adding a parameterized WHERE clause to the SelectCmd property. For example:

loCA.SelectCmd = "SELECT * FROM OEhdr WHERE salesorder = '" + PADL(ALLTRIM(thisform.txtSono.Value),6,' ') + "'"
if loCA.CursorFill()
* worked
else
* failed
endif

Doug

 

[Olaf Doschke]

Yes, it's that simple, you just change the SelectCmd.

If you created a CA class with the CursorAdapterBuilder The Select Command Builder is not the best thing about it, it's not even a shadow of the view designer, but you can add to the Select command.

And you could change the SelectCmd in the Method BeforeCursorFill() by changing the cSelectCmd and afterwards calling DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd).

Bye, Olaf.

 

[Ed Andres]

Thanks for the replies, I will certainly give the parameterized CA a try. How about the first question? Any takers?

Ed

 

[Olaf Doschke]

Hi,

okay, there is a way to make an SPT cursor updatable and write back to sql server via Tableupdate(). You set "SendUpdates", "Tables", "Keyfields", "Updatablefields" and some more with Cursorsetprop() on the SPT cursor. In cases this might be worth the hassle and be more flexible than a cursoradapter.

See here in the section "Option 3: Making SPT cursors updateable" -

http://my.advisor.com/Articles.nsf/aid/FALIJ01

Bye, Olaf.

 

[Ed Andres]

Olaf,
Thanks for the link and info.
I will do some more digging and see what works best for me.

Ed

 

[Ed Andres]

Ok, I must be the biggest idiot on the planet. I can't seem to get this to work. My goal is to pass a parameter to the form and then use it in the Select. Here is where I am; I created a form using the project manager, new. Added a text box to the form and made the value of it 10087. I added a cursor adapter to the data environment of the form with the alias Cursor1. The default select I used is SELECT * FROM Person, this gets all records from the Person table and this works. What I would like to do is have the select look like this, SELECT * FROM Person WHERE Person_ID = Thisform.Text1.Value. If I can get this to work, I think I can pass the parameter ok.

I have tried putting this in the BeforeCursorFill() of the cursor adapter with no success

LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
cSelectCmd = "select * from person where person_id = " + ALLTRIM(STR(thisform.text1.Value))
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd)

If this is in there, it works

LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
cSelectCmd = "select * from person where person_id = 10087"
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd)

I guess I'm not sure where to change the SelectCmd of the CA and how make it work. I think the data environment is loading before the form controls so how do I make this work.

Sorry for what probably is a very elementary question.

Ed

 

[Olaf Doschke]

The problem

The init of the form is not the first thing that runs. First comes Load(), the dataenvironment and the init of all controls etc. on the form is also previous to the forms init().

The way it works is, you use a table, view or CA in the DE with no data on load, if you want to make use of the init() parameters, and then do requeries in the init.

It's designed that way so you can access everything on the form from it's init(). It's not as you would expect it to be.

Bye, Olaf.

 

[Ed Andres]

Here is what I have ended up doing to get the desired results.

I created a CA class and then in a new method of my form I put this:

thisform.AddProperty([MyCursor])
thisform.MyCursor = NewObject([LeakstdByID],[\libs\cts_olab_app])
thisform.MyCursor.SelectCMD = "SELECT * FROM LeakSTD WHERE LeakSTD.ID = '" + ALLTRIM(thisform.lsid) + "'"
thisform.MyCursor.CursorFill()
REQUERY()

Then in the init of the form, I call this method and it works great.

Thanks Olaf and Doug for your patience and help. This will likely change the way I handle data from now on.

Ed

 

[Olaf Doschke]

If you just create the CA in that method, set SelectCmd and call CursorFill, you have no need for REQUERY().

Bye, Olaf.

 

[Ed Andres]

Thanks Mike, that is good to know. I had never really heard of that before. I'm curious to know how it is faster?

Ed