Notes on using the MSOLEDBSQL and SNC interfaces
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 MSOLEDBSQL and SNC interfaces support 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:
123456BEGININSERT INTO t_1 values(1, 'sfdfs')INSERT INTO t_2 values(1, 'sfdfs')SELECT * FROM t_1SELECT * FROM t_2END -
Starting with the keyword DECLARE:
1234DECLARE @p1 int, @p2 varchar(50)SELECT @p1 = 1EXECUTE sp_4 @p1, @p2 OUTPUTSELECT @p2 AS 'output'
You can run the batch of SQL statements in the Database painter or
in PowerScript. For example:
|
1 2 3 4 5 6 7 8 9 10 |
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 MSOLEDBSQL and SNC interfaces pool 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:
|
1 |
OLE DB Services=-4 |
You can also type the following statement in code:
|
1 |
ProviderString='OLE DB Services=-4') |
Triggers and synonyms in the Database
painter
In the Objects view for MSOLEDBSQL and 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.