Exception comes from where?

Being new to SQLServer, I may be doing something very basic wrong, but here's
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:

>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.

> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:

> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:

> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?

> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.

> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>