DAO and Access97 WHERE clause fails

[v.davis2]

Hi all,

I am attempting to use Access97 as the database to hold the results of a
python script. I seem to be able to make simple SELECT clauses work (like
SELECT * FROM TableName), but have not been able to figure out how to add a
WHERE clause to that (e.g., SELECT * FROM TableName WHERE myFieldName = 34)
This fails complaining that the wrong number of parameters are present.
I haved tried DAO36 and I have tried the ADO version with the same results.
Therefore I have to conclude it is my screwup!
Help in the forum or via email would sure be appreciated! ([email protected])

Here is the skeleton code:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.35')
sDBname = 'vpyAnalyzeDirectorySize.mdb'
sDB = 'c:\\documents and settings\\vic\\my
documents\\tools\\python25\\_myscripts\\'+sDBname
daoDB = daoEngine.OpenDatabase(sDB)

sSQL1 = 'SELECT * FROM T_Index2DirName'
daoRS = daoDB.OpenRecordset(sSQL1) # this works FINE and I can play
with the record set

#<snip>

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are
all correct in mdb file
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though the
record is there

daoRS.Close()


Traceback (most recent call last):
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py",
line 310, in RunScript
exec codeObject in __main__.__dict__
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\_MyScripts\TestForPosting01.py", line 14, in
<module>
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though
record is there
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\lib\site-packages\win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0.py",
line 523, in OpenRecordset
, Type, Options, LockEdit)
com_error: (-2147352567, 'Exception occurred.', (0, 'DAO.Database', 'Too few
parameters. Expected 1.', 'jeterr35.hlp', 5003061, -2146825227), None)

 

[John Machin]

Hi all,

I am attempting to use Access97 as the database to hold the results of a
python script. I seem to be able to make simple SELECT clauses work (like
SELECT * FROM TableName), but have not been able to figure out how to add a
WHERE clause to that (e.g., SELECT * FROM TableName WHERE myFieldName = 34)
This fails complaining that the wrong number of parameters are present.
I haved tried DAO36 and I have tried the ADO version with the same results.
Therefore I have to conclude it is my screwup!
Help in the forum or via email would sure be appreciated! ([email protected])

Here is the skeleton code:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.35')
sDBname = 'vpyAnalyzeDirectorySize.mdb'
sDB = 'c:\\documents and settings\\vic\\my
documents\\tools\\python25\\_myscripts\\'+sDBname
daoDB = daoEngine.OpenDatabase(sDB)

sSQL1 = 'SELECT * FROM T_Index2DirName'
daoRS = daoDB.OpenRecordset(sSQL1) # this works FINE and I can play
with the record set

#<snip>

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are
all correct in mdb file

Disclaimer: I've never used DAO.

That SQL statement appears to be incorrect/meaningless.
Don't you need something like
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = %d' % hsDB
?

You may need to read this (changing Office10 to whatever you have):
C:\Program Files\Common Files\Microsoft Shared
\Office10\1033\DAO360.CHM

 

[Dennis Lee Bieber]

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are

Note that, as written, hsDB is considered a reference to a table
column in the database table -- your hashed variable is NOT referenced.
You have coded the equivalent of:

select <all columns> from <a table> where <a field> = <another
field>


You need to find out how to specify/pass parameters to the JET
engine.

MSDN (VS6 version) gives these examples

-=-=-=-=-=-
The following example shows filtering with a pre-specified WHERE clause
(the second approach above):

// Filter records with the SQL keyword WHERE
CString strSQL = rsEnrollmentSet.GetDefaultSQL( ) +
"WHERE [Student ID] = " + strStudentID;
try
{
// Open the recordset using the filtered SQL string
rsEnrollmentSet.Open( dbOpenDynaset, strSQL );
// ...
}
// ...

The example calls GetDefaultSQL to obtain the SQL string defined for the
recordset's class at design time, using ClassWizard or AppWizard. Then
it concatenates a WHERE clause, part of which is based on run-time
information in strStudentID.
-=-=-=-=-=-
Parameterizing DAO Queries
In situations where your application executes the same query repeatedly,
it is more efficient to create a stored querydef object that contains
the SQL statement. Queries stored in the database execute faster and can
be used by anyone with access to the database.

If your application needs to alter WHERE clause arguments in a query,
you can also add a PARAMETERS clause to your query that permits the
Microsoft Jet database engine to substitute values into the query at run
time. Before running parameter queries, your application must substitute
values for each of the parameters as stored in the Parameters collection
of the querydef.

In general, parameterizing queries improves performance. The
parameterized SQL statement doesn't have to be recompiled each time you
run the query.

To create a parameter query

Create a PARAMETERS clause string that includes a parameter name and
data type for each parameter. Don't use the field name alone as the
parameter name, because duplicating it may cause problems. You can
include the field name within the parameter name, however. The example
calls the parameter "Student Ident" rather than "Student ID", the name
of the field.
If you are working with a database accessed by Microsoft Access, the
parameter name is used as a prompt string. Keep this in mind if you
expect Microsoft Access users to use this query.

Shown below is a typical PARAMETERS clause:

CString strParam = "PARAMETERS [Student Ident] TEXT; ";

The parameter name is enclosed in square brackets here because the name
contains a space. Otherwise the brackets are unnecessary.

Create a SELECT statement that retrieves the needed fields and
incorporates the named parameters into the WHERE clause. In the example
below, the parameters are used to filter the query to return only
selected students. Note that the parameter [Student Ident] is
substituted by the database engine during execution of the query at run
time.
strSQL = strParam + "SELECT * FROM Enrollment WHERE Enrollment.[Student
ID] = [Student Ident]";

Create a named querydef ("Find Enrollments") with your SQL statement.
CDaoQueryDef qd( m_dbStudentReg );
qd.Create( "Find Enrollments", strSQL );
qd.Append( );

Set the querydef parameters.
First, you need to gain access to the querydef. You can either use the
querydef object just created, or reference the stored querydef object
from the QueryDefs collection. The example shows using the querydef just
created.

COleVariant varParamValue( strStudentID );
qd.SetParamValue( "[Student ID]", varParamValue );

Execute the procedure.
Because this query returns records, you need to create a recordset to
capture the result set.

CEnrollmentSet rsEnrollmentSet( &m_dbStudentReg );
rsEnrollmentSet.Open( &qd, dbOpenDynaset );

The parameter is defined as part of the SQL statement and becomes part
of a PARAMETERS clause. You set the value of the parameter, at run time,
by calling the querydef object's SetParamValue member function. This
function takes:

A parameter name, which must match the name you specified in the SQL
string ("Student Ident" in the example).


A COleVariant object that contains the value. COleVariant makes it easy
to use the VARIANT data type from MFC for a variety of different actual
types. In the example, the actual type is a string.
For more information and a different example (presented in the Basic
language rather than C++), see the topic "Creating Parameter Queries
with DAO" in DAO Help.
-=-=-=-=-=-

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[v.davis2]

Hello all.
Thanks for the help! John pointed out to me the flaw in my code:
Change:
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB'
to:
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = %ld' % hsDB
That did the trick. I had looked at the statement so often that it was
*obviously* correct.
John also pointed me to the DAO help file that I had not been able to find.
Dennis also pointed out the correction, but went on to educate me much more
on what I was trying to do.
BTW, I had searched extensivly on line for the answer before posting, but
was missing the obvious, stupid coding error.
Thanks to all for getting me back on track!
--Vic

 

[Dennis Lee Bieber]

Dennis also pointed out the correction, but went on to educate me much more
on what I was trying to do.

I should point out that I don't do DAO (or ADO) -- and if I had to
code Python to access JET, I'd probably hijack a copy of mxODBC in order
to get a "sane" SQL interface. A nice clean "execute SQL with generic
place-holders, supplying a tuple of parameters"; none of this
"predeclare identifiers for parameter place-holders, populate some
attribute with place-holder/value pairs, and then execute the SQL"
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[stefaan]

I should point out that I don't do DAO (or ADO) -- and if I had to

code Python to access JET, I'd probably hijack a copy of mxODBC in order
to get a "sane" SQL interface.

I have successfully used the dejavu object-relational mapper (http://
projects.amor.org/docs/dejavu/1.5.0RC1/) to access MS ACCESS databases
recently.

Bestregards,
Stefaan.