Announcement

Collapse
No announcement yet.

sqltools simultaneous data sets

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sqltools simultaneous data sets

    I want to do a SELECT search then loop through the result set.
    Inside of that loop, I want to do another SELECT search.
    When I try that, it messes up the original result set.

    How can that be done?

  • #2
    If you are using the shorthand versions of the SQLTools functions, that's to be expected, as they all use the same statement number.

    You need to use multiple statement numbers, so you'll need to use the "long" version of the SQLTools functions instead. SQL_FETCHRESULT() vs SQL_FETCH, SQL_RESULTCOLUMNSINT() vs SQL_RESCOLSINT(), etc.
    Real programmers use a magnetized needle and a steady hand

    Comment


    • #3
      Hi Shawn

      You will have to make sure you have the correct parameters when initialising too:
      SQL_Initialize(NoOfDb, MaxStatements, ......)

      Comment


      • #4
        Shawn --

        Bud and Carlo covered all the bases (thanks guys!)... For lots more information about using multiple databases and/or statements, see "Two Of Everything" in the Help File.

        -- Eric Pearson, Perfect Sync, Inc.
        "Not my circus, not my monkeys."

        Comment


        • #5
          exactly what I needed to know, Thanks!

          Comment


          • #6
            I'm still having an issue with this:

            here is my code:
            Code:
            FUNCTION openDatabase() AS STRING          
            
              ' open and ititialize sql tools
                SQL_Authorize xxxxxxxx          ' my auth code here
                SQL_Initialize 2,2,50,3,3,0,0,0      
                
              ' open the dsn file and check for errors      
                IF NOT exists($datapath+"cgi\scouting.dsn") THEN
                    FUNCTION="can't find dsn file"
                    EXIT FUNCTION
                END IF      
                
                IF NOT exists($datapath+"cgi\supplyAgreement.dsn") THEN
                    FUNCTION="can't find dsn file"
                    EXIT FUNCTION
                END IF                                  
                
                sql_openDatabase(1,$datapath+"cgi\scouting.dsn",%PROMPT_TYPE_NOPROMPT) 
                sql_openDatabase(2,$datapath+"cgi\supplyAgreement.dsn",%PROMPT_TYPE_NOPROMPT)
                'SQL_OpenDB($datapath+"cgi\scouting.dsn") << this works for one db
                
                FUNCTION = SQL_ErrorQuickAll  
            
            END FUNCTION
            I get this error:
            [56496.861] ODBC 1 -1 -1 -1 IM002 0 -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified | [56496.861] ODBC 1 -1 -1 -1 01S00 0 -- [Microsoft][ODBC Driver Manager] Invalid connection string attribute | [56496.861] SQL_CloseDatabase 1 -1 -1 999000031 #0031 0 -- [Perfect Sync][SQL Tools] DB Not Open |

            Comment


            • #7
              The problem is in the DSN files. Try make a connection string as follows. This example is for SQL Server:

              local Connection as string

              Connection="DRIVER=SQL Server;"+_
              "SERVER=YOUR_SERVER;"+_
              "DATABASE=YOUR_DB;UID=Your_UserName;PWD=Your_Password;"

              lResult = SQL_OpenDatabase(1, Connection, %PROMPT_TYPE_NOPROMPT)

              Comment


              • #8
                I agree with Carlo's diagnosis. More specifically, the problem is inside the first DSN file. Note that the database numbers in the error messages are both 1.
                Code:
                [56496.861] ODBC 1 -1 -1 -1 IM002 0 -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 
                [56496.861] ODBC 1 -1 -1 -1 01S00 0 -- [Microsoft][ODBC Driver Manager] Invalid connection string attribute
                It appears that the second DSN is working ok, because there are no error messages for database 2.

                So focus on trying to connect to the first database. Your DSN file needs some tweaking.

                -- Eric Pearson, Perfect Sync, Inc.
                "Not my circus, not my monkeys."

                Comment


                • #9
                  sorry, I should of been more clear.
                  I am getting an error for both dsn files:

                  Code:
                  [58600.001] ODBC 1 -1 -1 -1 IM002 0 -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified | [58600.001] ODBC 1 -1 -1 -1 01S00 0 -- [Microsoft][ODBC Driver Manager] Invalid connection string attribute | [58600.001] SQL_CloseDatabase 1 -1 -1 999000031 #0031 0 -- [Perfect Sync][SQL Tools] DB Not Open | [58600.001] ODBC 2 -1 -1 -1 IM002 0 -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified | [58600.001] ODBC 2 -1 -1 -1 01S00 0 -- [Microsoft][ODBC Driver Manager] Invalid connection string attribute | [58600.001] SQL_CloseDatabase 2 -1 -1 999000031 #0031 0 -- [Perfect Sync][SQL Tools] DB Not Open
                  I am using a Microsoft Access database.
                  It does work with SQL_OpenDB but I will check my DSN files.
                  Is it possible to use a connection string for an Access Database?

                  Comment


                  • #10
                    http://www.connectionstrings.com/?carrier=access

                    Comment


                    • #11
                      Thanks Carlo and Eric,
                      The access connection string worked.
                      That seems much cleaner than a dsn file anyway.
                      Now that I finally have the database open, maybe I can go on

                      Comment


                      • #12
                        When I use Sql_statement I need to specify which database to pull from.
                        What if I want to grab data from multiple databases at the same time?

                        In other words, how do I use sql_statement if my statment looks like this:
                        Code:
                        select db1.table1.param1,db2.table2.param2 from db1.table1, db2.table2where db1.table1.param1="value"
                        I assume my sql statement will need a join or something but my specific question is about the sqlTools syntax.

                        Thanks

                        Comment


                        • #13
                          Originally posted by Shawn Anderson View Post
                          I assume my sql statement will need a join or something but my specific question is about the sqlTools syntax.

                          Thanks
                          There's no real specific SQLTools syntax, Per Se; just build a coherent SQL command/query and send it along: ( example has no error checking for brevity)

                          Code:
                          FUNCTION ListVacation() as LONG
                          
                            LOCAL SQLQry  AS STRING  
                            LOCAL EmpName  AS STRING
                            LOCAL VacaDay   AS STRING
                          
                            SQL_OPENDATABASE 1, "DSN=ODBCDef;"
                          
                            SQLQry = "SELECT Vacation.TheDate, Empl.TheName " + _
                                   " FROM Vacation, Empl " + _
                                   " WHERE Empl.EmployeeID = Vacation.EmployeeID " + _
                                   " AND Vacation.TheDate > '2008-01-01'"
                          
                            SQL_Statement 1, 1, %SQL_STMT_IMMEDIATE, SQLQry
                          
                            do
                              SQL_fetchresult 1, 1, %NEXT_ROW
                              if SQL_Endofdata(1,1) then exit loop
                              VacaDay = SQL_resultcolumndate(1, 1, 1, "dd-MMM-yyyy")
                              EmpName = SQL_resultcolumnstr(1, 1, 2)
                          
                              PRINT EmpName + " is taking " + VacaDay
                            loop
                          
                            SQL_Closedatabase 1
                          
                          END FUNCTION
                          The SQLQry could also be this, which in some cases is more appropriate:

                          Code:
                            SQLQry = "SELECT Vacation.TheDate, Empl.TheName " + _
                                " FROM Vacation " + _
                                "  LEFT OUTER JOIN Empl ON (Empl.EmployeeID = Vacation.EmployeeID) " + _
                                " WHERE Vacation.TheDate > '2008-01-01'"
                          Hope this helps
                          Real programmers use a magnetized needle and a steady hand

                          Comment


                          • #14
                            Shawn --

                            Bud is correct, SQL Tools doesn't care about the syntax. It passes your SQL statement verbatim to the database driver and return the results to you.

                            Your idea of using two different datasources in the same SQL statement will work only if your ODBC driver supports it. You'll need to research the driver-specific syntax.

                            -- Eric
                            "Not my circus, not my monkeys."

                            Comment


                            • #15
                              He's not asking about joining two or more tables from one database, he's asking about joining two or more tables from DIFFERENT databases. (In ODBC terms, two or more databases on different connection handles).

                              I know Oracle has syntax to do this in one SQL statement but I've never tried to do it with ODBC. Closest I've come is executing a query against one database, and during the fetch loop execute another query against second database using info from the first database's fetched row as a parameter.

                              MCM
                              Michael Mattias
                              Tal Systems (retired)
                              Port Washington WI USA
                              [email protected]
                              http://www.talsystems.com

                              Comment


                              • #16
                                Originally posted by Michael Mattias View Post
                                He's not asking about joining two or more tables from one database, he's asking about joining two or more tables from DIFFERENT databases. (In ODBC terms, two or more databases on different connection handles).
                                Oops. That's what I get for staying up late on the Internet. :tapedshut:
                                Real programmers use a magnetized needle and a steady hand

                                Comment

                                Working...
                                X