How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

ic2 · Post by **ic2** » Fri Jun 11, 2021 2:34 pm

I have I used this code for years: first I retrieve SQL queries using WCF and then I execture the query using ADS to add/modify a DBF:

Code: Select all

nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable) // ACE.AE_SUCCESS
		If nStatus==0
			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt) // ACE.AE_SUCCESS
			If nStatus=0
				nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)
				If nStatus<>0
					pBuf := Char[]{256}
					nLastError:=ACE.AdsGetLastError(pError, pBuf,pBufLen)
.....

For 1 client I had a couple of SQL statements which did append a record but nStatus returned 7200. I looked up 7200 and saw:

https://devzone.advantagedatabase.com/d ... _error.htm

Problem: The problem might be caused by an SQL statement error or some other limitation in the way Advantage processes SQL statements.
Solution: Use AdsGetLastError to retrieve a detailed description of the error.

So I checked ADSGetLastError and this was 0, which is the same result as trying the query from within the Advantage Data Architect, which worked. Incorrectly, my program hence didn't return a successful addition and the record was being added every time again.

Now the weird thing. When I lookup the return value from AdsCreateSQLStatement in the help it says:

After the SQL statement has been executed a cursor handle will be returned. This cursor handle provides access to the rowset returned.

Now I first can't imagine that I have interpreted the return value to be 0 on success without a good reason and second, it most statements return 0 and the faulty one 7200 which doesn't look to me like a cursor but it does look like the ADS error code.

In short: I wonder how I can determine for sure that an AdsCreateSQLStatement actually added a record in DBF. For now i keep checking the return value: if this is 0 or it is non zero but GetLastError is 0 then I consider the record added successfully.

But is this the best way?

Dick

ic2 · Post by **ic2** » Sat Jun 12, 2021 6:39 am

Addition to the above: I've done some tests and it is certainly not the best method because:

1 When everything works fine, AdsExecuteSQLDirectW returns 0. Fine so far.
2 When I misspell a field name, AdsExecuteSQLDirectW returns indeed 7200. But AdsGetLastError returns 0 so despite what the documentation says this doesn't help.
3 When I change the WHERE to a non existing key, AdsExecuteSQLDirectW still returns 0, so the return value can certainly not be used to determine a successful addition.

Addition: I've also tried Transactions, see code below. But when trying the query mentioned in 3, which can not possibly be executed, the program did not reach the Catch but simply continued with the Commit.

I am under the impression that a lot of things do not work in ADS as expected. So I am still without a reliable method to know if a query was executed successfully.

Code: Select all

	nStatus := ACE2.AdsConnect101( cConnectStr, Null_object,Ref hConn)
	If nStatus== 0
		cDbfFullPath:=cDataPath+cDBF+".dbf"
		nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable)
		If nStatus==0
			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt)
			If nStatus=0
				ace.AdsBeginTransaction(hConn)
				Try
					nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)
					ace.AdsCommitTransaction(hConn)
				Catch
					// Consider the  Query failed and do something, one of the options being could be ace.AdsRollbackTransaction(hConn)
				End

I hope someone using ADS has a proven better method.

Dick

Jamal · Post by **Jamal** » Sat Jun 12, 2021 5:25 pm

Dick,

Try calling/checking AdsGetLastError() result immediately after
nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt)

"According to the ADS docs, AdsGetLastError() gets cleared after every Ads function/method call:

https://devzone.advantagedatabase.com/d ... terror.htm

"The error code returned by this function will be the same as the one returned by the last Advantage Client Engine function call. The first action of each Advantage Client Engine function is to clear the previous error if there is one. Thus, a call to AdsGetLastError is valid only for the most recent function call as opposed to the most recently occurring error. If no error occured on the last Advantage Client Engine function call, AE_SUCCESS is returned in pulErrCode."

HTH,
Jamal

ic2 · Post by **ic2** » Sat Jun 12, 2021 5:57 pm

Hello Jamal,

Thanks for the suggestion, but I already tried multiple combinations and AdsGetLastError doesn't do anything. It returns 0 in the code below and even in both situations I described above (non existing WHERE and using a non existing field, only in the latter case ADSExecuteSQLDirect return 7200 instead of 0).

No idea if there's something else wrong in the code which I miss or if ADS is full of non working methods....

Dick

Code: Select all

	If nStatus== 0 
		cDbfFullPath:=cDataPath+cDBF+".dbf"
		nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable) 
		If nStatus==0
			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt) 
			If nStatus=0
				ace.AdsBeginTransaction(hConn)
				Try
					pBuf := Char[]{256}
					pError:=0
					pBufLen:=256
					nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)	
					nLastError:=ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
					ace.AdsCommitTransaction(hConn)
				Catch
					// Consider the  Query failed and do something, one of the options being could be ace.AdsRollbackTransaction(hConn)
				End

Jamal · Post by **Jamal** » Mon Jun 14, 2021 4:30 am

Dick,

Try using AdsVerifySQL / AdsVerifySQLW to verify the SQL statement before executing it, then after AdsExecuteSQLDirectW() call AdsGetRecordCount() to see how many rows affected.

Note: I don't have Advantage to test with.

HTH,
Jamal

ic2 · Post by **ic2** » Mon Jun 14, 2021 2:19 pm

Hello Jamal,

Jamal wrote: Try using AdsVerifySQL / AdsVerifySQLW to verify the SQL statement before executing it, then after AdsExecuteSQLDirectW() call AdsGetRecordCount() to see how many rows affected.

Not for the first time you've solved a problem for me which took me much time until your reply. And some time to get it all working

Thank you so much!

Problem is that some of the ADS methods return the input you want to know as a return value, others via a reference variable and some both.
In short:
- AdsVerifySQL returns 7200 if something is wrong in the statement (but so does AdsExecuteSQLDirectW and this same 7200 can be found in pError from the statement ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
- If the statement's syntax and fields are correct but the update wasn't done for whatever reason, ACE.AdsGetRecordCount(hStmt,2,Ref ulCount) will return 0 in ulCount if I pass hStmt which was retrieved from ACE.AdsCreateSQLStatement(hConn, Ref hStmt) .

So this seems to work (and Transaction does not, I do not know why not yet)

Code: Select all

	nStatus := ACE2.AdsConnect101( cConnectStr,Ref hConnOptions,Ref hConn)
	If nStatus== 0 // ACE.AE_SUCCESS
		cDbfFullPath:=cDataPath+cDBF+".dbf"
		nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,Ref hTable)
		If nStatus==0
			nStatus := ACE.AdsCreateSQLStatement(hConn, Ref hStmt)
			If nStatus=0
				nStatus:=Ace.AdsVerifySQL(hStmt,cSQLStatement)
				If nStatus<>0 // eg 7200 something is wrong in the statement
					ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
					ACE.AdsCloseSQLStatement( hStmt)
					ACE.AdsDisconnect( hConn)
					Return False
				Endif
				ace.AdsBeginTransaction(hConn)		// Transaction does not seem to work
				Try
					pBuf := Char[]{256}
					pError:=0
					pBufLen:=256
					nStatus := ACE2.AdsExecuteSQLDirectW(hStmt, cSQLStatement, Ref hPtr)
					ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
					ACE.AdsGetRecordCount(hStmt,2,Ref ulCount)
					ace.AdsCommitTransaction(hConn)
				Catch
					// Consider the  Query failed and do something, one of the options being could be ace.AdsRollbackTransaction(hConn)
				End
				If ulCount==0 // From AdsGetRecordCount call (3rd parameter), no records were updated
					pBuf := Char[]{256}
					ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
					ACE.AdsCloseSQLStatement( hStmt)
					ACE.AdsDisconnect( hConn)
					Return False
				Endif
				ACE.AdsCloseSQLStatement( hStmt)
			Endif
		Endif
	Endif
	ACE.AdsDisconnect( hConn)
Return true

Dick

Jamal · Post by **Jamal** » Mon Jun 14, 2021 5:26 pm

Dick,

You are welcome!

You wondered:

So this seems to work (and Transaction does not, I do not know why not yet)

Advantage Local Server does not support transactions. May be this is the reason why

Source: https://devzone.advantagedatabase.com/d ... action.htm

Jamal

Karl-Heinz · Post by **Karl-Heinz** » Tue Jun 15, 2021 7:17 am

Hi Dick,

You call AdsGetLastError() but you don't look at the content of 'pBuf'.

https://devzone.advantagedatabase.com/d ... _error.htm

i don't have ADS, but this should show a detailed error description.

Code: Select all

if Adsxxx() != AE_SUCCESS
 
    ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)

    // char array to string

    ? String{pBuf}:Substring ( 0 , (int) pBufLen )

endif

There´s also the function AdsShowError() that displays the description of the last error in a messagebox.

https://devzone.advantagedatabase.com/d ... werror.htm

Code: Select all

if Adsxxx() != AE_SUCCESS 

  AdsShowError("Error")

endif

To narrow down problems, you should:

- check the return value of *each* Adsxxx() call.
- If the value is != AE_SUCCESS, call immediately either AdsGetLastError() or AdsShowError()

regards
Karl-Heinz

ic2 · Post by **ic2** » Tue Jun 15, 2021 10:10 am

Hello Jamal,

Jamal wrote: Advantage Local Server does not support transactions. May be this is the reason why

I am using ADS Remote. What I think is that transaction only work in a real exception situation; after all it is wrapped up in a common Try--Catch.. If such an exception occurs it will probably roll back changes made below the Try.

I more or less expected Transactions to work also if the query wasn't executed for whatever reason, and that is not the case.

Dick

ic2 · Post by **ic2** » Tue Jun 15, 2021 10:24 am

Hello Karl-Heinz,

Karl-Heinz wrote: You call AdsGetLastError() but you don't look at the content of 'pBuf'.

It's s a bit weird with pBuf. This is what the parameters look like:

: AdsGetLastError.jpg (18.46 KiB) Viewed 643 times

But when I program:

Local pBuf As Char[]
Local pBufLen As System.UInt16
pBuf := Char[]{256}
ACE.AdsGetLastError(Ref pError, Ref pBuf,Ref pBufLen)
I get this compiler error:

Error XS1615 Argument 2 may not be passed with the 'ref' keyword

So I had to remove REF again. I'd say it should work just as it does for pBufLen.

Anyone an idea why I get this error?

It's less important however. For my test situations, the current code helps me to at least see when the query did not execute.
I started this all because I had couple of queries which returned 7200 but executed nevertheless. There was no way I could explain that they didn't return 0 like 10.000's of other queries at this client every year. I can not retest them now but I hope the improved code will now correctly conclude that the query succeeded. I can imagine that when it fails again pBuf could probably give me just that little extra info, but it doesn't work.

Dick

Main menu