Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Call SQL server Stored procedure using JDBC in Java

 
Amol Gane
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

I've a stored procedure in SQL server that creates a unique job number everytime it runs. I need to run this SP from Mac. I've installed Oracle SQL developer on Mac. I need to write a Java code (using JDBC) to embed this SP. The same java code will be used in applescript to run the SP. I found the code snippet with the similar requirement. Having hard time figuring out the code to implement my SP. Any help is greatly appreciated. Below is my SP 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.


 
Amol Gane
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can somebody please give me a hand on this? Stuck with this for too long. Thanks
 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happens when you run it? Do you get an error message or stack trace?

Incidentally, it's usually "while (rset.next())" instead of if, but I don't know if that's the cause of your problem because we don't have enough information and I've never tried it with if.
 
Amol Gane
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Kevin Robbins,

I haven't tried running the below code. I found the code snippet online. I need to embed the below SP in the code snippet. I'm new to Java so kinda stuck with syntax. Can you please give me hand on it?

Here is my 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:
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Amol Gane wrote:Hi Kevin Robbins,

I haven't tried running the below code. I found the code snippet online. I need to embed the below SP in the code snippet. I'm new to Java so kinda stuck with syntax. Can you please give me hand on it?


And there's your problem.
Here's the relevant bit for PreparedStatements and how to use them, which should give you the base syntax.
Also the bit for calling stored procedures, which will show the call syntax.

J. Kevin Robbins wrote:
Incidentally, it's usually "while (rset.next())" instead of if, but I don't know if that's the cause of your problem because we don't have enough information and I've never tried it with if.

For something like the code given it's quite common as you'd only expect a single result back (I'm guessing it generates and ID).
Similarly with something like a login check, where you'd only expect a username to match a single row in a table.
 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Amol Gane wrote:
EXEC Int.dbo.GetNewJobNumber '6852', 'Test Job', 'Manual SQL Query'

If I'm reading that correctly, your procedure requires three parameters, but you are only passing one in the code. Read the links that Dave provided; that's a good start.

Dave, I'll try the "if" next time I have a query that only returns one record. I've never done it that way, but I'm always open to learning.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic