Re: Exception comes from where?
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Thu, 08 Sep 2005 21:56:45 -0700
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.createException(Ljava.lang.String;Ljava.lang.String;I)Ljava.sql.SQLException;(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Ljava.sql.SQLException;II[Ljava.lang.String;Ljava.lang.String;I)Ljava.sql.SQLException;(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate()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.
.
- References:
- Exception comes from where?
- From: PJ Pugh
- Exception comes from where?
- Prev by Date: Exception comes from where?
- Next by Date: Re: Exception comes from where?
- Previous by thread: Exception comes from where?
- Next by thread: Re: Exception comes from where?
- Index(es):
Relevant Pages
|
