SQL Anywhere 16 and PowerBuilder host variables error on first connection

[cigaras]

There is an empty database:

dbinit test16
dbsrv16 -n test16 test16.db


With a simple procedure in there:
CREATE OR REPLACE PROCEDURE dba.sp_test(in @a integer, in @b integer)
BEGIN
  select @a + @b as c;
END;


Then there is a simple PowerBuilder application created using application template:
Integer a = 2, b = 2, c

DECLARE c_test CURSOR FOR
    SELECT c
    FROM sp_test(:a, :b);
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)

OPEN c_test;
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)

FETCH c_test INTO :c;
IF SQLCA.SQLCode <> 0 THEN
    MessageBox('DB Error', SQLCA.SQLErrText)
ELSE
    MessageBox('Success!', string(a) + ' + ' + string(b) + ' = ' + string(c))
END IF

CLOSE c_test;
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)


And this is where the fun begins: if I start the database and first time run the program, I get the following error after OPEN command:
SQLSTATE = 07002
[Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables


Then if I restart the program, i.e. reconnect to the database, error no longer occurs. And I get no errors on every next connection, only the first time after starting the database.
I am using the following software:

SQL Anywhere version 16.0.0 build 1823
PowerBuilder version 11.5.1 build 5097
PowerBuilder version 12.5.2 build 5006

There is no error using SQL Anywhere 11 ar 12, I tried older build 1691 for version 16 and got the same problem. I also tried declaring same cursor in SQL Interactive, could not reproduce the same error. Also if I change host variables in PowerBuilder application with static values like this:
DECLARE c_test CURSOR FOR
    SELECT c
    FROM sp_test(2, 2);


I get no error, but if at least one procedures argument is a variable, I get the error on first connection.

[jack_schueler · Accepted solution]

I investigated this issue today and have a fix for the problem (it's been there since the initial release of 16.0).

[Former Member · Accepted solution]

When will the fix be available and in what EBF?

In case it helps, I am still getting this error on version 16.0.0.19894, using C# and Entity Framework.

The stored procedure:

CREATE PROCEDURE DBA.MyProcedure(
@PARAM1 VARCHAR(8), 
@PARAM2 VARCHAR(20)
) 
AS 
BEGIN
   ...
   select RETMESSAGE = 'Some message'
   RETURN
END;

The C# code:

string Val1 = "test";
string Val2 = "test";
try
{
    // Call the procedure once, expecting the error
    List<string> result1 = context.Database.SqlQuery<string>(
        "SELECT * FROM DBA.MyProcedure(?, ?)",
        new SAParameter("@PARAM1", Val1), 
        new SAParameter("@PARAM2", Val2)).ToList();
}
catch
{
}
// Now I can call the procedure for real
List<string> result = context.Database.SqlQuery<string>(
    "SELECT * FROM DBA.MyProcedure(?, ?)",
     new SAParameter("@PARAM1", Val1),
     new SAParameter("@PARAM2", Val2)).ToList();

The first call always fails, with "Not enough values for host variables". The second call succeeds.

[jack_schueler · Accepted solution]

The fix is working its way through the review process. When approved it will go into the 16.0 code line and then we will be able to announce which build it will appear in. So hang on for a bit, it won't happen overnight.

[jack_schueler · Accepted solution]

A fix for this issue will appear in the following or later builds: 16.0.2007 12.0.1.4157

[Breck_Carter]

Confirmed...

I get the same symptom with the following PB 11.5 application open event script using a SQL Anywhere 16.0.0.1823 database. As you indicate, it fails when the script is run immediately after the database is started...

then it works ok on subsequent executions...

It behaves the same when run from the PB IDE and as a deployed executable (test.exe).

I don't think the pbodb115.ini file is involved here, but I attached the copy I am using (and haven't changed in a hundred years 🙂

see attached pbodb115.ini

Integer a = 2, b = 2, c

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 16;" &
    + "UID=dba; PWD=sql; DBN=ddd16; ENG=ddd16'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA;

IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
        'CONNECT failed in open:' &
        + '~r~nSQLCode = ' &
        + String ( SQLCA.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( SQLCA.SQLDBCode ) &
        + '~r~n' &
        + SQLCA.SQLErrText )
    RETURN
END IF

MessageBox ( 'CONNECT', 'OK' );

//CREATE OR REPLACE PROCEDURE dba.sp_test(in @a integer, in @b integer)
//BEGIN
//  select @a + @b as c;
//END;


DECLARE c_test CURSOR FOR
    SELECT c
    FROM sp_test(:a, :b);
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)

OPEN c_test;
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)

FETCH c_test INTO :c;
IF SQLCA.SQLCode <> 0 THEN
    MessageBox('DB Error', SQLCA.SQLErrText)
ELSE
    MessageBox('Success!', string(a) + ' + ' + string(b) + ' = ' + string(c))
END IF

CLOSE c_test;
IF SQLCA.SQLCode <> 0 THEN MessageBox('DB Error', SQLCA.SQLErrText)

MessageBox ( 'test', 'all done' );