Unable to prepare a statement when the object names contain more than one $ symbol
Altaf Malik
I have a table with two columns of type varchar and the table name contains two dollars. I am unable to prepare a statement for this table. Here is the code:
con.createStatement().executeUpdate("create table a$b$c(a varchar, b varchar)");
PreparedStatement pstmt = con.prepareStatement("insert into a$b$c values( ? , ?)");
pstmt.setString(1,"Hello");
pstmt.setString(2,"Welcome");
pstmt.execute();
This code throws the following exception.
Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:117)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2118)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1241)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1227)
at Main.main(Main.java:33)
How can i insert values in this table using PreparedStatement?
--Altaf Malik
Shape Yahoo! in your own image. Join our Network Research Panel today!
Kris Jurka
On Mon, 9 Jul 2007, Altaf Malik wrote:
> I have a table with two columns of type varchar and the table name
> contains two dollars. I am unable to prepare a statement for this table.
> Here is the code:
>
> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
> values( ? , ?)");
> Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
This is a bug in dollar quote parsing. It assumes that any potential
dollar quote start will have an end tag. When it doesn't find the end it
reports the end of the query, so the driver ends up skipping over the "?"
parameters.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
Tom Lane
> On Mon, 9 Jul 2007, Altaf Malik wrote:
>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>> values( ? , ?)");
> This is a bug in dollar quote parsing. It assumes that any potential
> dollar quote start will have an end tag.
If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name. (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)
regards, tom lane
Altaf Malik
--Altaf
Tom Lane <t...@sss.pgh.pa.us> wrote:
Kris Jurka writes:
> On Mon, 9 Jul 2007, Altaf Malik wrote:
>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>> values( ? , ?)");
> This is a bug in dollar quote parsing. It assumes that any potential
> dollar quote start will have an end tag.
If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name. (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)
regards, tom lane
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.
Gregory Stark
> I think $$ should start a quote instead of $x$. Or $ character have special
> meaning but if there is one dollar does not happen anything wrong. Are $$ and
> $x$ equal? If yes, what happens with the character(s) between the two dollar
> signs?
$anything$ starts a quote and ends with $anything$ as in:
postgres=# select $foo$bar$foo$;
?column?
----------
bar
(1 row)
However the $ must start a new token:
postgres=# select foo$foo$bar$foo$;
ERROR: column "foo$foo$bar$foo$" does not exist
LINE 1: select foo$foo$bar$foo$;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
Michael Paesold
> Kris Jurka <bo...@ejurka.com> writes:
>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>>> values( ? , ?)");
>
>> This is a bug in dollar quote parsing. It assumes that any potential
>> dollar quote start will have an end tag.
>
> If it thinks that's a dollar quote start, it's wrong already... that's
> a perfectly valid table name. (Although personally I'd suggest that the
> OP avoid dollar signs in identifiers, since they're none too portable.)
I guess I missed that case when implementing dollar quoting in the
driver. I will have a look at it shortly.
Best Regards
Michael Paesold
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Michael Paesold
sorry for not coming back to this earlier. Yeah, this is no real solution.
I thought about checking the character directly before the dollar quote.
But I have not had the time to look at the grammer if that is enough to fix
all error cases... I will do so tomorrow.
Best Regards
Michael Paesold
Kris Jurka schrieb:
>
>
> On Tue, 10 Jul 2007, Michael Paesold wrote:
>
>> Tom Lane schrieb:
>>> Kris Jurka <bo...@ejurka.com> writes:
>>>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>>>>> values( ? , ?)");
>>>
>>>> This is a bug in dollar quote parsing. It assumes that any
>>>> potential dollar quote start will have an end tag.
>>>
>>> If it thinks that's a dollar quote start, it's wrong already... that's
>>> a perfectly valid table name. (Although personally I'd suggest that the
>>> OP avoid dollar signs in identifiers, since they're none too portable.)
>>
>> I guess I missed that case when implementing dollar quoting in the
>> driver. I will have a look at it shortly.
>>
>
> Did you ever get a chance to look at this? The attached patch fixes the
> original complaint by ensuring that the ending tag exists, but as Tom
> pointed out that's still not good enough as the second attached test
> case demonstrates.
>
> Kris Jurka
>
---------------------------(end of broadcast)---------------------------
Michael Paesold
> Hi Kris,
>
> sorry for not coming back to this earlier. Yeah, this is no real
> solution. I thought about checking the character directly before the
> dollar quote. But I have not had the time to look at the grammer if that
> is enough to fix all error cases... I will do so tomorrow.
Well, I think we have several simple options (apart from implementing a
real parser in the driver ;-)...
1) In parseDollarQuotes(), we look at the character directly before the
$-char and compare it against all valid identifier characters. If it is,
we don't have a possible dollar-quote start:
From scan.l:
ident_start [A-Za-z\200-\377_]
ident_cont [A-Za-z\200-\377_0-9\$]
2) Same concept as 1), but instead create a new method parseIdentifier()
in the Parser class that would be used to detect and "skip" all regular
keywords and identifiers in the query string, and therefore avoid the
current problem. Seems like a performance loss though, because we would
do more checks on each character.
3) Again in parseDollarQuotes(), we look at the character directly
before the $-char and see if it is an *allowed* character before a
dollar-quote. But what is allowed? Operators, a comma, parenthesis, etc.
... this is much harder to get right.
Looking at gram.y and scan.l (...but I'm no expert) I can't see another
case of a dollar-character that I missed, except the one allowed in
ident_cont... so option 1) seems like the easiest solution. I will send
a patch implementing option 1 shortly.
Regarding your patch: I'm not sure if we should apply it, too. You will
probably get a different error messages in case of an unterminated
dollar quote. I haven't looked at the possible results.
My original reasoning was that if there is an unterminated quote, this
should not cause us parsing what is inside the quotes. So I would leave
it as-is. Another option would be to throw an "unterminated
dollar-quote" exception directly in the driver, which would be more
helpful in some cases.
Best Regards
Michael Paesold
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Michael Paesold
...
> 1) In parseDollarQuotes(), we look at the character directly before the
> $-char and compare it against all valid identifier characters. If it is,
> we don't have a possible dollar-quote start:
>
> From scan.l:
> ident_start [A-Za-z\200-\377_]
> ident_cont [A-Za-z\200-\377_0-9\$]
> Looking at gram.y and scan.l (...but I'm no expert) I can't see another
> case of a dollar-character that I missed, except the one allowed in
> ident_cont... so option 1) seems like the easiest solution. I will send
> a patch implementing option 1 shortly.
Ok, here is the patch. It also adds your tests exposing the bug to the
test suite. The patch fixes both test cases and passes all other
regression tests (at least with JDK 1.5....). Does it look OK to you?
Should we do anything additional about error-ing out when we detect an
unterminated dollar-quote (see previous mail)?
Kris Jurka
On Thu, 18 Oct 2007, Michael Paesold wrote:
> Ok, here is the patch. It also adds your tests exposing the bug to the test
> suite. The patch fixes both test cases and passes all other regression tests
> (at least with JDK 1.5....). Does it look OK to you?
Looks good. Applied.
> Should we do anything additional about error-ing out when we detect an
> unterminated dollar-quote (see previous mail)?
>
I don't think that's necessary. We don't do so for string literals,
quoted identifiers, or block comments.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster