Stored procedures are procedures that are stored on the database server and executed on request from an SQL client. Often the stored procedure executes several SQL queries against the tables of the database to yield the desired result. In Blackfish SQL, these SQL queries are written in the language of choice, that is available on the .NET or Java platforms. The desired effect may be to update a set of tables, or to calculate an accumulated value from one or more tables, or to add specialized integrity constraints. A stored procedure may have several parameters, which can be either input only, output only, or both.  

Example 

Consider an ADD_ORDER procedure that takes a customerId, an itemId, and a quantity value as input, and adds a record to the ORDERS table. However, suppose that you also want to verify that this customer has paid for previous orders. To achieve this, you can cause the procedure to throw an exception if this is not the case.  

The stored procedure is executed with an IDbCommand object by setting the properties CommandType and CommandText, and then adding the appropriate parameters.

Notice the difference in the interpretation of the parameters, depending on the combination of CommandType and the style of the parameter markers that are used. If the CommandType is StoredProcedure, the parameter names are taken from the implementation of the stored procedure, in which case it is possible to omit optional parameters.

A User Defined Function is a code snippet that is written to extend the built-in SQL support. Like stored procedures, they are executed on the database server and called from an SQL client. UDFs must return a value, and are usually written to be used in the WHERE clause of SELECT queries. However, a UDF may also be called by itself, similar to a stored procedure.  

Example 

Consider a MAX_VALUE function that takes two values, <value1> and <value2>, and returns the greater of the two. The UDF can be executed in an SQL statement:

'SELECT * FROM PEOPLE WHERE MAX_VALUE(HEIGHT,5*WIDTH) < ?'

Or, in an SQL CALL statement:

'?=CALL MAX_VALUE(?,?)'

This section provides detailed information on how to create Blackfish SQL stored procedures and UDFs for the .NET platform.

There are three steps involved in creating a Blackfish SQL stored procedure:

1. Write the code for the stored procedure as a static public member of a class.
2. Build an assembly with the stored procedures. Blackfish SQL must be able to locate the assembly. When developing in Delphi, Blackfish SQL is able to find the assembly in BDSCOMMONDIR. That is, it is not necessary to move the assembly to any special location. For deployment, it is recommended that you copy the assembly to the subdirectory where the executable for the Blackfish SQL server (BSQLServer.exe) resides, or install it in the Global Assembly Cache (GAC).
3. Create the binding of a SQL identifier to the assembly member.

Example 

This example uses the sample ADD_ORDER from the previous example in About Stored Procedures, with this schema:  

CUSTOMER TABLE  

ORDERS TABLE

ITEMS TABLE

1. Create a Delphi.NET package and name it MyProcs.dll.
2. Add a reference to System.Data.dll.
3. Add a unit:

P1 := Command.Parameters.Add('P1', DbType.Decimal);
P2 := Command.Parameters.Add('P2', DbType.Int32); 
P1.Direction := ParameterDirection.Output; 
P2.Value := CustId; 
Command.ExecuteNonQuery; 

if P1.Value = DBNull.Value then 

Owed := 0 

else

Owed := Decimal(P1.Value); 

Owed := Owed + Amount; 

Command.Parameters.Clear; 

Command.CommandText := 'SELECT CREDIT INTO ? FROM CUSTOMER WHERE CUST_ID=?';
P1 := Command.Parameters.Add('P1', DbType.Decimal);
P2 := Command.Parameters.Add('P2', DbType.Int32);
P1.Direction := ParameterDirection.Output;
P2.Value := CustId;
Command.ExecuteNonQuery;

Credit := Decimal(P1.Value);

if Owed > Credit then

raise Exception.Create('Customer doesn''t have that much credit'); 

Command.Parameters.Clear;

Command.CommandText := 'UPDATE ITEMS SET STOCK=STOCK-? WHERE ITEM_ID=?'; 
P1 := Command.Parameters.Add('P1', DbType.Int32); 
P2 := Command.Parameters.Add('P2', DbType.Int32); 
P1.Value := Quantity; 
P2.Value := ItemId; 
Command.ExecuteNonQuery; 

Command.Parameters.Clear;

Command.CommandText := 'INSERT INTO ORDERS (CUST_ID, ITEM_ID, QUANTITY, SALE_AMOUNT) '+ 'VALUES (?, ?, ?, ?)'; 
P1 := Command.Parameters.Add('P1', DbType.Int32); 
P2 := Command.Parameters.Add('P2', DbType.Int32);
P3 := Command.Parameters.Add('P3', DbType.Int32); 
P4 := Command.Parameters.Add('P4', DbType.Decimal);  

