What next command/ function to append records in the sql table back-end

[keisha1]

Can you please correct my script.
I have script/program to append data records in sql table but upon executed & checking from sql table no records inserted but connection string below was successfully connected, what I've missed in the commands or functions.

Set status off
set echo off

LOCAL hConn && Connect to an ODBC data source
hConn =SQLStringConnect("DSN=APRODSALES;Driver=SQL Server;Server=(local);UID=ghie;Database=master")

&& Execute SQL command
LOCAL nResult
nResult = SQLEXEC(hConn,"USE master")
nResult=sqlexec(hConn,"SELECT * FROM prodsales","MPRODSALES")

&&Insert a record in sql table
nResult=SQLSetProp(hConn, "Transactions",1)
nResult=SQLEXEC(hConn,"INSERT INTO prodsales(PRODCODE,PRODDESC,QUANTITY,UNITPRICE)VALUES('0004','Tire,4,3500.00) ");

nResult=TABLEUPDATE()
nResult=SQLCommit(hConn)
BROWSE


Thanks
appreciate your reply

 

[Mike Lewis]

To answer this question, we need one vital piece of information: What back-end database are you addressing? You refer to a "SQL table". But that could mean MySQL, SQLServer, Oracle or even Access or VFP. The point is that every back-end has its own dialect and other variations, and what works in one might not work the same in another.

For example, you are sending "USE Master" as your first command. In Visual Foxpro, that command would open a table. But in Microsoft SQL Server, it establishes a default database (which in any case would be redundant because the database is specified in your connection string).

You also should be checking the return value from SQLEXEC(). There is no point in storing that value and not testing it. The point is that if the value is negative, it means something has gone wrong with the command that you are sending. You could then call AERROR() to determine exactly what the error is. You need to do that after every call to a SQL function.

I suggest you do that as your next step, then let us know what you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike has a major point in not checking for errors.

SQLEXEC executes something remotely, whether that succeeded or not you have to check whether nResult>=0.

You seem to think errors will trigger VFPs own error handling, but if you execute into a completely separate process, maybe even on a remote PC that won't raise error exception in your VFP process.

You have a simple error in your SQL starting a string [tt]'Tire[/tt] forgetting the single quote string end delimiter.

Besides, there is no point in trying TABLEUPDATE without necessary cursor properties set for the workarea. By default, anything you query with SQLEXEC is read-write just like a SQL query result you do in native VFP with READWRITE keyword, but it's not updateable in the sense of writing back to the source. Just like editing a cursor resulting from INTO CURSOR xyz READDWRITE does not save changes back to the source DBF.

Adding a transaction also doesn't help that, if you want to make use of an updatable cursor you have to dig into the necessary CURSORSETPROP settings and programming for remote data access. If you want that, you don't want to SQLEXEC INSERTs at all. Or you go with SQL only, which can be much less code in situations you don't actually want to edit data coming from remote and saving back changes but only insert new data. So you're also mixing incompatible and opposing ideas on how to handle the remote data access in VFP.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

An alternative way of finding errors is to switch on DispWarnings - at least during your program development. You do that by using SQLSETPROP():

[tt]SQLSETPROP(hConn, 'DispWarnings', .T.)[/tt]

That way, VFP will display an immediate error message when the back-end returns an error. You don't need to call AERROR().

But you should only do that on a temporary basis. Once the application is ready for end users, remove the above line. You don't want users seeing error messages.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[keisha1]

Got it.

Thank you MIKE & OLAF