Can't seem to UPDATE dBase table

Thread view:

Glenn Robinson - 18 Jun 2004 17:16 GMT

Hello,

I'm using jdbc-odbc bridge to try and update a .dbf table.

My code is as follows:

stmt.executeUpdate(
"INSERT INTO sas.dbf VALUES ('1234', '1234567', '1', '1234',
'12:34:56', '2004-06-18', '1','1234',)");

When I run it I get a syntax error in INSERT INTO statement, the following
in my ODBC trace file:

<snip>
java 45c-538 ENTER SQLSetStmtOption
HSTMT 08E421D8
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000001

java 45c-538 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 08E421D8
UWORD 7 <SQL_CONCURRENCY>
SQLPOINTER 0x00000001 (BADMEM)

java 45c-538 ENTER SQLFreeStmt
HSTMT 08E421D8
UWORD 0 <SQL_CLOSE>

java 45c-538 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 08E421D8
UWORD 0 <SQL_CLOSE>

java 45c-538 ENTER SQLExecDirect
HSTMT 08E421D8
UCHAR * 0x008EC9C8 [ -3] "INSERT INTO sas.dbf VALUES
('1234', '1234567', '1', '1234', '12:34:56', '2004-06-18', '1','1234',)\ 0"
SDWORD -3

java 45c-538 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 08E421D8
UCHAR * 0x008EC9C8 [ -3] "INSERT INTO sas.dbf VALUES
('1234', '1234567', '1', '1234', '12:34:56', '2004-06-18', '1','1234',)\ 0"
SDWORD -3

DIAG [37000] [Microsoft][ODBC dBase Driver] Syntax error in INSERT INTO
statement. (-3502)

java 45c-538 ENTER SQLErrorW
HENV 00000000
HDBC 00000000
HSTMT 08E421D8
WCHAR * 0x0007F900 (NYI)
SDWORD * 0x0007F944
WCHAR * 0x0007F500
SWORD 300
SWORD * 0x0007F940

java 45c-538 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 00000000
HDBC 00000000
HSTMT 08E421D8
WCHAR * 0x0007F900 (NYI)
SDWORD * 0x0007F944 (-3502)
WCHAR * 0x0007F500 [ 69] "[Microsoft][ODBC dBase Driver]
Syn"
SWORD 300
SWORD * 0x0007F940 (69)

java 45c-538 ENTER SQLErrorW
HENV 00000000
HDBC 00000000
HSTMT 08E421D8
WCHAR * 0x0007F900 (NYI)
SDWORD * 0x0007F944
WCHAR * 0x0007F500
SWORD 300
SWORD * 0x0007F940

java 45c-538 EXIT SQLErrorW with return code 100
(SQL_NO_DATA_FOUND)
HENV 00000000
HDBC 00000000
HSTMT 08E421D8
WCHAR * 0x0007F900 (NYI)
SDWORD * 0x0007F944
WCHAR * 0x0007F500
SWORD 300
SWORD * 0x0007F940

<snip>

All columns are character apart from the 6th one which is a date type and I
suspect this is what's causing me a problem.

Can anyone shed any light on this?

Thanks

Signature

Glenn

Reply to this Message

Colin 't Hart - 18 Jun 2004 18:04 GMT

> I'm using jdbc-odbc bridge to try and update a .dbf table.
>
[quoted text clipped - 3 lines]
> "INSERT INTO sas.dbf VALUES ('1234', '1234567', '1', '1234',
> '12:34:56', '2004-06-18', '1','1234',)");

<snip>

I would always specify the columns that you are inserting into.

It may also give you more clues as to what is going on.

Cheers,

Colin

Reply to this Message

Glenn Robinson - 18 Jun 2004 18:53 GMT

> > I'm using jdbc-odbc bridge to try and update a .dbf table.
> >
[quoted text clipped - 13 lines]
>
> Colin

Tried that already, didn't make any difference.

Signature

Glenn

Reply to this Message

Gerd Nachtsheim - 18 Jun 2004 19:13 GMT

>>>I'm using jdbc-odbc bridge to try and update a .dbf table.
>>>
[quoted text clipped - 6 lines]
>
>>>'12:34:56', '2004-06-18', '1','1234',)");

You may want to try a date escape sequence for the date column(#6)
representation, i.e.

{d '2004-06-18' }

instead of the barenaked string '2004-06-18'

HTH

Gerd

Signature

Gerd Nachtsheim mailto:gerdn@users.sourceforge.net ICQ:#13126958

Reply to this Message

mromarkhan@rogers.com - 19 Jun 2004 01:21 GMT

> Hello,
> stmt.executeUpdate(
> "INSERT INTO sas.dbf VALUES ('1234', '1234567', '1', '1234',
> '12:34:56', '2004-06-18', '1','1234',)");

Peace be unto you.

Somebody might already have posted the
correct solution.
However, I noticed the dangling comma at the end
of your statement.

Just a guess. It may be that you are right.

Have a good day.

Signature

http://www.hti.umich.edu/k/koran/browse.html

Reply to this Message

Liz - 19 Jun 2004 05:36 GMT

> > Hello,
> > stmt.executeUpdate(
[quoted text clipped - 14 lines]
> --
> http://www.hti.umich.edu/k/koran/browse.html

Is sas.dbf a String that contains the table name?

Reply to this Message

Glenn Robinson - 19 Jun 2004 12:01 GMT

> > Hello,
> > stmt.executeUpdate(
[quoted text clipped - 11 lines]
>
> Have a good day.

Oh, how foolish do I feel. It was the dangling comma a the end

Thanks from a very embarrassed person

Signature

Glenn