sqltools simultaneous data sets

[Shawn Anderson]

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?

[Bud Durland]

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.

[Carlo Pagani]

Hi Shawn

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

[Eric Pearson]

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.

[Shawn Anderson]

exactly what I needed to know, Thanks!

[Shawn Anderson]

I'm still having an issue with this:

here is my 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 |

[Carlo Pagani]

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)

[Eric Pearson]

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.

[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.

[Shawn Anderson]

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

[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?

[Carlo Pagani]

http://www.connectionstrings.com/?carrier=access

[Shawn Anderson]

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

[Shawn Anderson]

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:

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

[Bud Durland]

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)

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:

  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

[Eric Pearson]

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

[Michael Mattias]

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

[Bud Durland]

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: