• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MS SQL Server and Java...

 
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I use MS SQL Server all day every day at work, but I do all my code development in
vb.net and C#.Net.

I'm currently studying for the scjp 1.5 and I was thinking about how one would go about
connecting to a SQL Server database and executing not only straight SQL queries, but also
Stored Procedures.

I've tried to look up some examples from google, but no good tutorials have come my way.

Just wanted to know if anyone knew of a good site or had a link to tutorials on this site.

Thanks,

Justin Fox
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Java offers the JDBC API for database connectivity.
HereĀ“s a tutorial: http://java.sun.com/docs/books/tutorial/jdbc/index.html

All what you need is a JDBC driver for your database. It is normally made available by the manfacturer of the database in question. Look around at their homepage. A self respected database manfacturer also provides good documentation with examples along the JDBC driver.
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I downloaded the JDBC for Microsoft SQL Server, but when i do the following:

"jdbc:sqlserver://ComputerName\\SQLEXPRESS;initial catalog=DataBasename;integratedsecurity=yes;"

it tells me that it is not setup for integrated security, the driver that is...

When it says:

"The JDBC driver supports the use of Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed.

The sqljdbc_auth.dll files are installed in the following location:

<installation directory>\sqljdbc_<version>\<language>\auth\"

Thats means just add it to the PATH environment variable right?

Thanks,

Justin
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes. Or configure your SQL Server instance to support SQL Server authentication. Much easier than trying to use windows integrated authentication.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The SQLServer JDBC driver also supports the usual username/password scheme. That may be easier to get going than mucking around with DLLs.
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sweet, I'll probabaly just do the integrated security thing for now, so I can hurry up and start testing
the JDBC methods and classes..

Thanks for the confirmation,

Justin Fox

P.S, Wouldn't it be easier to get the windows user logged into the system and validate that
against the integrated windows authentication? Not so much 'easier' but maybe more secure. Instead of using
a static 'sa' and 'password' under sql server authentication?

Just wondering...
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nevermind, I just changed my authentication mode to mixed and re-enabled sa, that was
a lot easier and it connected fine with out sqlexceptions...

Thanks,

Justin
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ok I've got a connection, and I can query the DB, but I don't understand how to pass parameters to a
stored procedure, in the documentation, they have this:

cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
cstmt.setInt(1, 50);

but don't you have to know the name of the parameter example: "@username"?

thanks,


Justin
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, the "1" in "setInt(1,...)" indicates that that value should replace the first parameter, i.e. the first question mark. It doesn't matter what the parameters are called in the stored proc definition.
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yep, I finally got that lol, but you can also make them named like so: (just incase anyone didn't know)

Also, they do something like the following:

Is this like executing a Scalar sql statement that has a return on insert?
And if so, what would the sql look like to make that "parameter" get populated.

I've tried the following:

...
@ItemNumber Int,
@parameter int
...
Insert Into Items (ItemNumber) Values (@ItemNumber);
set @parameter = 1
select @parameter
...

This doesn't seem to work, it tells me "Index 2 is out of range".

Thanks for all the help,

Justin
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Justin Fox wrote:

P.S, Wouldn't it be easier to get the windows user logged into the system and validate that
against the integrated windows authentication? Not so much 'easier' but maybe more secure. Instead of using
a static 'sa' and 'password' under sql server authentication?

Just wondering...



No. You end up with two locations where you configure authentication and authorisation - one in SQL Server the other in something like Active Directory (which you probably can't administer?). I would also never use the sa user; this user is special and has different capabilities from all other users. If you log in using your AD account you are piggy backing on the sa user (execute sp_who2 and you'll see how SQL Server manages the connection). SQL Server gives you quite rich functionality to manage permissions which you can't use if you log in with windows integrated security.
 
Justin Fox
Ranch Hand
Posts: 802
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Cool,

Yea I know how to make logins and then make users for a certain database using those logins and applying
certain schemas for those users.

Thanks for the info, I didn't know it was a bad thing to use the sa credentials for the db connection string.

Justin
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic