Q
Problem solve Get help with specific problems with your technologies, process and projects.

Calling a stored procedure from a Java-based application

Is it possible to call a SQL Server stored procedure from a Java-based application? In addition, what options are available to execute VB (Visual Basic) code from a SQL Server stored procedure?

These are two important questions as SQL Server is now being leveraged in more and different ways in organizations...

worldwide. With the recent announcements from Microsoft for the next version of SQL Server, code named "Yukon," these questions have great timing.

Yes, from a Java-enabled applet, application, or application server, a SQL Server 2000 stored procedure can be executed via Microsoft's JDBC driver support. In your Java code it is necessary to setup the appropriate connectivity, input and output parameters. Further ensure the JDBC Driver, Java Virtual Machine and TCPIP connectivity is installed and configured. Then in the Java code you would be able to call the code to execute a stored procedure.

For more information and to download the SQL Server 2000 JDBC driver visit:
http://www.microsoft.com/downloads/details.aspx?FamilyID=4F8F2F01-1ED7-4C4D-8F7B-3D47969E66AE&displaylang=en.

The ability to execute VB (Visual Basic) code with SQL Server 2000 can be accomplished in one of four ways:

  • Incorporating the code via calling the xp_cmdshell extended stored procedure with the application as an input parameter where any Windows operating system file can be called such as a batch file or *.exe
  • SQL-DMO (SQL Distributed Management Objects) is the object model leveraged by the SQL Server Tool set, i.e., Enterprise Manager and can be called via the sp_OACreate, sp_OAGetProperty, sp_OASetProperty, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OADestroy, etc.
  • Create an extended stored procedure that can execute a *.dll from SQL Server; for additional information review the article entitled "Creating Extended Stored Procedures" in SQL Server 2000 Books Online
  • For batch-level processing needs as opposed to OLTP (Online Transaction Process) scenarios, it is possible to execute a DTS Package with the ability to execute VB Script code or operating system files (batch, exe, etc) to meet business needs.
Stay tuned to additional programming language support in the next version of SQL Server. Yukon will support the CLR (Common Language Runtime) where any CLR compliant language (VB.NET, C#, etc.) can be compiled and executed in SQL Server stored procedures, functions, etc. For more information visit the " Overview of .NET Programming Features in SQL Server 'Yukon' Beta 1".

Further, be sure to check the new functionality with Transact- SQL (T-SQL) because it has been designed and tuned specifically for relational data access. With the expanding capabilities in Yukon, DBAs and Developers alike will have broader and deeper tool chest to leverage with the next version of SQL Server in the next 12 months. For more information review the "SQL Server 'Yukon' Beta 1 Transact-SQL Enhancements".


For news, advice and other information about SQL Server Development, click here.

This was last published in November 2003

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close