Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2009
    Posts
    4
    Rep Power
    0

    Default 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;
    try
    {
    // Establish the connection.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLSer verDriver");
    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
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --
    CREATE PROCEDURE [dbo].[sp_xxx_delete_row]
    @TableName nvarchar(50),
    @UniqueId bigint
    AS
    BEGIN
    -- 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)
    END


    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.
    tia
    Last edited by BobZ_Annapolis; 03-09-2009 at 05:58 PM.

Similar Threads

  1. Executing stored procedures
    By mew in forum JDBC
    Replies: 3
    Last Post: 08-20-2008, 05:47 PM
  2. Stored Procedures
    By geeta_ravikanti in forum JDBC
    Replies: 1
    Last Post: 04-22-2008, 02:34 AM
  3. Replies: 0
    Last Post: 02-11-2008, 08:47 AM
  4. Calling SQL Server stored procedures
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-07-2008, 08:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •