1

We are developing the module in tryton based on GNU Health.We got the following error :

ProgrammingError: operator does not exist character varying = bigint
Hint: No opreator matches the given name and argument type(s). You might need to add explicit type casts
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Balamurugan S
  • 13
  • 1
  • 5
  • What did you do to produce that error? How did you get to this point? What have you already tried/read? What was the SQL query that produced that error, and where did it come from? (If you don't know what the query was, enable `log_statement = all` in `postgresql.conf` and reload postgresql). – Craig Ringer Jan 25 '14 at 14:17
  • we develop the New patient registration module with PMRN and OP No.These are patient identification code. In Old patient registration we want to retrieve the date when give the PMRN No it will display the details of that particular Patient record. ex of PMRN No is 201401270001 but datatype is Char. The Query in Python Coding is ((SELECT name,age,dob,address FROM TABLENAME WHERE pmrn=%s) %(self.pmrn)) – Balamurugan S Jan 27 '14 at 05:47
  • Urk, please *edit the question* to add any follow-up then comment to let readers know you've made an edit. SQL in comments is nearly unreadable. – Craig Ringer Jan 27 '14 at 08:37
  • Also, in future, please show the actual SQL that got executed by the application, not the *template* SQL in the application. You can usually find that in your application logs; if not, see my first comment for how to get it from the PostgreSQL logs. – Craig Ringer Jan 27 '14 at 08:48

1 Answers1

4

As best as I can vaguely guess from the limited information provided, in this query: 

"SELECT name,age,dob,address FROM TABLENAME WHERE pmrn=%s" % (self.pmrn)

you appear to be doing a string substitution of a value into a query.

First, this is dangerously wrong, and you should never ever do it without an extremely good reason. Always use parameterized queries. psycopg2 supports these, so there's no excuse not to. So do all the other Python interfaces for PostgreSQL, but I'm assuming you're using psycopg2 because basically everyone does, so go read the usage documentation to see how to pass query parameters.

Second, as a result of failing to use parameterized queries, you aren't getting any help from the database driver with datatype handling. You mentioned that pmrn is of type char - for which I assume you really meant varchar; if it's actually char then the database designers need to be taken aside for a firm talking-to. Anyway, if you substitute an unquoted number in there your query is going to look like:

pmrn = 201401270001

and if pmrn is varchar that'll be an error, because you can't compare a text type to a number directly. You must pass the value as text. The simplistic way is to put quotes around it:

pmrn = '201401270001'

but what you should be doing instead is letting psycopg2 take care of all this for you by using parameterized queries. E.g.

curs.execute("SELECT name,age,dob,address FROM TABLENAME WHERE pmrn=%s", (self.pmrn,))

i.e. pass the SQL query as a string, then a 1-tuple containing the query params. (You might have to convert self.pmrn to str if it's an int, too, eg str(self.pmrn)).

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I have changes made as you told but I got an error like this cur.execute("select title,name,lastname,dob from outpatient_new_registration where pmrn = %s" ,(self.pmrn)) File "/trytond/backend/postgresql/database.py", line 306, in execute res = self.cursor.execute(sql, params) TypeError: not all arguments converted during string formatting – Balamurugan S Jan 27 '14 at 10:57
  • @BalamuruganS A python 1-tuple is `(self.prmn,)`. Not just `(self.prmn)`. If you write `(self.prmn)` Python will ignore the redundant parentheses and Psycopg2 will try to read the elements of the sequence `self.prmn`, which if it's a string is each character in the string. If there's more than one character, `psycopg2` will complain it has arguments but no placeholders to put them in. – Craig Ringer Jan 27 '14 at 11:31