Results 1 to 7 of 7

Thread: Properly close all active connections to database DB2

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2013
    Posts
    61

    Properly close all active connections to database DB2

    Code:
          Public Function IBMDatatable(SelectCommand$) As DataTable
    
            Dim dt As New DataTable
    
            Using cn As New iDB2Connection
                cn.ConnectionString = "Data Source=" & SYSTEMID & ";User Id=" & UserID & ";Password=" & Password & ";"
                Dim dtAdapter = New iDB2DataAdapter()
                dtAdapter.SelectCommand = New iDB2Command(SelectCommand, cn)
                dtAdapter.Fill(dt)
    
            End Using
    
            Return dt
    
        End Function
    Hi there, using the function above and the below code for example.. I make several calls to an IBM iseries DB2 database.

    Code:
    Dim recsales As String = "SELECT * from recsales"
    
            Dim dtsale As DataTable = IBMDatatable(recsales)
    The code runs without issue the first time, then if I try to run the same code again I get an error on
    Code:
    dtAdapter.Fill(dt)
    and i think it's because the previous connection didn't close properly.

    I have tried including cn.close() and cn.dispose() into my code but it still doesn't close the connection properly. If I check the cn.State.ToString() inside my function it says "Closed" but when I look on the iseries I can still see an idle connection.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,430

    Re: Properly close all active connections to database DB2

    Quote Originally Posted by VB.NETGAL View Post
    if I try to run the same code again I get an error
    If only there was a way for us to know what that error was.

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2013
    Posts
    61

    Re: Properly close all active connections to database DB2

    I guess the error would help ha.

    When i first run the code it creates a "job" on the iseries database and it's this instance that makes me think its not being killed properly
    Name:  1.png
Views: 696
Size:  929 Bytes

    the second time I run the code I get the error here
    Name:  2.jpg
Views: 761
Size:  23.3 KB

    Details of the error show this
    Name:  3.png
Views: 701
Size:  3.0 KB

    If I manually end the connection/job server side and run the code for the second time I receive no error.

    Previously I didn't have my connection inside a "using" statement and I can run the code as many times as I want without error but the only issue is it creates a load of idle connections server side which i have to end manually as they build up


    Any help appreciated.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,430

    Re: Properly close all active connections to database DB2

    To clarify, you're saying that you don't get an exception thrown if you don't use a Using statement but with the Using statement the exception is thrown, right? Is there an inner exception?

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2013
    Posts
    61

    Re: Properly close all active connections to database DB2

    Quote Originally Posted by jmcilhinney View Post
    To clarify, you're saying that you don't get an exception thrown if you don't use a Using statement but with the Using statement the exception is thrown, right? Is there an inner exception?
    Yes... if I remove the using... then I'm guessing it just creates new objects each time?

    Without the using statement because I use the same function to pull data 3 times using different select statements in the same click event i end up with 3 idle connections on the iseries.

    This is how i had my code without the using statement
    Code:
     Public Function IBMDatatable(SelectCommand$) As DataTable
    
            Dim dt As New DataTable
    
            Dim cn As New iDB2Connection
            cn.ConnectionString = "Data Source=" & SYSTEMID & ";User Id=" & UserID & ";Password=" & Password & ";"
            cn.Open()
            '''''''''''''''''''''''''''''''''''''''''''
            Dim dtAdapter = New iDB2DataAdapter()
            dtAdapter.SelectCommand = New iDB2Command(SelectCommand, cn)
            dtAdapter.Fill(dt)
    
            Return dt
    
        End Function
    I don't think there is any other detail on the error and that error message is true and it's the server disconnecting me because it's using the same object reference or something?

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,953

    Re: Properly close all active connections to database DB2

    It's going to create a new object, whether you use Using or not... but...

    What you SHOULD be doing is creating ONE connection, run your first select, run the second, run the thrid, then CLOSE (explicitly) the connect...

    Code:
     Public Function IBMDatatable(SelectCommand$, dbConn as iDB2Connection) As DataTable
    
            Dim dt As New DataTable
    
            Dim dtAdapter = New iDB2DataAdapter()
            dtAdapter.SelectCommand = New iDB2Command(SelectCommand, dbConn)
            dtAdapter.Fill(dt)
    
            Return dt
    
        End Function
    
    public sub GetData() 'Or what ever you want to call it.
            Using cn As New iDB2Connection("Data Source=" & SYSTEMID & ";User Id=" & UserID & ";Password=" & Password & ";")
                cn.Open()
    
                dt1 = IBMDataTable("select 1 here", cn)
                dt2 = IBMDataTable("select 2 here", cn)
                dt3 = IBMDataTable("select 3 here", cn)
    
                cn.Close
            End Using
    End Sub
    IT sounds like when you're allowing the connection to dispose, something isn't releasing the connection on the server end, so the pool fills up, and eventually you run out of resources. So, since you're doing multiple selects, ideally, you would connect once, run all your db operations, close the connection, then allow it to be disposed.

    I don't think the problem is the Using itself, but how it's being used. Personally, I prefer to explicitly open and close the connections and not rely on the object's default behavior of opening and closing (partially because I can't remember which ones do and don't).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2013
    Posts
    61

    Re: Properly close all active connections to database DB2

    Quote Originally Posted by techgnome View Post
    It's going to create a new object, whether you use Using or not... but...

    What you SHOULD be doing is creating ONE connection, run your first select, run the second, run the thrid, then CLOSE (explicitly) the connect...

    Code:
     Public Function IBMDatatable(SelectCommand$, dbConn as iDB2Connection) As DataTable
    
            Dim dt As New DataTable
    
            Dim dtAdapter = New iDB2DataAdapter()
            dtAdapter.SelectCommand = New iDB2Command(SelectCommand, dbConn)
            dtAdapter.Fill(dt)
    
            Return dt
    
        End Function
    
    public sub GetData() 'Or what ever you want to call it.
            Using cn As New iDB2Connection("Data Source=" & SYSTEMID & ";User Id=" & UserID & ";Password=" & Password & ";")
                cn.Open()
    
                dt1 = IBMDataTable("select 1 here", cn)
                dt2 = IBMDataTable("select 2 here", cn)
                dt3 = IBMDataTable("select 3 here", cn)
    
                cn.Close
            End Using
    End Sub
    IT sounds like when you're allowing the connection to dispose, something isn't releasing the connection on the server end, so the pool fills up, and eventually you run out of resources. So, since you're doing multiple selects, ideally, you would connect once, run all your db operations, close the connection, then allow it to be disposed.

    I don't think the problem is the Using itself, but how it's being used. Personally, I prefer to explicitly open and close the connections and not rely on the object's default behavior of opening and closing (partially because I can't remember which ones do and don't).

    -tg
    Thanks for your reply... I have refactored my code to your format as it certainly looks alot more logical. For some strange reason though I'm still getting the same error and the connection not closing server side.

    I'm going to keep trying some different things and will update my post if i figure it out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width