1. Russell Harvey
  2. PowerBuilder
  3. Saturday, 8 September 2018 01:55 AM UTC

Hi,

After calling SyntaxFromSql() I am seeing the "ansinull" property change from off to on. This is causing some queries to fail. I am connecting to ASE using ODBC.

I issued SyntaxFromSql() calls just after connecting to the database. I checked ansinull property using sp_show_options. It's off initially, then gets turned on after the SyntaxFromSql call.

Anyone know why it's doing this? I can turn ansinull off ("set ansinull off") after the function call, but it's used a lot in the application.

Thanks,

Russell Harvey

Russell Harvey Accepted Answer Pending Moderation
  1. Wednesday, 12 September 2018 21:37 PM UTC
  2. PowerBuilder
  3. # 1

Thanks for your replies Chris & Olan,

Just to clarify, I am using the Sybase ODBC driver, not the MS driver.

I have tried explicitly setting ansinull off (execute immediate "set ansinull off";), but sqlFromSyntax resets it to on.

Of course I am considering issuing the command after each sqlFromSyntax, but there are over 60 instances of it our application. (Or I'll convince the programmers around here to stop using "=null"!)

Anyway, thanks for your assistance. I am several weeks into trying to get this application to work with ODBC, and I'm hitting one roadblock after another. If anyone has any general advice for using ODBC with PB, I'd be grateful to hear about it.

Thanks,

Russell

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 12 September 2018 21:46 PM UTC
Hi Russ;

If you have a few minutes, it might be worthwhile opening a Support Ticket (its free) for this ODBC issue. That would at least get this issue on Engineering's ToDo list.



FYI: https://www.appeon.com/standardsupport



Regards ... Chris
  1. Helpful
  1. Russell Harvey
  2. Friday, 14 September 2018 17:55 PM UTC
Thanks Chris - I submitted a ticket for this.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 12 September 2018 20:46 PM UTC
  2. PowerBuilder
  3. # 2

Hi Russ;

   Hmmmm .. not much to go on in that trace - unfortunately.

   I wonder if the best workaround is to just turn off the setting after each "SyntaxFromSQL" command is executed? From what I have read thus far, its the Sybase-ODBC driver that is the one that sets this option ON at connect time? The other option of course, is to use the native ASE driver.

FYI:  EXECUTE IMMEDIATE "Set ANSI_NULLS OFF" 

  Another thought: What about using a 2nd Transaction Object connected to ASE just for the SyntaxFromSQL command's use?

HTH

Regards ... Chris

 

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 12 September 2018 20:42 PM UTC
  2. PowerBuilder
  3. # 3

Here's some important information:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017

   "In a future version of SQL Server, ANSI_NULLS will be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."


   https://knowledgebase.progress.com/articles/Article/6334

   In your application, you can restore the default Sybase behavior for a connection in the following ways:

Use the Initialization String option to specify the SQL command set ANSINULL off. For example, the following
connection string ensures that the handling of NULL values is restored to the Sybase default for the current connection:

DSN=SYB TABLES;DB=PAYROLL;IS=set ANSINULL off

Explicitly execute the following statement after the connection is established:

SET ANSINULL OFF



Hope this helps,

Olan

Comment
There are no comments made yet.
Russell Harvey Accepted Answer Pending Moderation
  1. Wednesday, 12 September 2018 19:03 PM UTC
  2. PowerBuilder
  3. # 4

Thanks for your reply, Chris.

I ran a trace - below is portion of the log I determined to be associated with the syntaxFromSql() call. It doesn't really give me any clues, but I don't speak whatever language this is. Maybe you or someone out there will have some amazing insight.

Next I might try the lower-level trace.

 

(08BD3048): PREPARE:
(08BD3048): select moniker from pending_trade where 1=2(DBI_PREPARE) (3.136 MS / 775.584 MS)
(08BD3048): DESCRIBE:(DBI_DESCRIBE) (0.021 MS / 775.605 MS)
(08BD3048): name=moniker,dsntype=char,len=7,type=CHAR,pbt=1,dbt=1,ct=0,prec=6,scale=0
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_GET_FROM_BEFORE) (0.000 MS / 775.605 MS)
(08BD3048): (DBI_VALIDATE_IDENT) (0.000 MS / 775.605 MS)
(08BD3048): GET EXTENDED ATTRIBUTES: TABLE=pending_trade OWNER=dbo(DBI_COMPLETE_TABLE_INFO) (9.980 MS / 785.585 MS)
(08BD3048): (DBI_PARSE_COLLIST) (0.000 MS / 785.585 MS)
(08BD3048): (DBI_VALIDATE_IDENT) (0.000 MS / 785.585 MS)
(08BD3048): (DBI_VALIDATE_HASH) (6.214 MS / 791.799 MS)
(08BD3048): (DBI_FORMAT_HASH) (6.370 MS / 798.169 MS)
(08BD3048): (DBI_EDITSTYLE_HASH) (6.635 MS / 804.804 MS)
(08BD3048): (DBI_DESCRIBE_OPTIMIZED) (0.000 MS / 804.804 MS)
(08BD3048): (DBI_CASE_SENSITIVE) (0.000 MS / 804.804 MS)
(08BD3048): (DBI_DW_COL_DICT) (6.566 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_TERMINATOR) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_GET_FROM_BEFORE) (0.000 MS / 811.370 MS)
(08BD3048): (DBI_VALIDATE_IDENT) (0.000 MS / 811.370 MS)
(08BD3048):  UNIQUE KEY CHECK: TABLE=pending_trade OWNER=dbo USER=dbo(DBI_UNIQUE_KEY) (11.892 MS / 823.262 MS)
(08BD3048): YES, unique key found

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 9 September 2018 14:16 PM UTC
  2. PowerBuilder
  3. # 5

Hi Russell;

   Have you tried this feature while connecting to ASE using PB's "native" client instead?

IE: SQLCA.dbms = "ASE"

Regards ... Chris

Comment
  1. Russell Harvey
  2. Monday, 10 September 2018 18:42 PM UTC
Hi Chris,



Thanks for your reply. There is no problem using the native driver. Unfortunately, we are transitioning from using the native to ODBC, so I'm kind of stuck with it.



I'm finding it challenging to get information on preferred settings for PB/ODBC. Any help is appreciated!



Thanks,

Russell
  1. Helpful
  1. Chris Pollach @Appeon
  2. Monday, 10 September 2018 19:27 PM UTC
Well, at least we have narrowed it down a bit. However, we have the issue as to whether its the PB ODBC "interface" driver of the ASE SQL driver itself.



I do not know exactly why the ODBC connectivity is giving you this problem as I don;t normally use middle-ware to connect to any DBMS if I can help it (just personal preference)./ However, I wonder if it might be worthwhile running an low level SQL trace to see if that might illuminate something ...

SQLCA.DBMS = "TRACE ODBC" // or

SQLCA.DBMS = "TRACE ASE"



Note: You can also run a low-level SQL trace from the ODBC driver as well via a setting in the ODBC Admin utility.



Food for thought





  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.