Results 1 to 9 of 9

Thread: Opening a second connection to a MSSQL Database

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Jun 2011
    Location
    Switzerland
    Posts
    736

    Default Opening a second connection to a MSSQL Database

    Hi everyone

    I would like to open a second connection to the database.
    How can I do that?

    This is aproximately what we want to achieve:
    - Make a complicated query (which we don't want to put in SQL filters)
    - Fetch Row after row
    - Store data into another table

    Our Problem: We can't store the data, since we have a ResultSet currently open and we don't want to store it into a array, etc.
    It would be nice to open a second connection, but the ConnectionManager seems to prevent that. How do we manually open a second connection?

    Pseudocode:
    Code:
                           
                            Handle hoSQLMngr
                            Handle hdbc hstmt
                            Integer iFetchResult
                            
                            Object oSQLHandler is a cSQLHandleManager
                                Move Self to hoSQLMngr
                            End_Object                        
                            
                            Open SomeTable
                            Get SQLConnect of hoSQLMngr "" "" to hdbc
                            Get SQLOpen of hdbc to hstmt
                            
                            // 2.Handle für Maillist?
                            
                            Send SQLExecDirect of hstmt ("select * from SomeTable WHERE SomeReallyComplicatedConditions ")
    
                            Repeat
                                Get SQLFetch of hstmt to iFetchResult
                                If (iFetchResult <> 0) Begin 
                                    
                                    Send SQLBindFile of hstmt SomeTable.File_number
                                        Set File_Field_Changed_Value of oAnotherTable_DD File_Field AnotherTable.SomeID to SomeTable.SomeID
                                        Set File_Field_Changed_Value of oAnotherTable_DD File_Field AnotherTable.AField to SomeTable.AField
                                        :
                                        :
                                        Send Request_Save of oAnotherTable_DD      // Problem occurs here: We have an open ResultSet and cannot execute other queries on the same connection
                                    
                                End
                            Until (iFetchResult = 0)
                            
                            Send SQLClose of hstmt      // Close the statement handle
                            Send SQLDisconnect of hdbc  // Connectivity Kit's disconnect
    Currently working for transXpro (an online platform for a Swiss translation agency).
    Improved (unofficial) DataFlex Forum Search (now with SSL)

  2. #2
    Join Date
    Feb 2009
    Location
    SW Connecticut/NY area
    Posts
    8,470

    Default Re: Opening a second connection to a MSSQL Database

    Raphael,
    This sounds like something you might want to do entirely in the back end. In other words retrieve the data, maybe into a temp table, and then write it out to the new table all in SQL. It can be done in ESQL unless it's overly complicated, in which case you might want to use a stored procedure.
    Bob Worsley
    203-249-2633
    rlworsley at gmail.com

    Do or do not. There is no try. — Yoda

  3. #3
    Join Date
    Feb 2009
    Location
    South Florida
    Posts
    4,727

    Default Re: Opening a second connection to a MSSQL Database

    We built a DLL to do our own queries for this and also speed
    Michael Salzlechner
    StarZen Technologies, Inc
    http.://www.starzen.com

    Development Blog
    http://www.salzlechner.com/dev

    DataFlex Package Manager (aka Nuget for DataFlex)
    http://windowsdeveloper.com/dfPackage

  4. #4
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    9,802

    Default Re: Opening a second connection to a MSSQL Database

    Raphael,

    Have you looked into enabling Multiple Active Result Sets ?
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  5. #5
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    3,582

    Default Re: Opening a second connection to a MSSQL Database

    Hi Raphael

    What Bob said!

    you don't need to fetch the data into your app, if you are just going to insert it again in another table (unless of course if this another table resides in another DB/DBMS).

    You can insert the result-set directly into the target table
    Code:
    INSERT INTO <target-tabe> SELECT ... from ...
    this way you avoid unnecessary data transfer and will speed-up things a lot.

    --------

    but answering your question.

    if you really want to open a new connection, If you are using managed connections I believe you have to create another cConnection object instance.

    I remember there was another way, but I am not finding... I did that before.. need to dig a little bit
    Samuel Pizarro

  6. #6
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    9,802

    Default Re: Opening a second connection to a MSSQL Database

    Samuel, Raphael, Bob,

    I agree that for performance a stored procedure / function is the way to go.

    I disagree when talking about business rules. You are bypassing all the business rules coded in your DD classes. That might not be a big deal but you better tell the customer that not all data in the database was created under control of the application and that you (read app / company) cannot always be hold responsible for this.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  7. #7
    Join Date
    Jun 2011
    Location
    Switzerland
    Posts
    736

    Default Re: Opening a second connection to a MSSQL Database

    I'm aware of the overhead. Our motivation is to split the "logic" into two parts. Some conditions are easily implemented in SQL ( Status='A' ) for "active" records, for example. Others not so much... if we have a field with a string and want to insert a "default" into the other table without modifying the source table it gets kind of ugly (... ISNULL(NULLIF(...)) ).

    In other words: Do the selection in SQL and the transformation in DataFlex code.

    Wel'll probably just use DataDictionaries with SQL-filters, which should do the trick as well. The version with SqlBindFile felt convenient though and we did not want to change the DDs/Add more DDOs, etc. if not needed.

    For huge tables we already use the "Insert into <target> Select ... from <source>" approach and as filter and selections get more sophisticated, the code readability degrades fast. Imagine adding records from two tables into a third one, avoiding duplicates, etc, transforming empty values to defaults (depending on values of other fields).

    I'll give MARS a shot. At a first glance, this seems to solve our problem, but a quick try was not yet successful. I have a bit more reading to do
    And I'll also give the cConnection-object a try. So far I was trying to create a second connection manager, but that does not seem to work.
    Michael's DLL sounds good, but I assume "SQLBindFile" would not work with that?
    Performance is importing but even our "huge" databases are relatively small (usually <1 million entries ). Most queries are executed within seconds (if we don't fetch every record)
    Currently working for transXpro (an online platform for a Swiss translation agency).
    Improved (unofficial) DataFlex Forum Search (now with SSL)

  8. #8
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    3,582

    Default Re: Opening a second connection to a MSSQL Database

    About opening a 2nd diff connection to the database

    What I know for sure will work , is to duplicate your connectionID under managed connections with a different connectionId name

    then you grab the handle and Open the stmt..

    Code:
      Get SQLConnectionId of ghoConnection "ID2" to hoConnect
        Get SQLOpen of hoConnect to hoStmt
    Samuel Pizarro

  9. #9
    Join Date
    Jun 2011
    Location
    Switzerland
    Posts
    736

    Default Re: Opening a second connection to a MSSQL Database

    Thank you Samuel. A second connection ID works.
    Currently working for transXpro (an online platform for a Swiss translation agency).
    Improved (unofficial) DataFlex Forum Search (now with SSL)

Posting Permissions

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