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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

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 first published in November 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top