Hello everyone,
First of all thanks for this awesome module!
Second, I'm having an issue with a query, when it gets past a certain length I get an exception: OperationalError: No Data Available thrown at me.
The query works fine in SSMS, so I know thats not the issue... but anyhow the relevant code goes:
query = "SELECT * FROM redLinks WHERE proj_id = 1 AND from_ID = 85 AND from_UID = 17055 AND to_ID = 233 AND to_uid = 233 AND from_Name = \"TC7: Misc Materials for Motor Assembly\""
print query
cur.execute(query)
data = cur.fetchone()
(Mind you the query is all on one line in my editor) and fetchone and fetch all throw the above mentioned exception.
If I remove any of the one conditional clauses, the query works as expected.
Any help would be greatly appreciated!
- Jeremy
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am no SQL expert, but for the queries we have to do here, we have to use single quotes for the string constants in the "WHERE" clause. Does it work any better if you change that last clause to read:
... AND from_Name = 'TC7: Misc Materials for Motor Assembly'"
that is, put the 'TC7...Assembly' value in single, rather than double, quotes. I know our database will not take the double quotes.
If this doesn't work, then I would check the documentation for line length limitations. I seem to recall reading of one, but I thought that it was about 2000 characters, which shouldn't be a problem for the line that you quote. Check the documentation; I may be misremembering things.
Eric
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
import pymssql
con=pymssql.connect(host='.',user='sa',password='P@ssw0rd')
cur=con.cursor()
query = 'SELECT '
for i in xrange(4095):
query += "'this is column data %d' AS col%d, " % (i,i)
query += "'final column' AS col4095"
cur.execute(query)
res = cur.fetchone()
print "query length=%d, number of values in resulting row=%d" % (len(query), len(res))
In my system it returns
query length=157517, number of values in resulting row=4096
So I don't think query length is the problem.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello everyone,
First of all thanks for this awesome module!
Second, I'm having an issue with a query, when it gets past a certain length I get an exception: OperationalError: No Data Available thrown at me.
The query works fine in SSMS, so I know thats not the issue... but anyhow the relevant code goes:
query = "SELECT * FROM redLinks WHERE proj_id = 1 AND from_ID = 85 AND from_UID = 17055 AND to_ID = 233 AND to_uid = 233 AND from_Name = \"TC7: Misc Materials for Motor Assembly\""
print query
cur.execute(query)
data = cur.fetchone()
(Mind you the query is all on one line in my editor) and fetchone and fetch all throw the above mentioned exception.
If I remove any of the one conditional clauses, the query works as expected.
Any help would be greatly appreciated!
- Jeremy
Jeremy:
I am no SQL expert, but for the queries we have to do here, we have to use single quotes for the string constants in the "WHERE" clause. Does it work any better if you change that last clause to read:
... AND from_Name = 'TC7: Misc Materials for Motor Assembly'"
that is, put the 'TC7...Assembly' value in single, rather than double, quotes. I know our database will not take the double quotes.
If this doesn't work, then I would check the documentation for line length limitations. I seem to recall reading of one, but I thought that it was about 2000 characters, which shouldn't be a problem for the line that you quote. Check the documentation; I may be misremembering things.
Eric
Double quotes is certainly the problem.
About query length, try below code:
import pymssql
con=pymssql.connect(host='.',user='sa',password='P@ssw0rd')
cur=con.cursor()
query = 'SELECT '
for i in xrange(4095):
query += "'this is column data %d' AS col%d, " % (i,i)
query += "'final column' AS col4095"
cur.execute(query)
res = cur.fetchone()
print "query length=%d, number of values in resulting row=%d" % (len(query), len(res))
In my system it returns
query length=157517, number of values in resulting row=4096
So I don't think query length is the problem.