SQL SERVER 200x: Passing parameters to Stored Procedures - HELP !!
OK, who's got it ?!?!?!
Where's the secret hidden manual which describes, with examples, how to write a JAVA application getting data out of a simple SQL Server 200x database by passing parameters to stored procedures ? :-)
I have a simple JAVA program doing all the basic database xactions, ie., SELECT, INSERT, UPDATE & DELETE.
I had all these hard-coded in the JAVA app, then decided to move them into SPs in the DB. I tested each one inside MS SQL Server Management Studio before creating them as SPs so i know the logic works.
The problem I am having is that every time i try to call one of them from inside my JAVA application, i get the following error : "Cannot convert nvarchar to bigint".
The SPs take a string nvarchar as a 1st parm to identify which table to work on. The 2nd parm is a bigint GUID. Other SPs have additional parms but i'm always getting this error.
A typical call from within the JAVA app is "exec sp_xxx 'strTable', intGUID"
I do not have an ODBC connection, am using SQLSERVER
String connectionUrl = "jdbc:sqlserver://yadda yadda yadda";
String strSQL = "exec sp_xxx '" + strTblNm + "', " + getUID();
// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
// Establish the connection.
con = DriverManager.getConnection(connectionUrl);
// execute the SQL statement
stmt = con.createStatement();
nResult = stmt.executeUpdate(strSQL);
Stepping over this last statement results in the "Cannot convert nvarchar to bigint" error message.
The SP is simple as well :
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_xxx_delete_row]
-- delete the row
declare @SQL nvarchar(100)
set @SQL = N'DELETE FROM ' + quotename(@TableName) + ' WHERE Unique_ID=' + @UniqueID
exec sp_executesql @SQL
if @@error<>0 return(1) else return(0)
Sorry if this is obvious to the more-experienced users out there, i'm kind of teaching myself all of this stuff with very little help from recent documentation.
This is 95% of what we need the JAVA app to do so i can get out of everyone's hair if we can get this small seemingly easy issue resolved.
Any help would be appreciated.