db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Noll <dan...@nuix.com>
Subject Re: Date - Timestamp format for inserts?
Date Mon, 20 Nov 2006 22:08:13 GMT
(Removing top posting.)

Marl Atkins wrote:
>>> What's wrong with this statement?
>>> INSERT INTO users
>>> (RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,
>>> City,S 
>>> tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,Acce
>>> ssLeve lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink 
>>> Systems, 
>>> Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
>>> L,NULL ,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )
>> 
>> Is there any particular reason you're not doing this the normal way?
>> 
>> (i.e. using ? and then setTimestamp(int,Timestamp))
 >
 > It would have to be because I don't know it.
 > Could you enlighten me?

This is JDBC basics but I'll answer anyway since I'm still drinking the 
morning coffee.

In your particular case... (ignore my wrapping, it won't actually be 
valid in Java so either put it on one line or concat the strings.)

PreparedStatement st = conn.prepareStatement(
   "INSERT INTO users (RecordID, CTMCClientID, OrgName, Prefix, FName,
                       MidInit, LName, Addr1, Addr2, City, State, Zip,
                       Phone, CellPhone, AltPhone, Fax, Email, Login,
                       Password, Status, AccessLevelID, DateCreated,
                       CreatedBy, DateExpired)
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                       ?, ?, ?, ?, ?, ?, ?, ?, ?)");

// more sets here...
st.setTimestamp(22, new Timestamp(...));
// more sets here...
st.executeUpdate();

In such a fashion we can insert dates without actually knowing the 
syntax for them... and also insert strings without actually knowing how 
to escape them. etc.

If it isn't immediately obvious, it's also possible to reuse that 
PreparedStatement (in fact it's recommended, if you're calling the 
statement multiple times.)

Daniel


-- 
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia    Ph: +61 2 9280 0699
Web: http://nuix.com/                               Fax: +61 2 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.

Mime
View raw message