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.

asked 09 Apr '14, 02:53

Valdas's gravatar image

Valdas
28791222
accept rate: 80%

edited 17 Apr '14, 02:18


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

permanent link

answered 15 Aug '14, 16:33

JBSchueler's gravatar image

JBSchueler
2.0k2837
accept rate: 16%

Comment Text Removed
Comment Text Removed

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.

(20 Aug '14, 15:22) Eric Murchie...

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.

(20 Aug '14, 16:10) JBSchueler
2

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

(08 Sep '14, 12:58) JBSchueler

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...

alt text

then it works ok on subsequent executions...

alt text

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' );
permanent link

answered 17 Apr '14, 08:29

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

edited 17 Apr '14, 08:55

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×198
×34

question asked: 09 Apr '14, 02:53

question was seen: 2,400 times

last updated: 08 Sep '14, 12:58