Notes on using the SNC interface

Using the DBHandle PowerScript function

The DBHandle function on the Transaction object returns the IUnknown* interface of the current session object. You can use this interface to query any interface in the session object. The interface is not locked by pIUnknown->Addref() in PowerBuilder, therefore you should not call the pIUnknown->Release() to free the interface after using it.

SQL batch statements

The SNC interface supports SQL batch statements. However, they must be enclosed in a BEGIN...END block or start with the keyword DECLARE:

Enclosed in a BEGIN...END block:

BEGIN
INSERT INTO t_1 values(1, 'sfdfs')
INSERT INTO t_2 values(1, 'sfdfs')
SELECT * FROM t_1
SELECT * FROM t_2
END

Starting with the keyword DECLARE:

DECLARE @p1 int, @p2 varchar(50)
SELECT  @p1 = 1
EXECUTE  sp_4 @p1, @p2 OUTPUT
SELECT @p2  AS  'output'

You can run the batch of SQL statements in the Database painter or in PowerScript. For example:

String batchSQL //contains a batch of SQL statements
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :batchSQL ;
OPEN DYNAMIC my_cursor ;
//first result set
FETCH my_cursor INTO . . .
//second result set
FETCH my_cursor INTO . . 
. . .
CLOSE my_cursor ;

Connection pooling

The SNC interface pools connections automatically using OLE DB pooling. To disable OLE DB pooling, type the following in the Extended Properties box on the Connection tab page in the Database Profile Setup dialog box:

OLE DB Services=-4

You can also type the following statement in code:

ProviderString='OLE DB Services=-4')

Triggers and synonyms in the Database painter

In the Objects view for SNC profiles in the Database painter, triggers display for tables in the Tables folder and Microsoft SQL Server 2005 synonyms display for tables and views.