ADO Bind Connection

I open a connection in my VB.Net Client and then i use exec ado bind connection statement in order to share the open connection with my Visual Cobol class and perform some exec sql statements. I close the connection in VB and Cobol and when i try to use the same connection again i get an sql error "Duplicate connection name". I'm sure that both connections (VB and Cobol) are closed and set to null before any attemp to reopen. Can you help please?  Thanx

  • Sounds like you are not doing a UNBIND of the connection in COBOL. When finished you should:-

              exec ado

                  unbind connection

              end-exec.

    that would free it up for reuse.

    Regards

    David

  • Which Visual COBOL product version are you using?

    What type of application is this? i.e. WinForm, WPF, WCF, ASP.NET, etc.

    You should not be closing the connection in both languages. COBOL should do an UNBIND as David states and then VB should do the Close.

    When do you get the duplicate connection message, when opening a second time in VB?

    Can you show us what this connect code looks like?

    Thanks.

  • This is VB part of the code. Now unbind statement is working but the next exec sql statement (execute stored procedure) raise an error with sqlcode=-1 and description "A severe error occurred on the current command. The results, if any, should be discarded."

    The Cobol Class (DBBridge) is following VB code.

       Private Sub cmdOk_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdOK.Click

           Dim ContWork As Boolean

           Dim WStat As Integer

           If Trim(txtUID.Text) > "" Then

               ADONET_ConnectionString = "User Id=" & txtUID.Text & ";Password=" & txtPWD.Text & ";"

           Else

               ADONET_ConnectionString = "Integrated Security=True;"

           End If

           frmMess.lblMess.Text = "Connecting to Database" & vbCrLf & "Please Wait ...."

           frmMess.ShowCenterParent(Me)

           Try

               VBDBConn = New SqlConnection(ADONET_ConnectionString)

               VBDBConn.Open()

               DBBridge = New DBBridge(VBDBConn, ADONET_ConnectionName)                     ' BIND CONNECTION

               If DBBridge.sqlmess.Trim <> "" Then

                   MsgBox("Connection Failed !!" & vbCrLf & DBBridge.sqlcd & " " & Trim(DBBridge.sqlmess) & " Connection=" & ADONET_ConnectionName)

                   If VBDBConn.State = ConnectionState.Open Then VBDBConn.Close()

                   VBDBConn = Nothing

                   DBBridge = Nothing

                   End

               End If

               DBBridge.DBName = ADONET_ConnectionName

               DBBridge.User = UserName

               DBBridge.Appl = ApplName

           Catch ex As Exception

               MsgBox("Connection Failed !!" & vbCrLf & ex.Message & " Connection=" & ADONET_ConnectionName)

               If VBDBConn.State = ConnectionState.Open Then VBDBConn.Close()

               VBDBConn = Nothing

               DBBridge = Nothing

               End

           End Try

    'second time connection

           Try

               If VBDBConn.State = ConnectionState.Open Then

                   DBBridge.Close()

                   VBDBConn.Close()

               End If

               VBDBConn = Nothing

               DBBridge = Nothing

               Dim myTime As DateTime = DateTime.Now

               VBDBConn = New SqlConnection(ADONET_ConnectionString)

               VBDBConn.Open()

               DBBridge = New DBBridge(VBDBConn, ADONET_ConnectionName)                     ' BIND CONNECTION

               If DBBridge.sqlmess.Trim <> "" Then

                   MsgBox("Connection Failed !!" & vbCrLf & DBBridge.sqlcd & " " & Trim(DBBridge.sqlmess) & " Connection=" & ADONET_ConnectionName)

                   If VBDBConn.State = ConnectionState.Open Then VBDBConn.Close()

                   VBDBConn = Nothing

                   DBBridge = Nothing

                   End

               End If

               DBBridge.DBName = ADONET_ConnectionName

               DBBridge.User = UserName

               DBBridge.Appl = ApplName

           Catch ex As Exception

               MsgBox("Connection Failed !!" & vbCrLf & ex.Message & " Connection=" & ADONET_ConnectionName)

               If VBDBConn.State = ConnectionState.Open Then VBDBConn.Close()

               VBDBConn = Nothing

               DBBridge = Nothing

               End

           End Try

           Me.Close()

       End Sub

    DBBridge

          $set ilusing "System".
          $set ilusing "System.Reflection".
          $set ilusing "System.Data".
          $set ilusing "System.Data.SqlClient".
           class-id DBBridge

           working-storage section.
           01  DBConn                          type SqlConnection.
           01  currentDBConn                   type SqlConnection.
           01  DBTran                          type SqlTransaction.
           01  currentDBTran                   type SqlTransaction.
           01  ConnectionInfo                  type PropertyInfo.
           01  currentTransactionProperty      type PropertyInfo.
           01  realTransactionProperty         type PropertyInfo.
           01  DBConnName                      string property no set as "ConnectionName".
           01  Bind_OK                         pic 9 value 0.
           01  Connect_OK                      pic 9 value 0.

           EXEC SQL INCLUDE SQLCA END-EXEC.
           01 SQLCA.
               05  SQLCAID         PIC X(8)         VALUE "SQLCA   ".
               05  SQLCABC         PIC S9(9) COMP-5 VALUE 136.
               05  SQLCODE         PIC S9(9) COMP-5 VALUE 0.
               05  SQLERRM.
                   49  SQLERRML    PIC S9(4) COMP-5.
                   49  SQLERRMC    PIC X(70).
               05  SQLERRP         PIC X(8).
               05  SQLERRD         PIC S9(9) COMP-5 OCCURS 6 VALUE 0.
               05  SQLWARN.
                   10  SQLWARN0    PIC X.
                   10  SQLWARN1    PIC X.
                   10  SQLWARN2    PIC X.
                   10  SQLWARN3    PIC X.
                   10  SQLWARN4    PIC X.
                   10  SQLWARN5    PIC X.
                   10  SQLWARN6    PIC X.
                   10  SQLWARN7    PIC X.
               05  SQLEXT.
                   10  SQLWARN8    PIC X.
                   10  SQLWARN9    PIC X.
                   10  SQLWARN10   PIC X.
                   10  SQLWARNA    REDEFINES SQLWARN10 PIC X .
               05  SQLSTATE    PIC X(5).

           01  MFSQLMESSAGETEXT                PIC X(255).

           01  sqlcd                           pic s9(10) comp-3 property no set as "sqlcd".
           01  sqlmes                          pic x(200) property no set as "sqlmess".

           01  Database                        pic x(255) property as "DBName".
          *01  dbconnection                    pic x(255).
          *01  dbuser                          pic x(50) property as "dbuser".
           01  Username                        pic x(50) property as "User".
           01  Computername                    pic x(50) property as "ComputerName".
           01  Connection-Name                 pic x(80) property as "Connection_Name" value "LifeConn".
          *01  Connection-Out                  pic x(255).
           01  wAppl                           pic x(40) property as "Appl".

           *>--------------------------------------------------------------------------
           *> Constructor
           *>--------------------------------------------------------------------------
           method-id new public.
           local-storage section.
           procedure division using by value conn as type SqlConnection
                                    by value connName as string.
               set sqlmes to "".
               if conn = null
                   set sqlcd to sqlcode
                   set sqlmes to connName & "=" & "Null Argument Exception (SqlConnection)"
                   goback.
               if connName = ""
                   set sqlcd to sqlcode
                   set sqlmes to connName & "=" & "Null Argument Exception (ConnectionName)"
                   goback.

               try
                   set DBConn to conn
                   set DBConnName to connName
                   exec ado
                       bind connection AdoConn to :DBConn with transaction :DBTran
                   end-exec
                  
                   if sqlcode <> 0
                       set sqlcd to sqlcode
                       set sqlmes to connName & "=" & MFSQLMESSAGETEXT
                       goback
                   end-if
                   if connName::ToLower()::Substring(0, 6) = "db2000"
                       declare eiStr as type String = "sp_setapprole 'lifeapp', 'lifeapp'"
                       exec sql
          *                call sp_setapprole ('lifeapp', 'lifeapp')
                           EXECUTE IMMEDIATE :eiStr
                       end-exec
                       if sqlcode < 0
                           set sqlcd to sqlcode
                           set sqlmes to connName & "=" & MFSQLMESSAGETEXT
                           goback
                       end-if
                   end-if
                   set Bind_OK to 1
               catch e as type System.Exception
                   raise new Exception(self::GetType()::Name & "::" & type MethodBase::GetCurrentMethod()::Name & e::Message)
               end-try.
               goback.
           end method.
           *>--------------------------------------------------------------------------
           *> Public Methods
           *>--------------------------------------------------------------------------
           method-id Close.
           procedure division.
               if Bind_OK = 1
                   if DBConn <> null
                       exec ado
                           unbind connection AdoConn
                       end-exec
                   end-if
               end-if.
               if Connect_OK = 1
                   exec sql
                       disconnect :Connection-Name
                   end-exec
               end-if.
               set DBConn to null.
               set DBTran to null.
           end method.

           end class.

  • I forgot to send that im working with Micro Focus Visual COBOL 2.2 Version 2.2.02100. This is a sample code from a windows application but the real use of that is from WCF Service in C# calling DBBridge dll

  • I forgot to send that im working with Micro Focus Visual COBOL 2.2 Version 2.2.02100. This is a sample code from a windows application but the real use of that is from WCF Service in C# calling DBBridge dll

  • In the BIND CONNECTION Statement you are using the host varialbe :DBTran. I can't see where you set the transaction object.

  • I think that the connection code is fine now. The error you are receiving appears to be due to the stored procedure you are calling.

    I found an article here that seems to address this error and it has to do with not calling sys.sp_UnsetAppRole when you are done which seems to spawn the error because of connection pooling.