Announcement

Collapse
No announcement yet.

sqltools simultaneous data sets

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

  • Bud Durland
    replied
    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:

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • Eric Pearson
    replied
    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

    Leave a comment:


  • Bud Durland
    replied
    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

    Leave a comment:


  • Shawn Anderson
    replied
    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

    Leave a comment:


  • Shawn Anderson
    replied
    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

    Leave a comment:


  • Carlo Pagani
    replied
    http://www.connectionstrings.com/?carrier=access

    Leave a comment:


  • Shawn Anderson
    replied
    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?

    Leave a comment:


  • Eric Pearson
    replied
    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 [COLOR="red"]1[/COLOR] -1 -1 -1 IM002 0 -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 
    [56496.861] ODBC [COLOR="Red"]1[/COLOR] -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.

    Leave a comment:


  • Carlo Pagani
    replied
    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)

    Leave a comment:


  • Shawn Anderson
    replied
    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 |

    Leave a comment:


  • Shawn Anderson
    replied
    exactly what I needed to know, Thanks!

    Leave a comment:


  • Eric Pearson
    replied
    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.

    Leave a comment:


  • Carlo Pagani
    replied
    Hi Shawn

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

    Leave a comment:


  • Bud Durland
    replied
    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.

    Leave a comment:


  • Shawn Anderson
    started a topic sqltools simultaneous data sets

    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?
Working...
X