WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ODBC
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

ODBC Extender and Accessing MS Access with qExecDirect Returning -1 Error

Keywords: 

Question:

I'm trying to use the sample program !sqltest.wbt that came with the ODBC Extender to access a MS Access created .mdb data file.

The sample code works up to the point of the qExecDirect call. No matter how I've modified the code it always returns a -1.

Has anyone used the ODBC Extender to access a MS Access database?

Answer:

qExecDirect is failing with the value -1, probably means it's a misformed SQL statement.

The format of "statement" will depend on the data source and driver.

SQL Return Codes
Constant Meaning Value
@qError SQL_ERROR -1
You may have to look at MS Access docs, and check your SQL syntax.

NB: Many users can share a database, but the methodology is actually that of sharing files. To enable users to share a database, all the applications that use the database must have access to the database files. Also, none of the applications can open the database in exclusive mode. When you open a database in exclusive mode, you prevent other applications from using the database.

To get further details on why the qExecDirect function might not be working, add:

retcode=qError(hstmt,2)
plugging the statement handle into qError... this should return some error message info back from the driver, giving us some insight as to why qExecDirect is failing.

Question #2:

I am having problems with INSERT and UPDATE using an ODBC MS Access Driver. SELECT statement works fine, but I am unable to INSERT INTO or UPDATE any tables in my Database. I Cut and Pasted my SQL Statements from Access. I can, however, use a PERL script with the same Database and ODBC Driver and INSERT and UPDATE.

Answer #2:

Most often the reason the qExecDirect function fails, is because of a misformed SQL Statement.....
You may try using our function qError, to get the SQL error from your Access Driver.
Here is how you might structure your code for debugging purposes...
;******************************************
;Insert
Debug(1)
retcode = qExecDirect(hstmt, "INSERT INTO %mytable% (%mycolumn%) VALUES ('string')") 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qExecDirect failed", retcode)
  err=qerror(hstmt,2)
  message("",err)
  Exit
Endif
;*****************************************

Question - How to "INSERT INTO" Using WIL Variables :

I'd like to know how to bind WIL parameters to SQL parameters (or column names) so the insert/ update can be run iteratively.

My understanding is that the ODBC extender creates a result set table when qExecDirect() (or one of the related ODBC meta functions) is called.

The problem I had was if you have a set of values stored in WIL variables and you want to insert these into a table using SQL Insert then there has to be a mechanism to bind the variables to columns in a result set before making the qExecDirect call. If you don't have access to a result set before qExecDirect then how do you do the binding?

Answer:

Use the qBindCol function.

Run the qBindCol on the variables and columns in question. Then set the variables how you like them and to do the Table Insert operation.

The qBindCol functions are referenced and are used to set the columns from the WIL variables.


Article ID:   W12541
Filename:   qExecDirect and SQL Questions.txt
File Created: 2017:07:28:12:58:33
Last Updated: 2001:09:06:10:10:30