PowerBuilder's connection to SQL Server

[Former Member]

I am converting a PowerBuilder classic app (PB ver, 12.6) from a Sybase ASE database to a SQL Server database. My question is about Multiple Active Result Sets (MARS) in a SQL Server connection, from a PowerBuilder classic app. SQLCA.DBMS = "ADO"

I am new to PowerBuilder.

I am experienced with ADO.NET

The first exception I ran into was "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.". This was the result from Execute Immediate "Begin Transaction" Using SqlCa. This lead me to explicitly specify "MultipleActiveResultSets=false" in SqlCa.DBParm

So with MARS disabled I reran the app. The begin transaction statement was successful, but another error came up from somewhere (I think the DataWindow Framework). The error is "There is already an open DataReader associated with this Command which must be closed first.".

I have seen this open DataReader error before, in my ADO.NET experience.

These two errors, one with MARS enabled, and one with MARS disabled, leads me to think that the PowerBuilder classic framework is expecting the MARS feature of a SQL Server connection to be enabled.

Am I right that MARS must be enabled on a SQL Server connection from a PB classic app?

[cpollach]

Hi David;

The MARS feature AFAIK is only supported by the SQLServer Native Client driver. A quick check on this aspect would be to change your transaction object, as follows:

• From: SQLCA.DBMS = "ADO"
• To: SQLCA.DBMS = "SNC"

HTH

Regards .. Chris

[CobyKako]

Hi Chris,

You're probably right because MARS was only mentioned for the SNC interface in the PowerBuilder online help.
Indeed, by reading the Connecting to Your Database manual, you can see at the "SQL Server 2005 features" section:

Multiple Active Result Sets
The SNC interface supports Multiple Active Result Sets (MARS), which enable applications to have multiple default result sets open and to interleave reading from them. Applications can also execute statements such as INSERT, UPDATE, and DELETE and stored procedure calls while default result sets are open.

Regards,
Jacob

[CobyKako]

Hello David,

Let's focus on your your first exception 🙂

If you issue an EXECUTE IMMEDIATE to control your own transaction with Begin Transaction, you must explicitly either commit or rollback the transaction because the SQLCA AutoCommit is set to True by default.

Check the documentation for AutoCommit which explains this:

Using EXECUTE IMMEDIATE When AutoCommit is set to True, you can use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, and other SQL statements to control your own transaction processing. If you use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, you must use the EXECUTE IMMEDIATE dynamic SQL statement to issue a corresponding COMMIT TRANSACTION or ROLLBACK TRANSACTION.

For information about using the EXECUTE IMMEDIATE statement, see the PowerScript Reference

Hope this helps,

Kind regards,
Jacob

[Former Member]

Hi Jacob,

Thanks for your answer, however ignoring my question and focusing on the first exception doesn't answer my question about MARS.

But since you focused on that, some example code could be helpful.

sqlca.AutoCommit = True
...
execute immediate "BEGIN TRANSACTION" using SQLCA;
if sqlca.sqlcode <> 0 then  //  if not successful show error message and return
	gf_sqlca_load_msg()
	gf_show_msg()       //  This is where the first error shows up (with MARS enabled)
	return
end if
//  Update database table - If successful Commit else Rollback
if mf_update_window(dm_actwin) = 1 then    //  Inside this function is where the second error shows up (with MARS disabled)
	execute immediate "COMMIT TRANSACTION" using SQLCA;
	if sqlca.sqlcode <> 0 then
		gf_sqlca_load_msg()
		gf_show_msg()
	end if
else    //  Table update failed - Rollback
	execute immediate "ROLLBACK TRANSACTION" using SQLCA;
	if sqlca.sqlcode <> 0 then
		gf_sqlca_load_msg()
		gf_show_msg()
	end if
end if

As you can see I do handle the transaction with Commit or Rollback.

Any idea about MARS? Have you heard if PowerBuilder expects MARS to be enabled on SQL Server connections?

Best regards,

David

[CobyKako]

Hi David,
I haven't heard you must turn on the MARS feature with PowerBuilder absolutely.
However, when MARS is switched off, I've seen from my PB experience that connection pooling has to be disabled also:

SQLCA.DBMS="TRACE SNC" // TRACE keyword here is used to enable database tracing
SQLCA.DBParm="ProviderString='MultipleActiveResultSets=false;OLE DB Services=-4'"

Regards,
Jacob