Welcome, Guest
Username: Password: Remember me
Forgot your password? Forgot your username? Create an account
Forum
Public Forums
Product
How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW
  • Page:
  • 1

TOPIC:

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 11 Jun 2021 16:34 #18798

  • ic2
  • ic2's Avatar
  • Topic Author


  • Posts: 1615
    • 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:
    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:

    devzone.advantagedatabase.com/dz/webhelp...antage_sql_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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 08:39 #18805

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • 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.
    	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

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 19:25 #18816

    • Jamal
    • Jamal's Avatar


  • Posts: 305
    • 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:

    devzone.advantagedatabase.com/dz/webhelp..._adsgetlasterror.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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 19:57 #18817

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • 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
    	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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 14 Jun 2021 06:30 #18833

    • Jamal
    • Jamal's Avatar


  • Posts: 305
    • 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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 14 Jun 2021 16:19 #18840

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • 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)
    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

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 14 Jun 2021 19:26 #18841

    • Jamal
    • Jamal's Avatar


  • Posts: 305
    • 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: devzone.advantagedatabase.com/dz/webhelp...c/adstransaction.htm

    Jamal

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 09:17 #18844

    • Karl-Heinz
    • Karl-Heinz's Avatar


  • Posts: 774
    • Hi Dick,

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

    devzone.advantagedatabase.com/dz/webhelp...antage_sql_error.htm

    i don't have ADS, but this should show a detailed error description.
    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.

    devzone.advantagedatabase.com/dz/webhelp...ace_adsshowerror.htm
    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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 12:10 #18845

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • 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

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 12:24 #18846

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • 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:



    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
    Attachments:

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 12:59 #18847

    • robert
    • robert's Avatar


  • Posts: 3448
    • Dick,
    I am not sure with which build of X# you are working.
    But in the current build the tooltip and completion code show parameter 2 as this:



    If you look at the prototype in our source code it is written as:
     PUBLIC STATIC METHOD AdsGetLastError(pulErrCode OUT DWORD, [InAttribute] [OutAttribute] strBuf AS CHAR[], pusBufLen REF WORD ) AS DWORD

    The [Inattribute] and [OutAttribute] tell you that you need to pass in a buffer of type CHAR[] and that Advantage will write to this buffer.

    Robert.
    XSharp Development Team
    The Netherlands
    Attachments:

    Please Log in or Create an account to join the conversation.

    Last edit: by robert.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 14:18 #18848

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • Hello Robert,

    I reinstalled 2.7 but if you look in the tooltip picture you see that the tooltip/completition is exactly the same as what you post.
    And if you look in the code than you see that do pass a Char[] variable.
    But that gives me:
    Error XS1615 Argument 2 may not be passed with the 'ref' keyword

    Or are you trying to say that this error does not occur in 2.8?

    In that case I'll try later when I reinstall 2.8.

    Dick

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 15 Jun 2021 15:13 #18849

    • robert
    • robert's Avatar


  • Posts: 3448
    • Dick,
    The tooltips are different. Yours (2.7) says "REF Char[]" , 2.8 sas "AS Char[]".
    This was a problem in the "tooltip" code inside VS which has been fixed in 2.8.
    The compilers in 2.7 and 2.8 both want this parameter without REF prefix.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 16 Jun 2021 10:31 #18854

    • ic2
    • ic2's Avatar
    • Topic Author


  • Posts: 1615
    • Hello Robert,

    Aaaah, I overlooked the difference. Anyhow, good to know that the compiler is doing the right thing.

    One remark for future readers: I also found out why the original code returned 7200 (suggesting an error) while the record was still INSERTed. Turned out that the record had 2 email addresses totaling more than the C60 in our contact DBF. The record will be inserted with the e-mail truncated but an error is generated too.

    With the current code I can both see (and log) any error but also act on the fact that the record has been added anyway.

    Dick

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.
    • Page:
    • 1
    Forum
    Public Forums
    Product
    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW
    ↑