You are not logged in. You can browse in the PowerBASIC Community, but you must click Login (top right) before you can post. If this is your first visit, check out the FAQ or Sign Up.
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.
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
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.
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 |
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.
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?
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
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
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.
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.
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
We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, and to analyze site activity. For additional details, refer to our Privacy Policy.
By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also acknowledge that this forum may be hosted outside your country and you consent to the collection, storage, and processing of your data in the country where this forum is hosted.
Comment