Qt-interest Archive, March 2008
Inserting records in a MS Access 2003 database
Message 1 in thread
Hi,
First some system specs:
WIndows XP SP2, Qt 4.4.0beta1
I am having trouble inserting records in a MS Access 2003 database through
ODBC. I have tried two methods myself: building up a query string myself,
and using value binding. Neither method seems to work.
I use the following connection string:
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/Documents and
Settings/andre/Mijn documenten/db1.mdb;Uid=Admin
This connection works for queries that update the table structure (I can
create the tables I want to create). However, if I want to insert records, I
run into problems. Using the binding method, I get this error:
> QODBCResult::exec: Unable to execute statement: [Microsoft][ODBC
> Microsoft Access-stuurprogramma]Het
> veld COUNT is onjuist
>
> " [Microsoft][ODBC Microsoft Access-stuurprogramma]Het veld COUNT is
> onjuist QODBC3: Unable to
> execute statement"
if I try to execute the following query:
> INSERT INTO Patents([patent search query], [searched patent number],
> [patent number], [title], [log], [ID])
> VALUES (:patent_search_query, :searched_patent_number, :patent_number,
> :title, :log, :ID)
Of course, all the values are bound as they should be.
When using the "direct" approach, I run into another error. Using this SQL
statement:
> INSERT INTO Patents([patent search query], [searched patent number],
> [patent number], [title], [log], [ID])
> VALUES ("Applicant=awl techniek", "NL9301537", "NL9301537", "Method and
> device for separating and
> positioning randomly oriented cuttings", "Data complete or no extra data
> available.", 2)"
I receive this error:
> QODBCResult::exec: Unable to execute statement: [Microsoft][ODBC
> Microsoft Access-stuurprogramma] is
> geen geldige naam. Controleer of er ongeldige tekens of leestekens in de
> naam voorkomen en of de naam
> niet te lang is.
>
>" [Microsoft][ODBC Microsoft Access-stuurprogramma] is geen geldige naam.
>Controleer of er ongeldige
> tekens of leestekens in de naam voorkomen en of de naam niet te lang is.
> QODBC3: Unable to execute
> statement"
Neither of the above errors makes sense to me. In the first case, I do not
have a clue where the reference to the field "COUNT" comes from, but it sure
is not in my table nor in my query. In the second - direct - case, the
driver seems to bomb out somehow completely. Why would the driver name all
of a sudden be invalid when it worked on the last query that created the
table I'm working on in the first place.
I searched the archives of this list, and I found this post:
http://lists.trolltech.com/qt-interest/2007-07/msg00864.html
Unfortunately, there was no answer.
I repeated this posters experiment, and found the same thing:
QSqlDriver::sqlStatement returns an empty string in my case (ODBC driver,
connected to a MS Access 2003 database) for both the insert and the update
statements.
Can somebody please provide me some pointers what might be the problem here?
Where am I going wrong? I can hardly imagine that this is problem in Qt, as
I would expect such a basic issue to surface much earlier.
Thanks,
André
--
[ signature omitted ]
Message 2 in thread
Hi,
"André Somers" <andre@xxxxxxxxxxxxxxxx> wrote in message
news:fsapbr$etl$1@xxxxxxxxxxxxxxxx
> Hi,
>
> First some system specs:
> WIndows XP SP2, Qt 4.4.0beta1
>
> I am having trouble inserting records in a MS Access 2003 database through
> ODBC. I have tried two methods myself: building up a query string myself,
> and using value binding. Neither method seems to work.
>
> I use the following connection string:
> Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/Documents and
> Settings/andre/Mijn documenten/db1.mdb;Uid=Admin
>
> This connection works for queries that update the table structure (I can
> create the tables I want to create). However, if I want to insert records,
> I run into problems. Using the binding method, I get this error:
>
>> QODBCResult::exec: Unable to execute statement: [Microsoft][ODBC
>> Microsoft Access-stuurprogramma]Het
>> veld COUNT is onjuist
>>
>> " [Microsoft][ODBC Microsoft Access-stuurprogramma]Het veld COUNT is
>> onjuist QODBC3: Unable to
>> execute statement"
>
> if I try to execute the following query:
>> INSERT INTO Patents([patent search query], [searched patent number],
>> [patent number], [title], [log], [ID])
>> VALUES (:patent_search_query, :searched_patent_number, :patent_number,
>> :title, :log, :ID)
> Of course, all the values are bound as they should be.
>
> (...)
>
> Neither of the above errors makes sense to me. In the first case, I do not
> have a clue where the reference to the field "COUNT" comes from, but it
> sure is not in my table nor in my query. In the second - direct - case,
> the driver seems to bomb out somehow completely. Why would the driver name
> all of a sudden be invalid when it worked on the last query that created
> the table I'm working on in the first place.
OK, as often seems to be the case, an answer is found shortly after
formulating the question.
After trying with a different database driver than Access (SQLITE), I got a
more understandable error for the value-binding method. It turns out that
you can not start binding values before you set the actual SQL statement. If
I move the binding of the values to a loop of it's own (after the one
construcing the SQL), I get a working solution.
That still does not explain why the direct approach does not work, and why
the empty strings are returned for QSqlDriver::sqlStatement, but at least I
can continue my project.
Thank,
André
--
[ signature omitted ]
Message 3 in thread
Hi,
It seems part of the problem persists after all:
> After trying with a different database driver than Access (SQLITE), I got
> a more understandable error for the value-binding method. It turns out
> that you can not start binding values before you set the actual SQL
> statement. If I move the binding of the values to a loop of it's own
> (after the one construcing the SQL), I get a working solution.
While inserting data works now, I get a problem that fields of type TEXT
(=MEMO), which according to Access documentation can accept more than 2GB
worth of text, get trucated to 255 characters. This means that some
important data is missing in the database.
Any hints on how to circumvent this issue?
André
--
[ signature omitted ]
Message 4 in thread
André Somers schrieb:
> Hi,
>
> It seems part of the problem persists after all:
>
>> After trying with a different database driver than Access (SQLITE), I
>> got a more understandable error for the value-binding method. It turns
>> out that you can not start binding values before you set the actual
>> SQL statement. If I move the binding of the values to a loop of it's
>> own (after the one construcing the SQL), I get a working solution.
> While inserting data works now, I get a problem that fields of type TEXT
> (=MEMO), which according to Access documentation can accept more than
> 2GB worth of text, get trucated to 255 characters. This means that some
> important data is missing in the database.
>
> Any hints on how to circumvent this issue?
>
According to
http://office.microsoft.com/en-us/access/HP052745731033.aspx
(and my feeble memory) TEXT != MEMO:
Use a Text data type to store data such as names, addresses, and any
numbers that do not require calculations, such as phone numbers, part
numbers, or postal codes. A Text field can store up to 255 characters,
but the default field size is 50 characters. The FieldSize property
controls the maximum number of characters that can be entered in a
Text field.
Use the Memo data type if you need to store more than 255 characters.
A Memo field can store up to 65,536 characters. If you want to store
formatted text or long documents, you should create an OLE Object
field instead of a Memo field.
Your 2GB size limit for MEMO fields my be optimistic too.
--
[ signature omitted ]
Message 5 in thread
Hi,
Thanks for your suggestions. See my comments mixed in with your reply below.
"ekkehard.horner" <ekkehard.horner@xxxxxxxx> schreef in bericht
news:fse3g9$n7n$1@xxxxxxxxxxxxxxxx
>> While inserting data works now, I get a problem that fields of type TEXT
>> (=MEMO), which according to Access documentation can accept more than 2GB
>> worth of text, get trucated to 255 characters. This means that some
>> important data is missing in the database.
>>
>> Any hints on how to circumvent this issue?
>>
> According to
>
> http://office.microsoft.com/en-us/access/HP052745731033.aspx
>
> (and my feeble memory) TEXT != MEMO:
It seems that knowledge base and the in-program help are in disagreement
then. :-(
I quote the (Dutch) help from Microsoft Access help pages on SQL types [1]
note 3:
> Tekens in velden van het type TEXT (oftewel MEMO) of CHAR (oftewel TEXT(n)
> met een specifieke lengte) worden opgeslagen (...)
Translated:
> Characters in fields of type TEXT (that is MEMO) or CHAR (that is, TEXT(n)
> with a specific length) are stored (...)
Also, the list of types above the notes does list TEXT, but not MEMO.
> Use a Text data type to store data such as names, addresses, and any
> numbers that do not require calculations, such as phone numbers, part
> numbers, or postal codes. A Text field can store up to 255 characters,
> but the default field size is 50 characters. The FieldSize property
> controls the maximum number of characters that can be entered in a
> Text field.
>
> Use the Memo data type if you need to store more than 255 characters.
> A Memo field can store up to 65,536 characters. If you want to store
> formatted text or long documents, you should create an OLE Object
> field instead of a Memo field.
I see... The page you reference does mention this, and that it applies to
Access 2003. Weird, because the help file says something different. :S
> Your 2GB size limit for MEMO fields my be optimistic too.
Again: the same page in the Access help tells me TEXT can store up to 2.14
gigabytes. Without the COMPRESSION (=using UTF-8 instead of UTF-16, I guess)
option, that would mean a bit over 1 billion characters (in theory): plenty
for my purposes. A few thousand is enough.
However, you are right that the online help tells differently. I will try
tomorrow at the office again, this time using the MEMO type to create the
tables in SQL.
Thanks for your suggestions.
André
[1] Microsoft JET SQL Reference -> Overzicht (top item, "overview") -> SQL
gegevenstypen ("SQL data types")
--
[ signature omitted ]
Message 6 in thread
Hi,
"ekkehard.horner" <ekkehard.horner@xxxxxxxx> wrote in message
news:fse3g9$n7n$1@xxxxxxxxxxxxxxxx
>> While inserting data works now, I get a problem that fields of type TEXT
>> (=MEMO), which according to Access documentation can accept more than 2GB
>> worth of text, get trucated to 255 characters. This means that some
>> important data is missing in the database.
>>
>> Any hints on how to circumvent this issue?
>>
> According to
>
> http://office.microsoft.com/en-us/access/HP052745731033.aspx
>
> (and my feeble memory) TEXT != MEMO:
>
> Use a Text data type to store data such as names, addresses, and any
> numbers that do not require calculations, such as phone numbers, part
> numbers, or postal codes. A Text field can store up to 255 characters,
> but the default field size is 50 characters. The FieldSize property
> controls the maximum number of characters that can be entered in a
> Text field.
>
> Use the Memo data type if you need to store more than 255 characters.
> A Memo field can store up to 65,536 characters. If you want to store
> formatted text or long documents, you should create an OLE Object
> field instead of a Memo field.
>
>
> Your 2GB size limit for MEMO fields my be optimistic too.
OK, so using MEMO as the field type in the SQL that creates the tables
works: no more 255 character limit. Thanks for your suggestions.
André
--
[ signature omitted ]
