Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (herse.apache.org: local policy)
From: "Marl Atkins" <marl@softlinksys.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Date - Timestamp format for inserts?
Date: Mon, 20 Nov 2006 17:11:34 -0500
Message-ID: <00ec01c70cf0$d7573710$6401a8c0@matkins>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AccM8RwD8IRC4iV9RfechHoYywIx/wAAEhmg
In-Reply-To: <4562274D.4040708@nuix.com>

Thanks:) 

-----Original Message-----
From: Daniel Noll [mailto:daniel@nuix.com] 
Sent: Monday, November 20, 2006 5:08 PM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

(Removing top posting.)

Marl Atkins wrote:
>>> What's wrong with this statement?
>>> INSERT INTO users
>>> (RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr
>>> 2,
>>> City,S
>>> tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,Ac
>>> ce ssLeve lID,DateCreated,CreatedBy,DateExpired ) 
>>> VALUES(1,NULL,'SoftLink Systems, 
>>> Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
>>> UL 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.