Re: [JSTL] How do I enter a null value with sql:param?
Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code is of tremendous benefit. Thank you for pointing me in the right direction and being patient with my posts as I worked through my problem. Derek -Original Message- From: Wolfgang Rckelein [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:40 AM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Thanks for digging this out. Both problems boil down to the case that when setObject(index, null) is passed in a null value this should be converted by the driver to an SQL null does not work with the JDBC driver (perhaps only for certain data types, which could be the cause why you see it only with sql:dateParam). From my experience I know that the MS SQL JDBC driver has/had problems in this area... Regards, Wolfgang - 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 prsent courriel contient des renseignements de nature privilgie et confidentielle et nest destin qu' la personne qui il est adress. Si vous ntes pas le destinataire prvu, vous tes par les prsentes aviss que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reu ce courriel par erreur, veuillez en aviser immdiatement lexpditeur et le supprimer de votre systme. Notez que la transmission de courriel ne peut en aucun cas tre considr comme inviolable ou exempt derreur puisque les informations quil contient pourraient tre intercepts, corrompues, perdues, dtruites, arrives en retard ou incompltes ou contenir un virus. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [JSTL] How do I enter a null value with sql:param?
I need to clarify the first statement that I made in my previous post (see below). I claim that parameter sqlType in method PreparedStatement.setNull(int parameterIndex, int sqlType) is redundant because a NULL SQL parameter has no intrinsic type. A NULL SQL value applies to any column type, so it is unnecessary to identify its type. Derek -Original Message- From: Derek Mahar Sent: May 20, 2004 2:08 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? Thank you. I appreciate your attention and quick response. After reading JDBC Specification Section 13.2.2.3, Setting NULL Parameters, I understand now why Kris suggested that the JSTL specification add the sqlType attribute to sql:param: method PreparedStatement.setNull(int parameterIndex, int sqlType) requires the type of the parameter that you want to set to NULL. However, why does method setNull require this parameter? A NULL parameter has no intrinsic type! In order to determine the type of a NULL parameter, you must consider its expression or assignment context. That is, in order to determine the type of a NULL parameter, must consider the type of the column to which you are assigning the parameter in a SET clause or the column or literal value to which you are comparing the parameter in a WHERE clause. Or, you must assign it a type using a new attribute like sqlType. According to JDBC Specification Section 13.2.3, Describing Outputs and Inputs of a PreparedStatement Object, ParameterMetaData.getParameterMetaData(int param) returns the type of each parameter in a parameterized query. What type does this method return for a NULL parameter? If it does, in fact, return a NULL parameter's contextual type, then I can change the sql:param implementation so that it invokes PreparedStatement.setNull instead of PreparedStatement.setObject for NULL parameters. Otherwise, I cannot see how I can supply setNull with a correct type. Consequently, I'll just have to wait for Microsoft to fix its JDBC driver so that it correctly handles the case PreparedStatement.setObject(index,null). Derek -Original Message- From: Justyna Horwat [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 1:10 PM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code is of tremendous benefit. Thank you for pointing me in the right direction and being patient with my posts as I worked through my problem. Derek -Original Message- From: Wolfgang Röckelein [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:40 AM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.h tml Thanks for digging this out. Both problems boil down to the case that when setObject(index, null) is passed in a null value this should be converted by the driver to an SQL null does not work with the JDBC driver (perhaps only for certain data types, which could be the cause why you see it only with sql:dateParam). From my experience I know that the MS SQL JDBC driver has/had problems in this area... Regards, Wolfgang - 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
RE: [JSTL] How do I enter a null value with sql:param?
On a slightly different topic, how about trying a different driver: http://jtds.sourceforge.net/ Quoting Derek Mahar [EMAIL PROTECTED]: I need to clarify the first statement that I made in my previous post (see below). I claim that parameter sqlType in method PreparedStatement.setNull(int parameterIndex, int sqlType) is redundant because a NULL SQL parameter has no intrinsic type. A NULL SQL value applies to any column type, so it is unnecessary to identify its type. Derek -Original Message- From: Derek Mahar Sent: May 20, 2004 2:08 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? Thank you. I appreciate your attention and quick response. After reading JDBC Specification Section 13.2.2.3, Setting NULL Parameters, I understand now why Kris suggested that the JSTL specification add the sqlType attribute to sql:param: method PreparedStatement.setNull(int parameterIndex, int sqlType) requires the type of the parameter that you want to set to NULL. However, why does method setNull require this parameter? A NULL parameter has no intrinsic type! In order to determine the type of a NULL parameter, you must consider its expression or assignment context. That is, in order to determine the type of a NULL parameter, must consider the type of the column to which you are assigning the parameter in a SET clause or the column or literal value to which you are comparing the parameter in a WHERE clause. Or, you must assign it a type using a new attribute like sqlType. According to JDBC Specification Section 13.2.3, Describing Outputs and Inputs of a PreparedStatement Object, ParameterMetaData.getParameterMetaData(int param) returns the type of each parameter in a parameterized query. What type does this method return for a NULL parameter? If it does, in fact, return a NULL parameter's contextual type, then I can change the sql:param implementation so that it invokes PreparedStatement.setNull instead of PreparedStatement.setObject for NULL parameters. Otherwise, I cannot see how I can supply setNull with a correct type. Consequently, I'll just have to wait for Microsoft to fix its JDBC driver so that it correctly handles the case PreparedStatement.setObject(index,null). Derek -Original Message- From: Justyna Horwat [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 1:10 PM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code is of tremendous benefit. Thank you for pointing me in the right direction and being patient with my posts as I worked through my problem. Derek -Original Message- From: Wolfgang Röckelein [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:40 AM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.h tml Thanks for digging this out. Both problems boil down to the case that when setObject(index, null) is passed in a null value this should be converted by the driver to an SQL null does not work with the JDBC driver (perhaps only for certain data types, which could be the cause why you see it only with sql:dateParam). From my experience I know that the MS SQL JDBC driver has/had problems in this area... Regards, Wolfgang -- Kris Schneider mailto:[EMAIL PROTECTED] D.O.Tech http://www.dotech.com
RE: [JSTL] How do I enter a null value with sql:param?
I'm merely aware of its existence. I've never used jTDS (or SQL Server) but just wanted to let you know about a potential alternative... Quoting Derek Mahar [EMAIL PROTECTED]: Thank you for the referral! Does the jTDS driver correctly implement PreparedStatement.setObject(index, null) (i.e. set the selected SQL parameter to NULL)? Have you encountered any serious flaws using this driver? Derek -Original Message- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 2:57 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? On a slightly different topic, how about trying a different driver: http://jtds.sourceforge.net/ Quoting Derek Mahar [EMAIL PROTECTED]: I need to clarify the first statement that I made in my previous post (see below). I claim that parameter sqlType in method PreparedStatement.setNull(int parameterIndex, int sqlType) is redundant because a NULL SQL parameter has no intrinsic type. A NULL SQL value applies to any column type, so it is unnecessary to identify its type. Derek -Original Message- From: Derek Mahar Sent: May 20, 2004 2:08 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? Thank you. I appreciate your attention and quick response. After reading JDBC Specification Section 13.2.2.3, Setting NULL Parameters, I understand now why Kris suggested that the JSTL specification add the sqlType attribute to sql:param: method PreparedStatement.setNull(int parameterIndex, int sqlType) requires the type of the parameter that you want to set to NULL. However, why does method setNull require this parameter? A NULL parameter has no intrinsic type! In order to determine the type of a NULL parameter, you must consider its expression or assignment context. That is, in order to determine the type of a NULL parameter, must consider the type of the column to which you are assigning the parameter in a SET clause or the column or literal value to which you are comparing the parameter in a WHERE clause. Or, you must assign it a type using a new attribute like sqlType. According to JDBC Specification Section 13.2.3, Describing Outputs and Inputs of a PreparedStatement Object, ParameterMetaData.getParameterMetaData(int param) returns the type of each parameter in a parameterized query. What type does this method return for a NULL parameter? If it does, in fact, return a NULL parameter's contextual type, then I can change the sql:param implementation so that it invokes PreparedStatement.setNull instead of PreparedStatement.setObject for NULL parameters. Otherwise, I cannot see how I can supply setNull with a correct type. Consequently, I'll just have to wait for Microsoft to fix its JDBC driver so that it correctly handles the case PreparedStatement.setObject(index,null). Derek -Original Message- From: Justyna Horwat [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 1:10 PM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code is of tremendous benefit. Thank you for pointing me in the right direction and being patient with my posts as I worked through my problem. Derek -Original Message- From: Wolfgang Röckelein [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:40 AM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param
RE: [JSTL] How do I enter a null value with sql:param?
Wow! I just installed the jTDS JDBC driver for Microsoft SQL Server, and it does *not* produce the NULL parameter problem that I encountered using Microsoft's SQL Server JDBC driver. I can now pass sql:param a JSTL null value and it will update the corresponding column with an SQL NULL value. Thank you again for this jTDS reference! Derek -Original Message- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 3:21 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? I'm merely aware of its existence. I've never used jTDS (or SQL Server) but just wanted to let you know about a potential alternative... Quoting Derek Mahar [EMAIL PROTECTED]: Thank you for the referral! Does the jTDS driver correctly implement PreparedStatement.setObject(index, null) (i.e. set the selected SQL parameter to NULL)? Have you encountered any serious flaws using this driver? Derek -Original Message- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 2:57 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? On a slightly different topic, how about trying a different driver: http://jtds.sourceforge.net/ Quoting Derek Mahar [EMAIL PROTECTED]: I need to clarify the first statement that I made in my previous post (see below). I claim that parameter sqlType in method PreparedStatement.setNull(int parameterIndex, int sqlType) is redundant because a NULL SQL parameter has no intrinsic type. A NULL SQL value applies to any column type, so it is unnecessary to identify its type. Derek -Original Message- From: Derek Mahar Sent: May 20, 2004 2:08 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? Thank you. I appreciate your attention and quick response. After reading JDBC Specification Section 13.2.2.3, Setting NULL Parameters, I understand now why Kris suggested that the JSTL specification add the sqlType attribute to sql:param: method PreparedStatement.setNull(int parameterIndex, int sqlType) requires the type of the parameter that you want to set to NULL. However, why does method setNull require this parameter? A NULL parameter has no intrinsic type! In order to determine the type of a NULL parameter, you must consider its expression or assignment context. That is, in order to determine the type of a NULL parameter, must consider the type of the column to which you are assigning the parameter in a SET clause or the column or literal value to which you are comparing the parameter in a WHERE clause. Or, you must assign it a type using a new attribute like sqlType. According to JDBC Specification Section 13.2.3, Describing Outputs and Inputs of a PreparedStatement Object, ParameterMetaData.getParameterMetaData(int param) returns the type of each parameter in a parameterized query. What type does this method return for a NULL parameter? If it does, in fact, return a NULL parameter's contextual type, then I can change the sql:param implementation so that it invokes PreparedStatement.setNull instead of PreparedStatement.setObject for NULL parameters. Otherwise, I cannot see how I can supply setNull with a correct type. Consequently, I'll just have to wait for Microsoft to fix its JDBC driver so that it correctly handles the case PreparedStatement.setObject(index,null). Derek -Original Message- From: Justyna Horwat [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 1:10 PM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code
RE: [JSTL] How do I enter a null value with sql:param?
One of these days (when I have the time) I'll be contacting Oracle to see if they can fix their driver. There's no other alternative JDBC drivers like this for Oracle are there? Keith -- Original Message --- From: Derek Mahar [EMAIL PROTECTED] To: Tag Libraries Users List [EMAIL PROTECTED] Sent: Thu, 20 May 2004 16:02:02 -0400 Subject: RE: [JSTL] How do I enter a null value with sql:param? Wow! I just installed the jTDS JDBC driver for Microsoft SQL Server, and it does *not* produce the NULL parameter problem that I encountered using Microsoft's SQL Server JDBC driver. I can now pass sql:param a JSTL null value and it will update the corresponding column with an SQL NULL value. Thank you again for this jTDS reference! Derek -Original Message- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 3:21 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? I'm merely aware of its existence. I've never used jTDS (or SQL Server) but just wanted to let you know about a potential alternative... Quoting Derek Mahar [EMAIL PROTECTED]: Thank you for the referral! Does the jTDS driver correctly implement PreparedStatement.setObject(index, null) (i.e. set the selected SQL parameter to NULL)? Have you encountered any serious flaws using this driver? Derek -Original Message- From: Kris Schneider [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 2:57 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? On a slightly different topic, how about trying a different driver: http://jtds.sourceforge.net/ Quoting Derek Mahar [EMAIL PROTECTED]: I need to clarify the first statement that I made in my previous post (see below). I claim that parameter sqlType in method PreparedStatement.setNull(int parameterIndex, int sqlType) is redundant because a NULL SQL parameter has no intrinsic type. A NULL SQL value applies to any column type, so it is unnecessary to identify its type. Derek -Original Message- From: Derek Mahar Sent: May 20, 2004 2:08 PM To: Tag Libraries Users List Subject: RE: [JSTL] How do I enter a null value with sql:param? Thank you. I appreciate your attention and quick response. After reading JDBC Specification Section 13.2.2.3, Setting NULL Parameters, I understand now why Kris suggested that the JSTL specification add the sqlType attribute to sql:param: method PreparedStatement.setNull(int parameterIndex, int sqlType) requires the type of the parameter that you want to set to NULL. However, why does method setNull require this parameter? A NULL parameter has no intrinsic type! In order to determine the type of a NULL parameter, you must consider its expression or assignment context. That is, in order to determine the type of a NULL parameter, must consider the type of the column to which you are assigning the parameter in a SET clause or the column or literal value to which you are comparing the parameter in a WHERE clause. Or, you must assign it a type using a new attribute like sqlType. According to JDBC Specification Section 13.2.3, Describing Outputs and Inputs of a PreparedStatement Object, ParameterMetaData.getParameterMetaData(int param) returns the type of each parameter in a parameterized query. What type does this method return for a NULL parameter? If it does, in fact, return a NULL parameter's contextual type, then I can change the sql:param implementation so that it invokes PreparedStatement.setNull instead of PreparedStatement.setObject for NULL parameters. Otherwise, I cannot see how I can supply setNull with a correct type. Consequently, I'll just have to wait for Microsoft to fix its JDBC driver so that it correctly handles the case PreparedStatement.setObject(index,null). Derek -Original Message- From: Justyna Horwat [mailto:[EMAIL PROTECTED] Sent: May 20, 2004 1:10 PM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Derek, I forwarded your comments to the JDBC specification lead. He said that now is a good time to bring up all of these annoyances in the specification. He will look into clarifying this area in the upcoming version of the spec. Thanks, Justyna Derek Mahar wrote: After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject
RE: [JSTL] How do I enter a null value with sql:param?
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.
Derek
-Original Message-
From: Kris Schneider [mailto:[EMAIL PROTECTED]
Sent: May 18, 2004 9:10 PM
To: Tag Libraries Users List
Subject: Re: [JSTL] How do I enter a null value with sql:param?
sql:param value=${null}/
Derek Mahar wrote:
How do I enter a null value into a table column with sql:param? The
JSTL 1.1 specification states for sql:param that, If value is null,
the parameter is set to the SQL value NULL. However, no matter what
value I pass to sql:param, I'm unable to set a null column:
Case 1: Set value to undefined variable
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest(a) VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
not supported by this driver.
Case 2: Set value to null
c:set var=nullValue value=null/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
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.
Cast 3: Set value to empty string
c:set var=nullValue value=/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
Microsoft SQL Server inserts a new row with column a set to 0.
Any suggestions?
Derek
--
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 nest 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 lexpé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 derreur puisque les informations
quil 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]
RE: [JSTL] How do I enter a null value with sql:param?
It's really not equivalent. Your Case 2 was:
c:set var=nullValue value=null/
Which sets nullValue to the String literal null. I suggested the equivalent
of:
c:set var=nullValue value=${null}/
Which will set nullValue to null (meaning that nullValue will actually be
removed from page scope). Did you actually try it and got the same error?
Quoting Derek Mahar [EMAIL PROTECTED]:
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.
Derek
-Original Message-
From: Kris Schneider [mailto:[EMAIL PROTECTED]
Sent: May 18, 2004 9:10 PM
To: Tag Libraries Users List
Subject: Re: [JSTL] How do I enter a null value with sql:param?
sql:param value=${null}/
Derek Mahar wrote:
How do I enter a null value into a table column with sql:param? The
JSTL 1.1 specification states for sql:param that, If value is null,
the parameter is set to the SQL value NULL. However, no matter what
value I pass to sql:param, I'm unable to set a null column:
Case 1: Set value to undefined variable
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest(a) VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
not supported by this driver.
Case 2: Set value to null
c:set var=nullValue value=null/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
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.
Cast 3: Set value to empty string
c:set var=nullValue value=/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
Microsoft SQL Server inserts a new row with column a set to 0.
Any suggestions?
Derek
--
Kris Schneider mailto:[EMAIL PROTECTED]
D.O.Tech http://www.dotech.com/
--
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]
RE: [JSTL] How do I enter a null value with sql:param?
Correction: the table name should have read tblTest rather than
tblTestDerek.
-Original Message-
From: Derek Mahar
Sent: May 19, 2004 9:45 AM
To: Tag Libraries Users List
Subject: RE: [JSTL] How do I enter a null value with sql:param?
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.
Derek
-Original Message-
From: Kris Schneider [mailto:[EMAIL PROTECTED]
Sent: May 18, 2004 9:10 PM
To: Tag Libraries Users List
Subject: Re: [JSTL] How do I enter a null value with sql:param?
sql:param value=${null}/
Derek Mahar wrote:
How do I enter a null value into a table column with sql:param? The
JSTL 1.1 specification states for sql:param that, If value is null,
the parameter is set to the SQL value NULL. However, no matter what
value I pass to sql:param, I'm unable to set a null column:
Case 1: Set value to undefined variable
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest(a) VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
not supported by this driver.
Case 2: Set value to null
c:set var=nullValue value=null/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
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.
Cast 3: Set value to empty string
c:set var=nullValue value=/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
Microsoft SQL Server inserts a new row with column a set to 0.
Any suggestions?
Derek
--
Kris Schneider mailto:[EMAIL PROTECTED]
D.O.Tech http://www.dotech.com/
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 nest 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 lexpé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 derreur puisque les informations
quil 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]
Re: [JSTL] How do I enter a null value with sql:param?
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 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [JSTL] How do I enter a null value with sql:param?
This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Keith -- Original Message --- From: Wolfgang Röckelein [EMAIL PROTECTED] To: Tag Libraries Users List [EMAIL PROTECTED] Sent: Wed, 19 May 2004 16:05:14 +0200 Subject: Re: [JSTL] How do I enter a null value with sql:param? 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 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- End of Original Message --- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
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]
Re: [JSTL] How do I enter a null value with sql:param?
Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Thanks for digging this out. Both problems boil down to the case that when setObject(index, null) is passed in a null value this should be converted by the driver to an SQL null does not work with the JDBC driver (perhaps only for certain data types, which could be the cause why you see it only with sql:dateParam). From my experience I know that the MS SQL JDBC driver has/had problems in this area... Regards, Wolfgang - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [JSTL] How do I enter a null value with sql:param?
You're right. It is not equivalent because I do not get the same error:
sql:update
INSERT INTO tblTest VALUES (?)
sql:param value=${null}/
/sql:update
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
not supported by this driver.
So why does the JDBC driver accept a null embedded directly in the
query, but not the null value passed via sql:param?
Derek
-Original Message-
From: Kris Schneider [mailto:[EMAIL PROTECTED]
Sent: May 19, 2004 9:56 AM
To: Tag Libraries Users List
Subject: RE: [JSTL] How do I enter a null value with sql:param?
It's really not equivalent. Your Case 2 was:
c:set var=nullValue value=null/
Which sets nullValue to the String literal null. I suggested the
equivalent
of:
c:set var=nullValue value=${null}/
Which will set nullValue to null (meaning that nullValue will actually
be removed from page scope). Did you actually try it and got the same
error?
Quoting Derek Mahar [EMAIL PROTECTED]:
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.
Derek
-Original Message-
From: Kris Schneider [mailto:[EMAIL PROTECTED]
Sent: May 18, 2004 9:10 PM
To: Tag Libraries Users List
Subject: Re: [JSTL] How do I enter a null value with sql:param?
sql:param value=${null}/
Derek Mahar wrote:
How do I enter a null value into a table column with sql:param?
The
JSTL 1.1 specification states for sql:param that, If value is
null,
the parameter is set to the SQL value NULL. However, no matter
what
value I pass to sql:param, I'm unable to set a null column:
Case 1: Set value to undefined variable
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest(a) VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type
is
not supported by this driver.
Case 2: Set value to null
c:set var=nullValue value=null/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
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.
Cast 3: Set value to empty string
c:set var=nullValue value=/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
Microsoft SQL Server inserts a new row with column a set to 0.
Any suggestions?
Derek
--
Kris Schneider mailto:[EMAIL PROTECTED]
D.O.Tech http://www.dotech.com/
--
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 nest 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 lexpé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 derreur puisque les informations
quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées
en retard ou
RE: [JSTL] How do I enter a null value with sql:param?
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 nest 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 lexpé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 derreur puisque les informations quil 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]
RE: [JSTL] How do I enter a null value with sql:param?
Following this thread, I came across the following post that states that according to the JDBC specification, invoking PreparedStatement.setObject(index, null) should set a nullable column to NULL: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06987.html However, nowhere does the documentation for java.sql.PreparedStatement.setObject discuss NULL values: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int, java.lang.Object) It states: The given argument will be converted to the corresponding SQL type before being sent to the database, but it says nothing about how it handles a null object reference. Derek -Original Message- From: Keith [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:28 AM To: [EMAIL PROTECTED] Subject: Re: [JSTL] How do I enter a null value with sql:param? This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Keith -- Original Message --- From: Wolfgang Röckelein [EMAIL PROTECTED] To: Tag Libraries Users List [EMAIL PROTECTED] Sent: Wed, 19 May 2004 16:05:14 +0200 Subject: Re: [JSTL] How do I enter a null value with sql:param? 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 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- End of Original Message --- - 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 nest 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 lexpé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 derreur puisque les informations quil 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]
RE: [JSTL] How do I enter a null value with sql:param?
According to this post, the case is closed for the JSTL implementation, but I suspect that so many JDBC drivers do not match the JDBC spec because the JDBC API documentation does not mention how the method handles the case PreparedStatement.setObject(index, null). http://www.mail-archive.com/[EMAIL PROTECTED]/msg06989.html Derek -Original Message- From: Keith [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:28 AM To: [EMAIL PROTECTED] Subject: Re: [JSTL] How do I enter a null value with sql:param? This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Keith -- Original Message --- From: Wolfgang Röckelein [EMAIL PROTECTED] To: Tag Libraries Users List [EMAIL PROTECTED] Sent: Wed, 19 May 2004 16:05:14 +0200 Subject: Re: [JSTL] How do I enter a null value with sql:param? 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 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- End of Original Message --- - 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 nest 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 lexpé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 derreur puisque les informations quil 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]
RE: [JSTL] How do I enter a null value with sql:param?
After reading Section 17.7 of the JDBC specification (available at http://java.sun.com/products/jdbc/download.html), I now agree that there is a problem with the Microsoft SQL JDBC driver, but there is also a problem with the official Java JDBC API documentation that does not mention how PreparedStatement.setObject() handles the case PreparedStatement.setObject(index, null). I guess we should ask Sun to correct (or complete) its JDBC API documentation and I need to ask Microsoft to fix its JDBC driver. However, I still need to fix my problem now. So, as I have Standard Tag Library source code, but do not have the source of the Microsoft SQL Server JDBC driver, I will likely change the sql:param implementation so that it invokes PreparedStatement.setNull() when it receives a null value. Here is a perfect case where having source code is of tremendous benefit. Thank you for pointing me in the right direction and being patient with my posts as I worked through my problem. Derek -Original Message- From: Wolfgang Röckelein [mailto:[EMAIL PROTECTED] Sent: May 19, 2004 10:40 AM To: Tag Libraries Users List Subject: Re: [JSTL] How do I enter a null value with sql:param? Keith wrote: This was on the Users list and was my problem. But mine had to do specifically with the sql:dateParam and the Oracle JDBC driver. The regular sql:param worked fine for me when I passed it a null value. Link to the starting thread in the archive: http://www.mail-archive.com/[EMAIL PROTECTED]/msg06748.html Thanks for digging this out. Both problems boil down to the case that when setObject(index, null) is passed in a null value this should be converted by the driver to an SQL null does not work with the JDBC driver (perhaps only for certain data types, which could be the cause why you see it only with sql:dateParam). From my experience I know that the MS SQL JDBC driver has/had problems in this area... Regards, Wolfgang - 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 nest 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 lexpé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 derreur puisque les informations quil 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]
Re: [JSTL] How do I enter a null value with sql:param?
sql:param value=${null}/
Derek Mahar wrote:
How do I enter a null value into a table column with sql:param? The
JSTL 1.1 specification states for sql:param that, If value is null,
the parameter is set to the SQL value NULL. However, no matter what
value I pass to sql:param, I'm unable to set a null column:
Case 1: Set value to undefined variable
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO tblTest(a) VALUES (?)
: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
not supported by this driver.
Case 2: Set value to null
c:set var=nullValue value=null/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
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.
Cast 3: Set value to empty string
c:set var=nullValue value=/
sql:update
INSERT INTO tblTest(a) VALUES (?)
sql:param value=${nullValue}/
/sql:update
Output:
Microsoft SQL Server inserts a new row with column a set to 0.
Any suggestions?
Derek
--
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]
