Skip to Content
avatar image
Former Member

PowerBuilder's connection to SQL Server

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 01 at 08:01 AM
    -1

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Mar 01 at 08:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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