Calling a MaxDB procedure from another application

[Former Member]

Hi Experts,

Does anyone know how to call a MaxDB stored procedure from outside of MaxDB, using the sapdbc.jar jdbc driver? I can call one successfully from Database Studio like this:

call DBADMIN.order_tot_amt (1, :o_id) (This simple stored procedure only returns a single value)

For example, this works with MSSQL:

DECLARE @Description Varchar(60)

Execute usp_getCatDesc 'Produce', @Description = @Description OUTPUT

Select @Description

Is there something similar for MaxDB? I have been unable to find any information after much searching.

Thanks,

Diana Hoppe

[lbreddemann · Accepted solution]

HI Diana,

if it's possible in DB Studio, then it's possible with JDBC

Let's see... create a dummy-dumm-dumm procedure:

CREATE DBPROC hotel.getHotelName (IN HNO FIXED, OUT HOTEL_NAME CHAR(50)) AS
SELECT name INTO:HOTEL_NAME FROM hotel.hotel WHERE hno = :hno;

Now use the super elaborated JDBC HelloWorld coding from the documentation to call this fine piece of MaxDB programming master art...

import java.sql.*;
public class HelloMaxDB
{
	public static void main(String[] args)
	throws ClassNotFoundException, SQLException
	{
		String username = "MONA";
		String password = "RED";
		String host = "";
		String dbname = "DB77";

  /*
   * Load JDBC Driver
   */
		Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");
  /*
   * Define Connection URL
   */
		String url = "jdbc:sapdb://" + host + "/" + dbname;

  /*
   * Connect to the Database
   */
		Connection connection = DriverManager.getConnection (url, username, password);

  /*
   * Execute SQL Statements
   */
		Statement stmt = connection.createStatement ();
		ResultSet resultSet = stmt.executeQuery ("SELECT * FROM HOTEL.CUSTOMER");
		resultSet.next ();
		String hello = resultSet.getString (1);
		System.out.println ("Hello String: "  + hello);
		resultSet.close ();

  /*
   * Letu2019s go for a stored procedure call, a.k.a callable statement
   * http://download.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/callablestatement.html#999652
   */
   
        CallableStatement cstmt = connection.prepareCall("{call hotel.getHotelName (?, ?)}");
	    cstmt.setInt(1, 10);
        cstmt.registerOutParameter(2, java.sql.Types.CHAR);
	
	    cstmt.execute();
	
		String hotelName = cstmt.getString (2);
		System.out.println ("Hotelname String: " + hotelName);

   /*
	* close all objects
    */
		stmt.close();
		connection.close ();
		}
}

Compile and run it...

C:\>javac -cp .;C:\sapdb\runtime\jar\sapdbc.jar HelloMaxDB.java

C:\>java -cp .;C:\sapdb\runtime\jar\sapdbc.jar HelloMaxDB
Hello String: 3000
Hotelname String: Congress

As Aerosmith put it: AMAZING

Best regards over the big ocean!

Lars

[Former Member · Accepted solution]

Hi Lars,

I must be driving you crazy with this! LOL

It's hard to explain, but the application I work on uses jdbc to issue queries to the MaxDB database. Of course, this process occurs under the hood, it's a matter of finding the right "pieces" to build the statement to send to the driver. I will look over the code you sent, and, at the very least, I can pass it on to our developers to compare with our current code for building the call statement, perhaps they need to add additional code for MaxDb stored procedure calls.

Thank you as usual Lars, your expertise is greatly appreciated

- Diana

[lbreddemann · Accepted solution]

Hi Diana,

as long as this is just rather easy stuff, that simply hasn't been documented well enough, I'm enjoying a little diversion from the way more difficult stuff (at least for me ) I have to deal with nowadays 😄

I of course don't know how you call procedures up to now, but the code I presented is pure standard JDBC coding and not MaxDB specific.

If further questions or problems arise... you obviously know how to get the answers

cheers,

Lars