» Documentation » SQL Reference

User Procedures

User defined procedures let you create your own external routines. These routines are export functions, written in a programming language such as C/C++ and loaded via DLLs that ScimoreDB can dynamically load and run directly in the databases address space. Data modifications are run under current transaction's scope, i.e. if error or procedure aborts current transaction all changes will be rolled back.

Each user defined procedure accepts server connection CServerConnection instance as an input parameter. CServerConnection object will allow you to read parameters passed to procedure, execute SQL queries or Rollback transaction.

Register User Defined Procedure

Register DLL that exports one or more User Defined Procedures. module_name specify the reference name of the module and module_path specify the locations of the DLL.

ADD MODULE 'module_name','module_path' 

UnRegister User Defined Procedure

UnRegister DLL and unload library from database adress space.

DROP MODULE 'module_name' 

Execute User Defined Procedure

Execute exported user defined procedure myprocedure located in module_name DLL.

EXECUTE UDI [module_name@myprocedure] [parameter [, ...] ] 

Examples

Register, unregister module

ADD MODULE 'tpcc','d:\scimore\udi\uditpcc.dll'
DROP MODULE 'tpcc' 

Execute User Defined Procedure

EXECUTE UDI [tpcc@neworder] 0,1,0,0 

Prepared Command Text (C#):

sCmd.CommandType = CommandType.Text;
sCmd.CommandText = @"execute udi [tpcc@Payment]";
sCmd.Parameters.Clear();
sCmd.Parameters.Add("wid", ScimoreDbType.Int).Value = wid;
sCmd.Parameters.Add("did", ScimoreDbType.Int).Value = did;
sCmd.Parameters.Add("cid", ScimoreDbType.Int).Value = cid;
sCmd.Parameters.Add("cwid", ScimoreDbType.Int).Value=cwid;
sCmd.Parameters.Add("cdid", ScimoreDbType.Int).Value=cdid;
sCmd.Parameters.Add("amount", ScimoreDbType.Float).Value = hamount;
sCmd.ExecuteNonQuery();