Highlighted
Absent Member.
Absent Member.
3413 views

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

0 Likes
7 Replies
Highlighted
Absent Member.
Absent Member.

RE: ADO Bind Connection

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

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: ADO Bind Connection

I have already did that. Doesn't seem to work.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: ADO Bind Connection

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.

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: ADO Bind Connection

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.

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: ADO Bind Connection

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

0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: ADO Bind Connection

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

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: ADO Bind Connection

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.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.