In order to support NULL parameter values, the <sql:param> should really use
PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType) or PreparedStatement.setNull(int parameterIndex, int sqlType) instead of PreparedStatement.setObject(int parameterIndex, Object x) and it should determine the value type as do other tags that coerce a value to a certain type. If it sees the value null or NULL or perhaps an undefined variable, it should set targetSqlType or sqlType to java.sql.Types.NULL. Derek -----Original Message----- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:28 AM To: Tag Libraries Users List; [EMAIL PROTECTED] Subject: Re: [JSTL] How do I enter a null value with <sql:param>? It certainly could be a driver issue, but you've gotta pass null instead of "null" to find out ;-). The Standard taglib uses the following to set SQL parameters: PreparedStatement.setObject(int parameterIndex, Object x) It should be pretty easy to write a simple JDBC test to see how your driver behaves when x is null. I suppose it might be worth considering an enhancement to <sql:param> along the lines of: <sql:param value="..." sqlType="NUMERIC"/> Where the sqlType attribute contains the name of a SQL type from java.sql.Types that Standard could use with: PreparedStatement.setNull(int parameterIndex, int sqlType) I suppose that if sqlType was supplied and value was non-null, then Standard would also use: PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType) Of course, that implies a JSTL spec change... Quoting Wolfgang Röckelein <[EMAIL PROTECTED]>: > Derek Mahar wrote: > > > Your solution is equivalent to Case 2 that I listed in my original > > message. The Microsoft SQL Server JDBC driver complains with a > > JspException: > > > > javax.servlet.ServletException: javax.servlet.jsp.JspException: > > INSERT INTO tblTest(a) VALUES (?) > > : [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error > > converting the nvarchar value 'null' to a column of data type int. > > > > However, the JDBC driver accepts and correctly processes the > > following > > query: > > > > <sql:update> > > INSERT INTO tblTestDerek VALUES (null) > > </sql:update> > > > > In light of this, I suspect that there may be an error in the > > implementation of <sql:param> where it treats "null" as a string > > argument rather than as an SQL null argument. > > Please search the (user & devel) list. We discussed this problem a > while ago and came to the conclusing that this is a problem caused by > the JDBC driver used. > > The subject might have been "SQL Update problem with Dates" > > Regards, > Wolfgang -- Kris Schneider <mailto:[EMAIL PROTECTED]> D.O.Tech <http://www.dotech.com/> --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
