Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Call SQL server Stored procedure using JDBC in Java

 
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
 
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • 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:
 
Master Rancher
Posts: 4664
49
  • 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
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • 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.
 
This looks like a job for .... legal tender! It says so right in this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic