How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

This forum is meant for questions and discussions about the X# language and tools
ic2
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by Jamal »

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
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by Jamal »

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
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by Jamal »

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
Posts: 774
Joined: Wed May 17, 2017 8:50 am
Location: Germany

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by Karl-Heinz »

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
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW

Post by ic2 »

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
AdsGetLastError.jpg (18.46 KiB) Viewed 426 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
Post Reply