SQL Remote Access

[siddeek]

I have a foxpro billing system, which is updating MS SQL database remotely. Following is a one of SQL INSERT I use when saving the billing;

chkTrn=SQLEXEC(ln,"INSERT INTO trnnumber (serial, _user, dt, module, remark, idn) VALUES ((SELECT ISNULL(MAX(serial)+1,1) FROM trnnumber),?muser, ?mdtUpdate, 'invoice', '', ?midn)")

Then check chkTrn value to make sure where the insert function worked or not. If chkTrn is less than 0, there will be loop function which will keeps on try till the connection back to normal.

My only worry is; will there be any partial update being done during the insertion process, due to loss of the connection. if so how to check it?

Thanks in advance.

 

[Mike Lewis]

The short answer is: Don't worry In general, SQL Server wraps single updates in a transaction, which means that if part of it fails, the whole thing will fail, which is what you want.

But it's not quite that simple. If chkTrn is equal to zero (not less than zero) AND you have set your connection for asynchronous processing, then doing an insert in a loop would be appropriate. But if chkTran is less than zero, it means that SQL Server has reported an error. In that case, doing the update in a loop will only result in the program hanging.

If chkTrn is less than zero, you need to call AERROR() to determine what went wrong. AERROR() generates an array, the third element of which contains the SQL Server error code. Your focus should then be to fix that error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

If you have an error you can't rely on the connection going back to normal.

While all AERRORs you get will be VFP Error 1526 and the error message starts with Connectivity error, this is the ODBC layer telling you the server reported an error, not that the connection is temporarily lost. Error messages usually continue with the driver used and the server error number and message. It's documented in AERROR in detail. So Mike is right, the next step to do is check AERROR.

"Connectivity" is not "Connection" it points to the C in ODBC - Open DataBase Connectivity.

A MSSQL server could close a connection handle due to deadlock or due to too few CALs, which might give a new connection a handle while yours is closed. I think you'll then get a timeout error, not sure.

It's okay to be concerned about an sql handle still being valid, the only practical way is to do SQLEXEC(ln,"Select GetDate() as ServerTime") and check whether that's not returning <0. I also wouldn't worry too much about instable connections, that's a rare case.

But when a connection really is lost, you can't just wait until it returns, you'll need to be proactive and reconnect by SQLConnect or SQLStringConnect.

All that said SQL Server offers Identity columns that count up automatically, using Max(field+1) for a primary key is a bad idea, even thouugh this all hapens in SQL Server with an index on the field and within a transaction, Identity or using a sequence (see CREATE SEQUENCE and related about sequence usage) are the natural ways to get incrementing numeric ID values.

Chriss

 

[siddeek]

Thank your Mike for your valuable comment and advice.
I am checking the connection handle at the beginning for other errors using try and catch method and assume only network related errors could occur during the insert/update process that's why I do this in a loop.

BTW, could you please advice me what sort of errors could occur other than connection failure.

Thanks.

 

[siddeek]

Thank you Chriss,
I am using the max function because the sql auto increment just jump by 1000 for some of my tables, and I could not eliminate this.
Chriss as I mentioned I check for the OBDC related errors while issuing the connection handle using the try and catch then catch the error.

Thanks.

 

[Mike Lewis]

Unfortunately, "checking the connection handle at the beginning" is not going to help in this case. If you mean that SQLCONNECT() is returning less than zero, that probably indicates that the ODBC data source cannot be found, or that the server cannot be contacted. If that happens, you won't get as far as doing the SQLEXEC().

The errors returned by SQLEXEC() are errors that are actually detected by SQL Server. That could include simple syntax errors in your SQL code, or it could be things like invalid column names or table names.

As far as I know, there is no documented list of all these errors (the last time I checked, there were several thousand of them). The actual error codes and messages are stored in a table on the server (master.dbo.sysmessages). But you don't really need to know that. The point is that the error codes and messages are shown in the error array mentioned above, which is why you need to call AERRORS() whenever you get a negative reply from SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

TRY..CATCH will not help you catching any SQL Passthrough problems. Errors are always indicated by negative result, but you always get this result, no exception is raised. And then you need to look into AERROR to get details of the error. That's also true for SQLConnect or SQLStringConnect.

Besides that, I only know two reasons an IDENTITY field would increment by 1000.
Either the IDENTITY is defined with 1000 increment IDENTITY(1,1) will start at 1 and increment by 1.
Or you shutdown the server by killig the task? Or it crashes because of anything else. Then what gets you is the identity cache. See

https://blog.sqlauthority.com/2018/...ll notice that there,which runs on the table.

Chriss