AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Prepared statement and SQL functions          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message Steve Guest Posted: Sun Feb 19, 2006 11:12 pm    Post subject: Prepared statement and SQL functions Hi, I'm using a PreparedStatement for an INSERT operation and can set the values of ints and strings using the .set* methods. But some of the parameters are function calls to be executed at the database server. Example: I want to end up with: "INSERT INTO table ( id, name, last_update ) VALUES ( 1, 'Fred', NOW() )" [ NOW() is a MySQL function that returns the current date ] PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, ? )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); .... What do I do for parameter 3? Thanks for any help. -- Regards, Steve "...which means he created the heaven and the earth... in the DARK! How good is that?" Back to top Alan Krueger Guest Posted: Mon Feb 20, 2006 12:12 am    Post subject: Re: Prepared statement and SQL functions Steve wrote: Quote: Example: I want to end up with: "INSERT INTO table ( id, name, last_update ) VALUES ( 1, 'Fred', NOW() )" [ NOW() is a MySQL function that returns the current date ] PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, ? )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); Unlike C/C++, "Fred" is replaced with a String object not a character array. You don't need to explicitly construct a String object here. Quote: ... What do I do for parameter 3? java.sql.Timestamp, perhaps? Back to top Joe Weinstein Guest Posted: Mon Feb 20, 2006 2:12 am    Post subject: Re: Prepared statement and SQL functions Steve wrote: Quote: Hi, I'm using a PreparedStatement for an INSERT operation and can set the values of ints and strings using the .set* methods. But some of the parameters are function calls to be executed at the database server. Example: I want to end up with: "INSERT INTO table ( id, name, last_update ) VALUES ( 1, 'Fred', NOW() )" [ NOW() is a MySQL function that returns the current date ] PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, ? )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); ... What do I do for parameter 3? Just use JDBC SQL extensions. (Lots of prople don't know about them): // Use the standard JDBC SQL extension "{ now() }", which the // driver will *parse* and substitute what the DBMS would need. PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, {now()} )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); ps.executeUpdate(); Joe Weinstein at BEA Systems Quote: Thanks for any help. Back to top IchBin Guest Posted: Mon Feb 20, 2006 2:12 am    Post subject: Re: Prepared statement and SQL functions Alan Krueger wrote: Quote: Steve wrote: Example: I want to end up with: "INSERT INTO table ( id, name, last_update ) VALUES ( 1, 'Fred', NOW() )" [ NOW() is a MySQL function that returns the current date ] PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, ? )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); Unlike C/C++, "Fred" is replaced with a String object not a character array. You don't need to explicitly construct a String object here. ... What do I do for parameter 3? java.sql.Timestamp, perhaps? DEFINE the default value type for the column as a TIMESTAMP and then set the DEFAULT value as CURRENT_TIMESTAMP. This way it will always be created when you insert a row. You will not have to worry about updating it. -- Thanks in Advance... IchBin, Pocono Lake, Pa, USA http://weconsultants.servebeer.com/JHackerAppManager __________________________________________________________________________ 'If there is one, Knowledge is the "Fountain of Youth"' -William E. Taylor, Regular Guy (1952-) Back to top Lee Fesperman Guest Posted: Sat Mar 04, 2006 9:12 am    Post subject: Re: Prepared statement and SQL functions Joe Weinstein wrote: Quote: Just use JDBC SQL extensions. (Lots of prople don't know about them): // Use the standard JDBC SQL extension "{ now() }", which the // driver will *parse* and substitute what the DBMS would need. PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, {now()} )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); ps.executeUpdate(); The correct syntax for calling a Scalar Function using the SQL Extensions is: {fn now()} -- Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Back to top Steve Guest Posted: Sat Mar 04, 2006 11:12 am    Post subject: Re: Prepared statement and SQL functions On 4/3/06 08:35, in article 440950F4.47F (AT) ix (DOT) netcom.com, "Lee Fesperman" <firstsql (AT) ix (DOT) netcom.com> wrote: Quote: Joe Weinstein wrote: Just use JDBC SQL extensions. (Lots of prople don't know about them): // Use the standard JDBC SQL extension "{ now() }", which the // driver will *parse* and substitute what the DBMS would need. PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, {now()} )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); ps.executeUpdate(); The correct syntax for calling a Scalar Function using the SQL Extensions is: {fn now()} Ah, great. Thanks Lee. -- Regards, Steve "...which means he created the heaven and the earth... in the DARK! How good is that?" Back to top Joe Weinstein Guest Posted: Sat Mar 04, 2006 6:12 pm    Post subject: Re: Prepared statement and SQL functions Lee Fesperman wrote: Quote: Joe Weinstein wrote: Just use JDBC SQL extensions. (Lots of prople don't know about them): // Use the standard JDBC SQL extension "{ now() }", which the // driver will *parse* and substitute what the DBMS would need. PreparedStatement ps = conn.prepareStatement( "INSERT INTO table ( id, name, last_update ) VALUES ( ?, ?, {now()} )" ); ps.setInt( 1, new Integer( 1 ) ); ps.setString( 2, new String( "Fred" ) ); ps.executeUpdate(); The correct syntax for calling a Scalar Function using the SQL Extensions is: {fn now()} Thanks, right. Joe Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.