cancel
Showing results for 
Search instead for 
Did you mean: 

"Not enough values for host variables" when setting more than one variable from Python with SQLAnywhere10

Former Member
0 Kudos

The following statements all work perfectly:

cur.execute('SELECT ?, ?', [1, 2])

cur.execute('BEGIN DECLARE a int; set a = ?; SELECT 1; END', [1])

cur.execute('BEGIN DECLARE a int; set a = ?; SELECT a; END', [1])

cur.execute('BEGIN DECLARE a int; DECLARE b int; SET a = ?; SELECT a; END', [1])

But the following doesn't:

cur.execute('BEGIN DECLARE a int; DECLARE b int; SET a = ?; SET b = ?; SELECT a; END', [1, 2])

It fails with:

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/Users/asday/.virtualenvs/store-first/lib/python2.7/site-packages/sqlanydb.py", line 792, in execute

    self.executemany(operation, [parameters])

  File "/Users/asday/.virtualenvs/store-first/lib/python2.7/site-packages/sqlanydb.py", line 769, in executemany

    self.handleerror(*self.parent.error())

  File "/Users/asday/.virtualenvs/store-first/lib/python2.7/site-packages/sqlanydb.py", line 689, in handleerror

    eh(self.parent, self, errorclass, errorvalue, sqlcode)

  File "/Users/asday/.virtualenvs/store-first/lib/python2.7/site-packages/sqlanydb.py", line 379, in standardErrorHandler

    raise errorclass(errorvalue,sqlcode)

OperationalError: ('Not enough values for host variables', -188)

Why?  I need to be able to set more than one variable at a time, it's kind of important.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Adam,

I was able to reproduce the issue and with request logging I see:

[,5,*batch*,1,set a = :?

H,5,,integer,1

H,5,,integer,2

],5,*batch*,1

[,5,*batch*,1,set b = :?

],5,*batch*,1

E,5,-188,Not enough values for host variables

Clearly nothing is being signed to b and results the -188 error. Let me look into it and get back to you when I can.

Jinwoo

Former Member
0 Kudos

Hi Adam,

We didn't support host variables in batch statements on early builds of SQL Anywhere 10. Starting 3873, then we allowed that with few restrictions:

- only one statement in the batch can refer to host variables

- the statement which uses host variables cannot be preceded by a statement which returns a result set

(reference: The link is in SQLA 17 but the same applies to late SQLA 10 builds as well)

As a workaround, you can use SELECT.. INTO.

cursor.execute('BEGIN DECLARE a int; DECLARE b int; SELECT ?, ? INTO a, b; SELECT a; END', [1, 2])

However, for some reason the SELECT a statement returns an error:

Traceback (most recent call last):

  File ".\Scripts\test.py", line 13, in <module>

    cursor.execute('BEGIN DECLARE a int; DECLARE b int; SELECT ?, ? INTO a, b; SELECT a; END', [1, 2])

  File "c:\Python35-32\lib\site-packages\sqlanydb-1.0.6.1-py3.5.egg\sqlanydb.py", line 761, in execute

  File "c:\Python35-32\lib\site-packages\sqlanydb-1.0.6.1-py3.5.egg\sqlanydb.py", line 738, in executemany

  File "c:\Python35-32\lib\site-packages\sqlanydb-1.0.6.1-py3.5.egg\sqlanydb.py", line 660, in handleerror

  File "c:\Python35-32\lib\site-packages\sqlanydb-1.0.6.1-py3.5.egg\sqlanydb.py", line 350, in standardErrorHandler

sqlanydb.OperationalError: b'Communication error'

Let me know if this works on your environment without the same error I see.

Jinwoo

Former Member
0 Kudos

After the

SELECT INTO

, the database closes the connection for some reason.  I have no idea why.  My current solution is as you suggested, but there are two database hits.  The first declares the variables, and selects the parameters into them, the second runs the actual query utilising the variables.  There's a third hit later on where I clean up the variables, but that's unavoidable, so I'm not worried about it.

Answers (0)