P1.Value := CustId;
P2.Value := ItemId;
P3.Value := Quantity;
P4.Value := Amount;
Command.ExecuteNonQuery;
Command.Free;
end;

end.

After completing the code for the stored procedure:

1. Build an assembly DLL (for example, Procs.dll) which contains the class MyClass shown in Step 1.
2. When deploying, copy the assembly to the subdirectory where the executable for the Blackfish SQL server (BSQLServer.exe) resides.

Now that the code is ready to be executed, the Blackfish SQL database must be made aware of the class member that can be called from SQL. To do this, start DataExporer and issue a CREATE METHOD statement:

CREATE METHOD ADD_ORDER AS 'MyProcs::SampleStoredProcedures.TMyClass.AddOrder'; 

MyProcs is the name of the package, and the method name is fully-qualified with unit name and class name. 

Execute the stored procedure ADD_ORDER from a Delphi Console application:

unit MyCompany;

interface

implementation
uses
  System.Data;

type
  TSomething = class
  public
    procedure AddOrder(
      Connection: DbConnection;
      CustId: Integer;
      ItemId: Integer;
      Quantity: Integer);
    end; 
    
{ Assume:
   Connection: is a valid Blackfish SQL connection.
   CustId:     is a customer in the CUSTOMER table.
   ItemId:     is an item from the ITEMS table. 
   Quantity:   is the quantity of this item ordered.
   }
   
procedure TSomething.AddOrder(
  Connection: DbConnection;
  CustId: Integer;
  ItemId: Integer;
  Quantity: Integer);
var
  Command: DbCommand;
  P1, P2, P3: DbParameter;
begin
   Command := con.CreateCommand;
   Command.CommandText := 'ADD_ORDER';
   Command.CommandType := CommandType.StoredProcedure;
   P1 := Command.Parameters.Add('custId', DbType.Int32);
   P2 := Command.Parameters.Add('itemId', DbType.Int32);
   P3 := Command.Parameters.Add('quantity', DbType.Int32);
   P1.Value := CustId;
   P2.Value := ItemId;
   P3.Value := Quantity;
   Command.ExecuteNonQuery;
   Command.Free;
  end;
  
 end.

When TSomeThing.AddOrder is called in the client application, this in turn calls the stored procedure ADD_ORDER, which causes TMyClass.AddOrder to be executed in the Blackfish SQL server process. By making TMyClass.AddOrder into a stored procedure, only one statement has to be executed over a remote connection. The five statements executed by TMyClass.AddOrder are executed in-process of the Blackfish SQL server, using a local connection.  

Note that the application is not passing a connection instance to the call of the ADD_ORDER stored procedure. Only the actual logical parameters are passed.  

Blackfish SQL generates an implicit connection object, when it finds a stored procedure or UDF where the first argument is expected to be a System.Data.IDbConnection instance.

The Delphi language supports output parameters and reference parameters. The Blackfish SQL database recognizes these types of parameters and treats them accordingly.  

Database NULL values require special handling. The System.String can be handled by the NULL value. However, for all other types, the formal parameter type must be changed to TObject, since NULL is not a valid value for a .NET ValueType. If the formal parameter is a TObject type, then the value of System.DBNull is used for a database NULL value. Blackfish SQL will also accept nullable types in stored procedures written in C# (for example, int).  

Examples: 

Example of a stored procedure with an INOUT parameter; NULL values are ignored:

class procedure TMyClass.AddFive(ref Param: Integer); 
begin 
  Param := Param + 5; 
end; 
     

Example of a stored procedure with an INOUT parameter; NULL values are kept as NULL values:

class procedure TMyClass.AddFour(ref Param: TObject); 
begin 
  if Param <> nil then 
    Param := TObject(Integer(Param) + 4); 
end; 
 

Use:

procedure TryAdding(Connection: DbConnection); 
var 
  Command: DbCommand; 
begin 
  Command := Connection.CreateCommand; 
  Command.CommandText := 'ADD_FIVE'; 
  Command.CommandType := CommandType.StoredProcedure; 
  P1 := Command.Parameters.Add('param', DbType.Int32); 
  P1.Direction := ParameterDirection.InputOutput; 
  P1.Value = 17; 
  Command.ExecuteNonQuery; 
    if 22 <> Integer(P1.Value) then 
      raise Exception.Create('Wrong result'); 

  Command.Parameters.Clear; 
  Command.CommandText := 'ADD_FOUR'; 
  Command.CommandType := CommandType.StoredProcedure; 
  P1 := Command.Parameters.Add('param', DbType.Int32); 
  P1.Direction := ParameterDirection.InputOutput; 
  P1.Value = 17; 
  Command.ExecuteNonQuery; 
    if 21 <> Integer(P1.Value) then 
      raise Exception.Create('Wrong result'); 
  
  P1.Value = DBNull.Value; 
  Command.ExecuteNonQuery; 
    if DbNull.Value <> P1.Value then 
      raise Exception.Create('Wrong result'); 
  
  Command.Free; 
end; 
  

The above implementation of AddFour uses a TObject wrapper class for integers. This allows the developer of addFour to recognize NULL values passed by Blackfish SQL, and to set an output parameter to NULL to be recognized by Blackfish SQL.  

In contrast, in the implementation for AddFive, it is impossible to know if a parameter was NULL, and it is impossible to set the result of the output parameter to NULL.

If for some reason an operator (for example: a bitwise AND operator) is needed for a where clause, and Blackfish SQL does not offer that operator, you can create one in Delphi, Visual Basic, C#, or C++ and call it as a UDF. However, use this capability with caution, since Blackfish SQL will not recognize the purpose of such a function, and will not be able to use any indices to speed up this part of the query.  

Consider the UDF example given earlier, involving the MAX_VALUE UDF:

'SELECT * FROM PEOPLE WHERE MAX_VALUE(HEIGHT,5*WIDTH) < ?'

That query is equivalent to this query:

'SELECT * FROM PEOPLE WHERE HEIGHT < ? AND 5*WIDTH < ?'

where the same value is given for both parameter markers. This SQL statement yields the same result, because the implementation of MAX_VALUE is known. However, Blackfish SQL will be able to use only indices available for the HEIGHT and WIDTH column for the second query. If there were no such indices, the performance of the two queries would be about the same. The advantage of writing a UDF occurs when functionality does not already exist in Blackfish SQL (for example: a bit wise AND operator).

To debug .NET stored procedures:

• When the protocol is in-process or not set
• When the protocol is TCP

To debug stored procedures when the protocol is in-process or not set:

1. Create a project to use for debugging. Using your favorite IDE, create a project that includes the client code of the application, the stored procedures, and a reference to the Borland.Data.BlackfishSQL.LocalClient.dll library.
2. Add breakpoints to the stored procedure(s). The debugger will handle the stored procedures in the same way as with the client code.

To debug stored procedures when the protocol is TCP: 

If your IDE supports remote debugging: 

Delphi will be able to attach to the Blackfish SQL Server process.

1. Compile the stored procedures with debug information.
2. Copy the assembly to the bin directory of the Blackfish SQL installation.
3. Start the client application in the debugger and attach to the server process.
4. Add breakpoints to the stored procedure(s). The debugger will handle the stored procedures in the same way as with the client code.

If your IDE does not support remote debugging:

1. Create a project to use for debugging. Set up the project to debug the server directly.
2. Create an executable that calls Borland.Data.DataStore.DataStoreServer.StartDefaultServer.
3. Add a breakpoint to the stored procedure.
4. Run the separate client process.

A stored procedure can produce an ADO.NET DbDataReader simply by returning a DbDataReader.  

class function GetRiskyCustomers(  
  Connection: DbConnection;  
  Credit: Decimal credit): DbDataReader;
var  
  Command: DbCommand;   
  P1: DbParameter;
begin  
  Command := Connection.CreateCommand;   
  Command.CommandText := 'SELECT NAME FROM CUSTOMER WHERE CREDIT > ? '; 
  P1 := Command.Parameters.Add('param', DbType.Decimal);   
  P1.Value := Credit;   
  Result := Command.ExecuteReader;
end; 

Note that the command object is not freed at the end of the method. If the command was freed, it would implicitly close the DbDataReader, which results in no data being returned from the stored procedure. Instead, Blackfish SQL closes the command implicitly after the stored procedure has been called. 

The GetRiskyCustomers stored procedure can be used as follows, in ADO:

function GetRiskyCustomers( 
  Connection: DbConnection): ArrayList; 
var
  Command: DbCommand; 
  Reader: DbReader; 
  List: ArrayList; 
