on 06-30-2016 4:46 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.