DB connector SQL question

[wmsTrebor]

I'm trying to test a simple database connector. I'm using the SQL Server/Sybase driver and have gotten as far as a valid connection. However the following SQL is generating an error:
INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrpwh, usrtyp,
usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, 123, ' ',
'${date.get('yyyy-MM-dd')}', 'WMSVISION', '[email protected]', ' ', ' ', 'EMPL',
' ', 'en_US - English')

The error that is occurring is:
java.sql.SQLException: Invalid parameter index 1. Query: INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrpwh, usrtyp,
usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, 123, ' ',
'?', 'WMSVISION', '[email protected]', ' ', ' ', 'EMPL',
' ', 'en_US - English')

Parameters: [2007-07-13]
--------------------
I've tried using a date escape of {d '${date.get('yyyy-MM-dd')}'} but that only changes the error to:
java.sql.SQLException: Invalid JDBC date escape at line position 181.
SimpleDbconnect.xml (4872 bytes)

[jbartels]

ReB connector SQL question

What datatype is the usruid column??

[wmsTrebor]

ReB connector SQL question

Here are how the column types. Note I've noticed that usrdiv was a character field and added quotes but still got the same error.
SQL> describe usr
Name Null? Type
----------------------------------------- -------- ----------------------------
USRUID NOT NULL VARCHAR2(10)
USRNAM NOT NULL VARCHAR2(30)
USRPWD NOT NULL VARCHAR2(10)
USRACC NOT NULL NUMBER(11)
USRDIV NOT NULL VARCHAR2(3)
USRSTS NOT NULL VARCHAR2(1)
USRLPCR NOT NULL DATE
USRCNB NOT NULL VARCHAR2(16)
USREML NOT NULL VARCHAR2(50)
USRPHN NOT NULL VARCHAR2(25)
USRFPH NOT NULL VARCHAR2(25)
USRPWH NOT NULL VARCHAR2(80)
USRTYP NOT NULL VARCHAR2(4)
USRCTYP NOT NULL VARCHAR2(4)
USRTLID NOT NULL VARCHAR2(40)

[nshaik]

ReB connector SQL question

Deos the INSERT work fine when you try from any other SQL client application?

[wmsTrebor]

ReB connector SQL question

After noticing a missing field and trying a few things here are the latest results:

If the date is hard coded as the following is, the insert works:
INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
usrtyp, usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, '123', ' ',
'2007-07-13', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
'EMPL', ' ', 'en_US - English')


But if we try to use a formatted date using the date.get method the insert fails:
INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
usrtyp, usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, '123', ' ',
'${date.get('yyyy-MM-dd')}', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
'EMPL', ' ', 'en_US - English')

FAILURE: Error writing to database:
SQLException
Invalid parameter index 1. Query: INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
usrtyp, usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, '123', ' ',
'?', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
'EMPL', ' ', 'en_US - English')

Parameters: [2007-07-16]

So it seems to be either a date specific problem or a parameter replacement problem.

[tlewis3462]

ReB connector SQL question

It looks like the problem is the datatype of the parameter. I like to do all datatype conversions within the SQL of the insert/update. In your case, the code

CONVERT(datetime,${date.get('yyyy-MM-dd')})

should work. This converts a string to a datetime usable by the DBMS. In my case, it's MS SQL Server 2000. Hope that helps a little.
-Tim

[wmsTrebor]

ReB connector SQL question

Thanks! Using CONVERT worked. Here is the final SQL for the destination tab.
INSERT IGNORE INTO usr (
usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
usrtyp, usrctyp, usrtlid
) values (
'joe', 'joe user', ' ', 0, '123', ' ',
CONVERT(datetime,${date.get('yyyy-MM-dd')}), 'WMSVISION', '[email protected]', ' ', ' ', ' ',
'EMPL', ' ', 'en_US - English')