begin 
  List := ArrayList.Create;
  Command := Connection.CreateCommand;  
  Command.CommandText := 'GETRISKYCUST';
  Command.CommandType := CommandType.StoredProcedure;
  P1 := Command.Parameters.Add('Credit', DbType.Decimal);
  P1.Value := 2000;
  Reader := Command.ExecuteReader;
  while Reader.Read do
    List.Add(Reader.GetString(0));
  Command.Free;
  Result := List;
end;

This section provides detailed information on how to create Blackfish SQL stored procedures and UDFs for the Java platform.

Stored procedures and UDFs for Blackfish SQL for Java must be written in Java. The compiled Java classes for stored procedures and UDFs must be added to the CLASSPATH of the Blackfish SQL server process in order to be available for use. This should give the database administrator a chance to control which code is added. Only public static methods in public classes can be made available for use.  

You can update the classpath for the Blackfish SQL tools by adding the classes to the <jds_home>/lib/storedproc directory.

• If the stored procedure consists of a .jar file, then place the jar file in <jds_home>/storedproc/lib/jars.
• If the stored procedure consists of one or more class files, place the class files in <jds_home>/storedproc/classes. For example, if your stored procedure file is com.acme.MyProc, then you would place it as: c:<jds_home>/lib/storedproc/classes/com/acme/MyProc.class

After a stored procedure or a UDF has been written and added to the CLASSPATH of the Blackfish SQL server process, use this SQL syntax to associate a method name with it:

CREATE JAVA_METHOD <method-name> AS <method-definition-string>

<method-name> is a SQL identifier such as INCREASE_SALARY and <method-definition-string> is a string with a fully qualified method name. For example:

com.mycompany.util.MyClass.increaseSalary

Stored procedures and UDFs can be dropped from the database by executing:

DROP JAVA_METHOD <method-name>

After a method is created, it is ready for use. The next example shows how to define and call a UDF.

This example defines a method that locates the first space character after a certain index in a string. The the first SQL snippet defines the UDF and and the second shows an example of how to use it.  

Assume that TABLE1 has two VARCHAR columns: FIRST_NAME and LAST_NAME. The CHAR_LENGTH function is a built-in SQL function.

package com.mycompany.util;
public class MyClass {
   public static int findNextSpace(String str, int start) {
     return str.indexOf(' ',start);
   }
}

CREATE JAVA_METHOD FIND_NEXT_SPACE AS
  'com.mycompany.util.MyClass.findNextSpace';

SELECT * FROM TABLE1
  WHERE FIND_NEXT_SPACE(FIRST_NAME, CHAR_LENGTH(LAST_NAME)) < 0;

A final type-checking of parameters is performed when the Java method is called. Numeric types are cast to a higher type if necessary in order to match the parameter types of a Java method. The numeric type order for Java types is:

1. double or Double
2. float or Float
3. java.math.BigDecimal
4. long or Long
5. int or Integer
6. short or Short
7. byte or Byte

The other recognized Java types are:

• boolean or Boolean
• String
• java.sql.Date
• java.sql.Time
• java.sql.Timestamp
• byte[]
• java.io.InputStream

Note that if you pass NULL values to the Java method, you cannot use the primitive types such as short and double. Use the equivalent encapsulation classes instead (Short, Double). A SQL NULL value is passed as a Java null value. 

If a Java method has a parameter or an array of a type that is not listed in the tables above, it is handled as SQL OBJECT type.

If a Java method parameter is an array of one of the recognized input types (other than byte[]), the parameter is expected to be an output parameter. Blackfish SQL passes an array of length 1 (one) into the method call, and the method is expected to populate the first element in the array with the output value. The recognized Java types for output parameters are:

• double[] or Double[]
• float[] or Float[]
• java.math.BigDecimal[]
• long[] or Long[]
• int[] or Integer[]
• short[] or Short[]
• Byte[] (but not byte[] since that is a recognized input parameter by itself)
• boolean[] or Boolean[]
• String[]
• java.sql.Date[]
• java.sql.Time[]
• java.sql.Timestamp[]
• byte[][]
• java.io.InputStream[]

Output parameters can be bound only to variable markers in SQL. All output parameters are essentially INOUT parameters, since any value set before the statement is executed is passed to the Java method. If no value is set, the initial value is arbitrary. If any of the parameters can output a SQL NULL (or have a valid NULL input), use the encapsulated classes instead of the primitive types. 

