wood burning stoves 2.0*
The moose likes JDBC and the fly likes MS SQL Server and Java... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MS SQL Server and Java..." Watch "MS SQL Server and Java..." New topic
Author

MS SQL Server and Java...

Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
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


You down with OOP? Yeah you know me!
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Jan 24, 2006
Posts: 802
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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Yes. Or configure your SQL Server instance to support SQL Server authentication. Much easier than trying to use windows integrated authentication.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42367
    
  64
The SQLServer JDBC driver also supports the usual username/password scheme. That may be easier to get going than mucking around with DLLs.


Ping & DNS - my free Android networking tools app
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
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

Joined: Jan 24, 2006
Posts: 802
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

Joined: Jan 24, 2006
Posts: 802
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
Marshal

Joined: Mar 22, 2005
Posts: 42367
    
  64
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

Joined: Jan 24, 2006
Posts: 802
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 24, 2006
Posts: 802
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MS SQL Server and Java...