Sql Statement In Foxpro

[JICGreg]

I have had foxpro and sql work together for a while now, but only in the very,very simplest of terms. I basically delete data in a sql table and add it back in using a foxpro view.

I now have to query the sql table. I am trying to do this within a foxpro program.

So here is what I have:

store "returns" to sql_source_table
store "ABCD" to ticker

lnconn = "sqlconnect("JICData",.T.)
STORE "SELECT FROM * " + sql_source_table + " where ticker = " + "'" + ticker + "'" TO table_SELECT

sqlexec(lnconn,table_select)

What I think I'm sending to sql is: 'SELECT FROM RETURNS WHERE TICKER = 'ABCD'

My first question does this look correct? I know that query will work in sql management studio.

My second question is how can I tell if it works? There has to be a way to see the records that are returned. At first I thought I would simply say view_returns (the remote view name I have been using) but, while that brings up the remote view, it is not specific to the query (in other words it is all data).

I do not really know anything about cursors but that seems to be one way to 'see' the results of the query. I added the phrase into cursor temp after the WHERE TICKER = ABCD phrase. When I issued the sqlexec command, it did not bomb, but likewise no cursor window popped up to let me see the results. I also added browse right after the sqlexec line and that did not do anything.

Any help would be greately appreciated.

Greg

 

[JRB-Bldr]

You will want to execute your SQL Query commands within the SQL Server backend by using the VFP SQLEXEC() command.

On a VERY general basis it would be something like:

* --- Acquire a Connection Handle ---
lnconn = sqlconnect("JICData")
* --- Build SQL Server syntax UPDATE Command ---
cSQLCommand = "UPDATE ThisSQLTable SET ThisField = 5 WHERE OtherField = 'AAAA'"
* --- Execute Command ---
nRet = SQLEXEC(lnConn,cSQLCommand)
IF nRet = 1
   * --- Successful Execution ---
   * <do whatever>
ELSE
   * --- NOT Successful Execution ---
   * <do whatever>
ENDIF

* --- Drop Connection To SQL Server ---
=SQLDisconnect(0)

You can get more info by going to your VFP Command Window and typing Help SELECT - SQL

Plus by doing a Google search for VFP "SQL Server" you will find a number of additional references.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

Well, according to the code you've posting here, you're sending

SELECT FROM * returns

Surely that's just a typo in your post, and not in your actual code?

Don't stumble over the term cursor. It's just a set of records like any other opened DBF. All data coming back from a remote data source will land in a cursor.

In fact, ANY query to a back end will beget a cursor. Even an update SQL statement will at least beget a one-record cursor identifying the number of rows affected. If you're not getting any kind of cursor, use AERROR() to find out what went wrong.

 

[Mike Lewis]

Greg,

To add to the good advice you've already been given ....

You asked how to "see the records that were returned." If you are running this in the VFP development environment, just do this:

SELECT SQLResult
BROWSE

SqlResult is the name of the cursor that will contain the records. It behaves just like a VFP table.

In a program, you could do this:

IF RECCOUNT("SQLResult") = 0
  * No data returned
ELSE
  * Some data returned
ENDIF

However, that doesn't tell you if the SQL command actually worked. It only tells you if it returned any data. To find out if the command was valid, use JRB-Bldr's code, but include a call to AERROR():

nRet = SQLEXEC(lnConn,cSQLCommand)
IF nRet = 1
   * --- Successful Execution ---
   * <do whatever>
ELSE
   * --- NOT Successful Execution ---
   AERROR(laError)
   IF laError(1) = 1526
     * Error WAS returned from back end
     MESSAGEBOX(laError(3))  && Display the error message
  ENDIF 
ENDIF

Next point: When asking questions about accessing a back end from VFP, please PLEASE tell us the back end you are using. In this case, you mentioned Management Studio, from which I would guess you are using SQL Server, but that might not be the case. The syntax of SQL commands varies with the back end, and it's much easier to give advice if we don't have to guess which one you are using.

Dan, you said that a query "will always begat a cursor". That might be true with some back ends, but I don't think it's the case with SQL Server. An UPDATE will only send back the number of affected rows if you explicitly ask it to (by interrogating @@ROWCOUNT, for example).

By the way, I like the use of "begat" in this context. I'll be using it myself from now on.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Mike,

Right. My point was that the only way VFP has of receiving data from a back end is in a cursor. It will always be a cursor. It will never NOT be a cursor. There's nothing special here!

 

[JICGreg]

Thank you for your response:

Dan:

You are correct that is not what I intended. But at
jrbbldr's recommendation, I added the code to test whether the connection and execution was successful. It was, so now I know I have a "good" string. I issued the brows command after sqlexec() and voila! there is the data.

First, thank you very much for your help. I would like to ask another question if I could.

So now I have this connection with sql and the records that I want. If I make a change to a record, I suppose the best way to make sure it is "committed" to sql is to create sql update string. Correct?

My sense is that tableupdate() would not be appropriate, because I think that is only for remote views, but I'm not sure.

Thanks again.


Greg

 

[Dan Freeman]

That's correct.

TECHNICALLY you can set your SQLExec() result up to behave exactly like a view, but there's so much code involved (and therefore maintenance overhead) if you're going to do that why not use a view in the first place? ;-)

Just send the UPDATE.

 

[JRB-Bldr]

Note - you can also specify the name of the returned cursor in your SQLEXEC() command if you want so that you 'know' what its name will be for future referencing.

Something like:

* --- Acquire a Connection Handle ---
lnconn = sqlconnect("JICData")
* --- Build SQL Server syntax SELECT Command ---
cSQLCommand = "SELECT * FROM ThisSQLTable WHERE OtherField = 'AAAA'"
* --- Execute Command ---
nRet = SQLEXEC(lnConn,cSQLCommand,'Result')
IF nRet = 1
   * --- Successful Execution ---
   SELECT Result
   * <do whatever>
ELSE
   * --- NOT Successful Execution ---
   * <do whatever>
ENDIF

* --- Drop Connection To SQL Server ---
=SQLDisconnect(0)

Keep in mind that the query syntax needs to be that of the SQL Server backend - not necessarily the same as might work on a VFP data table.
Certain VFP functions what are very convenient to use are not always supported by 'non-native' backends such as SQL Server, MySQL, Informix, etc.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Normally you'd simply start a new thread for a new question, but as this is connected to SQLEXEC here, I'll go for that:

The cursor you retrieve with SQLEXEC() - by the way: Take a look at SQLEXEC help and you'll find you can set the name of the return cursor.

Th result cursor is not connected to sql server like a remote view, therefore you are right tableupdate() will not update the SQL Server table.

But you can make an SQLEXEC result cursor an updatable remote cursor, you need to set a few properties, but it pays, if you want a simple way to save changes.

http://support.microsoft.com/kb/138094/en-us

This KB article describes how to turn a cursor created by SQLEXEC to be updatable towards the backend it came from and therefore make it an updatable view.

Otherwise you're also right, you can update records by sending an UPDATE sql query, eg

lcSQL = 'update returns set field1=?sqlresult.field1 where id =?sqlresult.id'
Select sqlresult
Scan
   Sqlexec(lnHandle, lcSQL)
Endscan

? within the SQL String makes VFP pass the expressions after the ? as parameters. In this case sqlresult.field1 and sqlresult.id, of course this needs to be adjusted to real names.

The loop will scan through all records in an cursor alias "sqlresult" and store back changes to field1. This is slow in comparison to Tableupdate(), even if you optmize the loop to only call SQLEXEC in cases of a change in the record.

Also you'll need another loop and another lcSQL with an INSERT statement for new records and then antoher one for deleteions.

As you're an SQL novice, it would pay to go through the different SQL statements just to learn a bit more SQL before you make use of updatable cursors, but in the end you'll get quite far with just the SQL-SELECT and do insert, update and delete via TABLEUPDATE().

Bye, Olaf.