How to achieve this using SPT? 2

[Yosua]

Hi,
How to achieve this (currently using RemoteView) using SQLPassThrough. I'm using MySQL 5.5
There are SQL and VFP "IF" side by side.
I post the listing in pastebin to save some space

http://pastebin.com/8xQTe5xc

Thanks for your solution and feedback.

Yosua Wisnu

 

[Olaf Doschke]

I like it pastebin could become valuable for geving feedback right there through a new paste version.

I don't have the time right now, but in general you can do the same with SQLPasstrhrough as with Views, the change merely is replacing the USE vBook line to instead SQLEXEC() some query and make the result cursor updatable, so you can use the following code as is, specifically the Tableupdate().

An example on How to make an SPT result cursor updatable can be found here:

http://nowiremember.wordpress.com/2010/01/06/create-a-spt-and-make-it-updatable/

You mainly have to make some cursorsetprop:

cursorsetprop(“Buffering”,5)
cursorsetprop(“SendUpdates”,.t.)
cursorsetprop(“Keyfieldlist”,”your key field(s)”)
cursorsetprop(“Tables”, "sql server table name")
cursorsetprop(“UpdateNameList”,”field ” + lcTable+”.field”)
cursorsetprop(“UpdatableFieldList”,”list of updatable fields”)

So in short these are all the settings you now have set in your remote view definition. Nothing extraordinary new.

Bye, Olaf.

 

[JRB-Bldr]

You can work out the MySQL Query command syntax on your own.
If you need help with that, ask on a MySQL forum area.

After that you can then check it for accuracy and successful execution within the MySQL Management interface without the use of VFP.

Once you know what the SQL Query syntax needs to be then you can put it into VFP.

* --- Build Your Tested SQL Command Syntax - Something Like... ---
* cSQLQuery = "SELECT * FROM MyTable WHERE CustomerID = 'GEROGE'"
*

cSQLQuery = <whatever you need it to be>

* --- If you need to return a record set to VFP ---
nRet = SQLEXECUTE(nConnHandle,cSQLQuery,'Results')

* --- If no return record set needed (such as Update/Insert,etc.) ---
nRet = SQLEXEC(nConnHandle,cSQLQuery)

* --- Then... ---
IF nRet = 1
  * --- Successful Execution ---
  <do whatever>
ELSE
  * --- NOT Successful Execution ---
  <do whatever>
ENDIF

Good Luck,
JRB-Bldr

 

[Yosua]

Thanks Olaf and JRB-Bldr for your quick replies,
How can I forgot about those cursors.
I think I've got the idea about cursorsetprop, thanks a lot.

Yosua Wisnu

 

[Olaf Doschke]

as an add on: If you open your remote view in view designer (in vfp9) and change to the SQL window (see toolbar) you get an idea of the cursorsetprop needed, because the sql window shows the code to programmatically generate the remote view including DBSetProp().

DBSetprop() calls unfortunately don't compare to Cursorsetprops, but you get the infos needed (eg what fields should be updatable etc).

Bye, Olaf.

 

[JRB-Bldr]

If your VFP Database vsqldb already has a defined Connection to your MySQL Server (maybe named 'MySQL') then you can just do something like:

OPEN DATABASE vsqldb

* --- Establish Connection Handle Thru VFP Database Connection ---
nConnHandle = SQLCONNECT("MySQL")

* --- Build SQL Query String ---
cSQLQuery = <whatever you need it to be>

* --- Execute the SQL Query command 'thru' the Connection ---
nRet = SQLEXECUTE(nConnHandle,cSQLQuery,'Results')

* --- If no return record set needed (such as Update/Insert,etc.) ---
nRet = SQLEXEC(nConnHandle,cSQLQuery)

* --- Then... ---
IF nRet = 1
  * --- Successful Execution ---
  <do whatever>
ELSE
  * --- NOT Successful Execution ---
  <do whatever>
ENDIF

* --- Disconnect From Remote MySQL ---
=SQLDisconnect(0)

You should be able to do what you need through SPT only - No need to create a VFP Remote View and configure its associated CursorSetProp() criteria.

Good Luck,
JRB-Bldr

 

[Yosua]

Thanks Olaf, so it's better when I made cursorsetprop() and should I remove the DBsetprop() fater create it?
So JRB-Bldr what I need is just the *.dbc and a connection which is already configured before? that's great.

 

[JRB-Bldr]

Remember that first you need to define a DSN ODBC to your MySql Server within your Windows workstation(s).
Settings - Control Panel - Administrative Tools - Data Sources (ODBC) - System DSN tab.

Then within your VFP Database you can use that defined ODBC DSN to configure a Connection which can be used with your SPT.

Since I typically already have VFP Database Connections defined for other purposes, I use this method the most.

However, an alternative to using the VFP Database Connection would be to make a direct connect through your ODBC DSN within your code.
Something like:

=SQLSETPROP(0,"DispLogin",3)  && Hide Login Window

cConnString = "DSN=ThisSQLServer;Provider=MSDASQL.1;Password=xxxxx;Persist Security Info=True;User ID=AAAAAAAA;Data Source=ThisSource"

nConnHandle = SQLSTRINGCONNECT(cConnString)

<then so on with your other SQL Query code...>

NOTE - The above code is NOT for a MySQL Server.

Instead you will need to create your own MySQL Server Connect String (cConnString) based on that server's own criteria.
( see:

http://www.connectionstrings.com/mysql

)

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

so it's better when I made cursorsetprop() and should I remove the DBsetprop() fater create it?

Cursorsetprop is not better or worse than Cursorsetprop(). It's just cursorsetprop sets cursor properties, and dbsetprop sets database properties, for example remote view properties, as in this case.

You have to defnie the same things for remote views as for spt cursors: What field is a key, what fields should be updataable, etc.

That's all I'm saying.
The DBsetprops you can see in the view designer just help to make the same settings with cursorsetprop for spt cursors.

The general idea of this in contrast to jrbbldrs way is, that after making that setting a SPT cursor has the exact same purpose as a remote view cursor: You can change it and write back changes to the remote database via tableupdate() instead of creating INSERT, UPDATE and/or DELETE sql statements.

Bye, Olaf.