package com.mycompany.util;
public class MyClass {
   public static void max(int i1, int i2, int i3, int result[]) {
       result[0] = Math.max(i1, Math.max(i2,i3));
  } 
}

CREATE JAVA_METHOD MAX 
   AS 'com.mycompany.util.MyClass.max';

CALL MAX(1,2,3,?);

The CALL statement must be prepared with a CallableStatement in order to get the output value. See the JDBC documentation for how to use java.sql.CallableStatement. Note the assignment of result[0] in the Java method. The array passed into the method has exactly one element.

If the first parameter of a Java method is of type java.sql.Connection, Blackfish SQL passes a connection object that shares the transactional connection context used to call the stored procedure. This connection object can be used to execute SQL statements using the JDBC API. 

Do not pass anything for this parameter. Let Blackfish SQL do it. 

package com.mycompany.util;
public class MyClass {
    public static void increaseSalary(java.sql.Connection con,
       java.math.BigDecimal amount) {
    java.sql.PreparedStatement stmt 
        = con.prepareStatement("UPDATE EMPLOYEE SET SALARY=SALARY+?");
               stmt.setBigDecimal(1,amount);
               stmt.executeUpdate();
               stmt.close(); 
    }
}


CREATE JAVA_METHOD INCREASE_SALARY 
   AS 'com.mycompany.util.MyClass.increaseSalary';

CALL INCREASE_SALARY(20000.00);

Note:

• INCREASE_SALARY requires only one parameter: the amount by which to increase the salaries. The corresponding Java method has two parameters.
• Do not call commit(), rollback, setAutoCommit(), or close() on the connection object passed to stored procedures. For performance reasons, it is not recommended to use this feature for a UDF, even though it is possible.

A Java stored procedure can produce a ResultSet on the client by returning either a ResultSet or a DataExpress DataSet from the Java implementation of the stored procedure. The DataSet is automatically converted to a ResultSet for the user of the stored procedure. 

Example 

This example returns a ResultSet:

package com.mycompany.util;

public class MyClass {
    public static void getMarriedEmployees(java.sql.Connection con)
      java.sql.Statement stmt = con.getStatement();
      java.sql.ResultSet rset 
        = stmt.executeQuery("SELECT ID, NAME FROM EMPLOYEE 
                                WHERE SPOUSE IS NOT NULL");
      return rset;
}

Note: Do not close the stmt statement. This statement is closed implicitly. 

Example 

This example returns a DataSet, which is automatically converted to a ResultSet:

package com.mycompany.util;

public class MyClass {
    public static void getMarriedEmployees()
    com.borland.dx.dataset.DataSet dataSet = getDataSetFromSomeWhere();
    return dataSet;
}

Note: Do not close the stmt statement. This statement is closed implicitly. 

Example 

Register and call the previous examples like this:

java.sql.Statement stmt = connection.getStatement();
stmt.executeUpdate("CREATE JAVA_METHOD GET_MARRIED_EMPLOYEES AS "+
                   "'com.mycompany.util.MyClass.getMarriedEmployees'");
java.sql.ResultSet rset = stmt.executeQuery("CALL GET_MARRIED_EMPLOYEES()");
int id = rset.getInt(1);
String name = rset.getString(2);

Java methods can be overloaded to avoid numeric loss of precision. 

package com.mycompany.util;
public class MyClass {
  public static int abs(int p) {
    return Math.abs(p);
  }

  public static long abs(long p) {
    return Math.abs(p);
  }

  public static BigDecimal abs(java.math.BigDecimal p) {
    return p.abs();
  }

  public static double abs(double p) {
    return Math.abs(p);
  }
}


CREATE JAVA_METHOD ABS_NUMBER AS 'com.mycompany.util.MyClass.abs';

SELECT * FROM TABLE1 WHERE ABS(NUMBER1) = 2.1434;

The overloaded method abs is registered only once in the SQL engine. Now imagine that the abs method taking a BigDecimal is not implemented! If NUMBER1 is a NUMERIC with decimals, then the abs method taking a double would be called, which can potentially lose precision when the number is converted from a BigDecimal to a double.

The return value of the method is mapped into an equivalent SQL type. Here is the type mapping table:

Preface 

Overview 

System Architecture 

Establishing Connections 

Administering Blackfish SQL 

Using Blackfish SQL Security 

Using Triggers in Blackfish SQL Tables 

Stored Procedures Reference 

SQL Reference 

Optimizing Blackfish SQL Applications 

Deploying Blackfish SQL Database Applications 

Troubleshooting