Questions? Feedback?powered byOlark live chat software

Standard Support

Bug 1661

SyntaxFromSql() causes ansinull to set on, using ODBC 15 October, 2018

Russell Harvey
14 September, 2018
Product: PowerBuilder Category: Database
Version: 2017 R2 Publishing: Public
Status: Verifying Priority: P3
Classification: Issue Resolution:
Mark Lee 15 October, 2018
test code

Hi ,

Please refer to the attachment. We modified the corresponding code based on your sample case to work around the issue.
Please modify your code according to the attachment and see if it resolves the issue.

Regards,
Mark Lee
Russell Harvey 15 October, 2018
No, I don't see that this has any effect. I am already using autocommit = true, but at first syntaxFromSql() ansinull is turned on. Autocommit is also turned off in certain parts of the application when the frontend is managing transactions.

We are working around the issue by removing "= null" and replacing with "is null". 

Also we see that isNull(column, '') returns ' ' (space) instead of '' (empty string), and we will adjust our code accordingly. I am not sure if this is related to the ansinull issue or just a "feature" of connecting via ODBC.

Thanks,
Russell Harvey
Russell Harvey 15 October, 2018
(In reply to Mark Lee from comment #10)
Hi Russell,

Can you confirm if setting autocommit = true can work around the issue?
We verified this setting with your sample case and it will enable ansinull
setting to work correctly without any issue.
You can refer to the PB documentation we provided in the previous comment.
ASE database needs to be set in this way to work around the issue.
 
Please let us know if your issue is resolved and we will then close this
ticket.
 
By the way, in future, kindly please report your PB issue via our support
ticket system to ensure it is being properly received by our tech support
and tracked: https://www.appeon.com/standardsupport/
All communication should be through the ticket system (not email) so our
support dept can properly track things.
Thanks for your understanding.

Regards,
Mark Lee

-----------------------------------------------------------------------------
---------
Thanks Mark,
 
We are already using autocommit = true (except in certain cases where there
is transaction management needed in the FE, but autocommit is always set
back to true).
 
In any case, the autocommit setting doesn’t seem to have any bearing on the
ansinull setting. I have no issues with sqlFromSyntax() not working.
Mark Lee 14 October, 2018
Hi Russell,

Can you confirm if setting autocommit = true can work around the issue?
We verified this setting with your sample case and it will enable ansinull setting to work correctly without any issue.
You can refer to the PB documentation we provided in the previous comment.
ASE database needs to be set in this way to work around the issue.
 
Please let us know if your issue is resolved and we will then close this ticket.
 
By the way, in future, kindly please report your PB issue via our support ticket system to ensure it is being properly received by our tech support and tracked: https://www.appeon.com/standardsupport/
All communication should be through the ticket system (not email) so our support dept can properly track things.
Thanks for your understanding.

Regards,
Mark Lee

--------------------------------------------------------------------------------------
Thanks Mark,
 
We are already using autocommit = true (except in certain cases where there is transaction management needed in the FE, but autocommit is always set back to true).
 
In any case, the autocommit setting doesn’t seem to have any bearing on the ansinull setting. I have no issues with sqlFromSyntax() not working.
Mark Lee 12 October, 2018
Hi Russell,

Please refer to the following link to resolve it:
https://www.appeon.com/support/documents/appeon_online_help/pb2017r3/powerscript_reference/ch10s666.html

Note for Adaptive Server Enterprise
If your DBMS is Adaptive Server Enterprise and you call SyntaxFromSQL, PowerBuilder must determine whether the tables are updatable through a unique index. This is only possible if you set AutoCommit to true before calling SyntaxFromSQL, as shown here:

sqlca.autocommit=TRUE
ls_dws=sqlca.syntaxfromsql (sqlstmt, presentation, err)
sqlca.autocommit=FALSE
Mark Lee 12 October, 2018
Or You can work around this issue If you change it to:
sqlca.autocommit = true
Mark Lee 12 October, 2018
Or you may reset ansinull to off after you use SytaxFromSql to work around this issue.
Mark Lee 12 October, 2018
Hi Russell,

Thanks for reporting the test case!
We reproduced it on our end and will do further research to figure it out.
We will keep you posted about the progress.
 
By the way, PB 11.5 and PB 12.6 have the same behavior.
 
Currently this issue only happens when connecting ASE using ODBC drive.
You can work around this issue If you change it to: 
SQLCA.DBMS = "ASE Adaptive Server Enterprise" 
OR  SQLCA.DBMS = "SYC Adaptive Server Enterprise" 

Regards,
Mark Lee
Mark Lee 10 October, 2018
test case
Mark Lee 17 September, 2018
Hi Russell,

Our verification was also done on ASE 15.7.
We understand it will take some time to put together a working example. We appreciate your effort. 
We hope that you understand it is very important for us to have a reproducible example as we can’t reproduce it on our side.

Regards,
Mark Lee
Russell Harvey 17 September, 2018
Select @@version gives me:

Adaptive Server Enterprise/15.7/EBF 26393 SMP SP138 /P/Solaris AMD64/OS 5.10/ase157sp138x/4002/64-bit/FBO/Mon Aug 29 21:14:14 2016      

Note: this only happens when connecting via ODBC (using Sybase driver).

It will take me some time to put together a working example.
Mark Lee 17 September, 2018
Hi Russell,

We are unable to reproduce the issue. Please provide the version detail of your ASE, and provide a reproduciable test case (PBLs) for us to further analyze the issue.

Regards,

Mark Lee
Govinda Lopez 14 September, 2018
Hi Russell,

I will transfer your ticket to our engineering team for further analysis. We will keep you posted on the results here.


Regards,
Russell Harvey 14 September, 2018
*Phenomenon:
Connecting to ASE using Sybase ODBC driver, the application starts up with ansinull off (determined by sp_show_options), but a call to SyntaxFromSql() switches ansinull to set on. This is causing unexpected results in sp calls that user "where col = null", instead of "where col is null".

*Reproduce Steps:
-Connect to db using Sybase ODBC driver.
-Call SytaxFromSql
-Check ansinull setting before and after SyntaxFromSql() call.

Remark:
OS:
Windows 7 
Platform:
32-bit 
Database Type:
SAP ASE 
Database Version:
15.7.0