JDBC Driver for Act! CRM

Build 24.0.8963

Calling Stored Procedures

Calling Stored Procedures

Act! CRM supports two classes of stored procedures:

  • To execute parameterized stored procedure calls, use CallableStatement objects.
  • To execute stored procedures as SQL statements using the EXEC syntax, use Statement objects.

Using Callable Statement Objects

You can use the generic execute method of the CallableStatement class to execute any stored procedure as a parameterized query.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the StoredProc stored procedure:

CallableStatement cstmt = conn.prepareCall("StoredProc");
cstmt.setString("Input1", "Value1");
boolean ret = cstmt.execute();   
if (!ret) {
  int count=cstmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=cstmt.getResultSet();
  while(rs.next()){
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}

Using Statement Objects

You can use the execute method of the Statement class to execute any stored procedure as an SQL statement.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the StoredProc stored procedure: (See EXECUTE Statements for more on the syntax.)

Statement stmt = conn.createStatement();
boolean ret = stmt.execute("EXEC StoredProc Input1 = 'Value1'");

if (!ret) {
  int count=stmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=stmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963