Forum Stats

  • 3,854,538 Users
  • 2,264,379 Discussions
  • 7,905,721 Comments

Discussions

Call SQL server stored procedure using JDBC in Java

2905651
2905651 Member Posts: 5
edited Mar 23, 2015 3:28PM in New To Java

Hello All,

I'm new to Java. I need to run a SQL server stored procedure(that creates a unique job number) from Oracle SQL Developer (JDBC) in Java. The same Java code will be used in Applescript to run the SP. I found a code snippet online with the similar requirement. Can somebody give me hand to embed my SP in below code snippet?  Below is the Stored Procedure and Code Snippet:

SP

EXEC Int.dbo.GetNewJobNumber '6852', 'Test Job', 'Manual SQL Query'

6852- CustomerCode,

Test Job - Job Title,

Manual SQL query - Shows how new job number was created.

Code Snippet:

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class Main {

  public static void main(String[] argv) throws Exception {

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",

        "USERID", "PASSWORD");

    CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");

    proc_stmt.setString(1, "employee");

    ResultSet rs = proc_stmt.executeQuery();

    if (rs.next()) {

      int employeeId = rs.getInt(1);

      System.out.println("Generated employeeId: " + employeeId);

    } else {

      System.out.println("Stored procedure couldn't generate new Id");

    }

  }

}

Tagged:

Answers

  • Unknown
    edited Mar 23, 2015 3:28PM
    I'm new to Java. I need to run a SQL server stored procedure(that creates a unique job number) from Oracle SQL Developer (JDBC) in Java.

    Sorry - that is NOT possible.

    Each vendor has their own JDBC jar file for working with their database and they are NOT compatible.

    You can NOT use Oracle's JDBC jar file to connect to Sql Server.

This discussion has been closed.