Consistent misbehaviour of JDBC drivers when submitting data?

[Simon Brooke]

I'm getting a consistent problem with JDBC drivers for both MS Access
and Postgres, namely that when I build and submit an insert or update
query, I get a response saying there's a syntax error. Yet when I
submit the identical query on the command line (copy and paste), it
works satisfactorily. Has anyone else seen this behaviour? Are there
any clues as to what the real problem is?

Here's the postgres error message:
javax.servlet.ServletException: doInsert: SQL error [ERROR: parser: parse error at or near ""
] whilst trying to build and execute insert [insert into customer (
id, parent, trade_name, reg_name, reg_no, reg_addr, sic) values ( 4
, 2 , 'Testing', 'Testing Company Ltd', '986372', -1 , 2 );]
at uk.net.unitech.streamline.StreamlineForm.doInsert(StreamlineForm.java:145)
at uk.net.unitech.streamline.StreamlineForm.doHead(StreamlineForm.java:291)
at uk.co.weft.htform.HTPage.doGet(HTPage.java:134)
at uk.co.weft.htform.HTPage.doPost(HTPage.java:148)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:278)

The Microsoft equivalent reads:
[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in INSERT
INTO statement.

The table exists, and all the fields exist; all the values are
appropriate values for the fields (as I said, I've cut and pasted
these queries into the command line (or in MS Access the SQL View
window) and they've worked. The user identified by the getConnection
user name and password is a valid user with premission to insert and
update the table.

Help!

--
si...@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon
"This young man has not the faintest idea how socialists think and does
not begin to understand the mentality of the party he has been elected
to lead. He is quite simply a liberal" -- Ken Coates MEP (Lab) of Tony Blair

[Kam Chan]

I've seen some drivers don't like the semi-colon at the end of the
statement.

Good luck.

Kam Chan

Simon Brooke wrote in message ...

[Simon Brooke]

In article <6ug8ga$ln9$1...@news0-alterdial.uu.net>,

"Kam Chan" <kchan_nospam@rgare_nospam.com> writes:
> I've seen some drivers don't like the semi-colon at the end of the
> statement.

Thanks for this suggestion, but I've tried it and it didn't work. I've
tried both with and without semi-colons, and both with and without
spaces after the commas, both with insert and with update queries; and
I'm getting the same thing every time.

Anybody else have any clues? Pretty please?

Simon, baffled...

--
si...@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon

Morning had broken. I found a rather battered tube of Araldite
resin in the bottom of the toolbag.

[Paul Henry]

Simon Brooke <si...@caleddon.jasmine.org.uk> wrote in article
<m37lytu...@caleddon.jasmine.org.uk>...
>
[SnipCity]

>
> Here's the postgres error message:
> javax.servlet.ServletException: doInsert: SQL error [ERROR: parser:
parse error at or near ""
> ] whilst trying to build and execute insert [insert into customer (
> id, parent, trade_name, reg_name, reg_no, reg_addr, sic) values ( 4
> , 2 , 'Testing', 'Testing Company Ltd', '986372', -1 , 2 );]
>

[More Snip]

Braindump of some ideas, apologies if they're stupid or you've already
tried them:

0. Spaces between parentheses and values, especially the "values ( 4"
looks dodgy. Try "values (4, 2," etc.

1. Maybe there is a limit on the length of the statement. (Seems unlikely
since yours is only about 150 characters). You say in your previous reply
to Kan that update statements also fail. Do you know if any similar length
statements work OK? Does a simple select statement <128 characters work?

2. Could you have some non-printing character in there? Since: parse error
at or near "" kind of implies either double quotes or some non-printing
character. Do the text data have any CR/LFs or TABs on the end? Or the
statement as a whole, does that have a CR on the end? This could relate to
how you have set up the statement in source code, e.g. as a single string
or multiline concatentation.

5. reg_no looks suspicious - is this a numeric or string? Is it a
consistent datatype in both application and database? Try putting a NULL
value in and see what happens.

6. In order to try and isolate the problem, change each field value to NULL
(except the PK and mandatory fields of course) and gradually add values
until the error occurs.
i.e. (4, NULL, NULL, NULL, NULL, NULL, NULL)
then (4, 2, NULL, NULL, NULL, NULL, NULL) etc.

if you get the same error straight away, then try changing the size of the
statement i.e. just insert by specifying one field.
e.g. insert into customer (id) values (2);

The basic idea is to reduce the above insert statement into the simplest
statement possible. Is this problem consistent on all SQL statements or
just "insert into" or just "insert into customer" etc.

HTH, Good Luck!
-Paul

--
Paul Henry paul.r...@btinternet.com
Non-Aristotelian Software Ltd em...@hotmail.com
Manchester M21 9FR ENGLAND, UK +44 (0)161-861 7159
"It happens sometimes. People just explode." - Repo Man