Powerbuilder 12.5 and SQL Anywhere 11 app fails certain sql statements

[Former Member]

I have an application that was developed in PB 12.5 build 2511 and it uses SQL Anywhere database version 11.0.1.2584.

When I run the app from PB everything is fine and app works great, however when I create the exe and deploy it on my test box suddenly some of the sql statements simply stop working. sqlca code is -1.

To give an exmple one of the column my sql is selecting is defined as smallint on the database when i try to pull this field into a powerscript interger var sql fails with an error message 'can not convert to numeric' from the exe, it works fine when I run the app from powerbuilder. Can someone help what is missing here. thanks a lot in advance.

Javed

[Breck_Carter]

Please show us the PowerScript statement that is failing, plus the SQLCA.SQLDBCode and SQLCA.SQLErrText values (those are the actual error codes coming from SQL Anywhere, the SQLCode value is just a PowerBuilder thing).

[Former Member]

The sql error is 'cannot convert 'remote' (name of the column) to numeric. sqldbcode is -157 and the actual sql that is failing is:

SELECT code, name, level, IsNull("remote", 0), IsNull(active, 0) INTO :ls_Code, :ls_Name, :li_Level, :li_Remote, :li_Active FROM logon WHERE username = :ls_UserName AND password = :ls_PassWord;

[Breck_Carter]

It looks like "remote" is being passed to SQL Anywhere as a string literal rather than a column name. I assume you have coded it as "remote" because remote is a reserved word in SQL Anywhere... but generally "doublequotes" are used for string literals in PowerScript.

I am guessing there's a bug somewhere; perhaps one of these workarounds will get past it...

IsNull ( logon."remote", 0 )

IsNull ( logon.remote, 0 )

IsNull ( [remote], 0 )

IsNull ( logon.[remote], 0 )

IsNull ( remote, 0 )

[Former Member]

Yes remote is a key word in sqla and that is why i used double qoutes, the problem is that this is not the only sql that is failing, there are other sql that fails as well, but some work fine. On top of all that i did not get any issues while running it from inside PB.

[Former Member]

It would be worth checking that the contents of the 'PBODB125.ini' file used by the IDE and by your runtime EXE are the same. This file contains various settings that allow Powerbuilder to modify its behaviour when connecting via ODBC e.g. there is an 'IdentifierQuoteChar' setting that may be relevant.

[Former Member]

I will surely chk this and report back but Luke/Breck I have created PB exe in the past using older versions, but creating a project in PB 12.5 is little differnet, it allows the option of creating machine code, which was not there beofre, will this have any impact.

[Former Member]

this did it Luke, I had copied the PBODB125.DLL but I did not relaize that this dll is using an associated ini file as well. The ini file contains an attribute called IdentifierQuoteChar which was causing problems. thanks to all who contributed.