edwig
ODBC Error [42000] running certain MS Access Update or Insert Statements
Hello Edwig,
the following Update and Insert Statements return the following ODBC errors:
update PropVals set value='StartParm' where objectid = ? and propertyid = 88;
update PropVals set [value]='D:\zrgu' where objectid = ? and propertyid = 59;
insert into PropVals (value, objectid, propertyid) values ('D:\zrgu', ?, 59);
0 update PropVals set value = 'StartParm' where objectid =? and propertyid = 88..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3503][Microsoft][ODBC Microsoft Access Driver] Syntaxfehler in UPDATE-Anweisung.
0 update PropVals set [ value ]= 'D:\zrgu' where objectid =? and propertyid = 59..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-1002][Microsoft][ODBC Microsoft Access Driver] Unzulässiges Einklammern des Namens '[ value ]'.
0 insert into PropVals ( value , objectid , propertyid ) values ( 'D:\zrgu' ,?, 59)..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3502][Microsoft][ODBC Microsoft Access Driver] Syntaxfehler in der INSERT INTO-Anweisung.
I don't see any syntax errors in these statements. Is "value" a reserved word? But the affected table has a column named "value" which cannot be renamed. Is there a way to escape such a column name?
Best regards, Gerhard
I did some more testing. In fact "VALUE" is a reserved word. And I think I found the reason for the syntax error message:
Update PropVals set [ value ]='CCCC' where objectid=2487;
[Microsoft][ODBC Microsoft Access Driver] Unzulässiges Einklammern des Namens '[ value ]'
The problem is that ODBC Query adds spaces between the square brackets and the identities (table or column names). That is not accepted by MS Access.
Escapeing a reserved word just with square brackets works:
Update PropVals set [value]='CCCC' where objectid=2487;
OK.
That needs to be fixed.
The standard way (The SQL Standard ISO 9075) to escape an identifier that is also a reserved word is to enclose it in double quotes. The escape in angular brackets is a Microsoft specific extension of SQL-Server, so never the best bet. If you want to try it out, this is what I did to test your error message in an MS-Access ODBC driver:
CREATE TABLE test ( one INT, two CHAR(100), [Value] INT );
INSERT INTO test VALUES (1,'testin',2); SELECT * FROM test; SELECT Value FROM test; -- Works fine, resonds with the inserted values
-- Now test with the insert :variable1 input LONG; :variable1 = 88;
INSERT INTO test(one,two,[Value]) VALUES (2,'Other test',?); -- BOOM ! Error INSERT INTO test(one,two,"Value") VALUES (2,'Other test',?); -- Works fine !!
SELECT * FROM test; -- Responds with two lines (1 and 2) and 88 for the value!
My conclusiion: It works just fine. Nothing needs to be fixed. Please test again with double quotes as the identifier escape.
I ran tests with your script:
Status Affected rows Result
0 CREATE TABLE test
(
one INT,
two CHAR(100),
[Value] CHAR(128)
)..... : OK
1 INSERT INTO test VALUES (1,'testin',2)..... : OK
1 SELECT * FROM test ..... : OK
1 SELECT Value FROM test ..... : OK
1 :variable LONG : OK
1 :variable = 88 : OK
1 INSERT INTO test(one,two,[Value]) VALUES (2,'Other test',?)..... : OK
1 INSERT INTO test ( one , two , "Value" ) VALUES (2, 'Other test' ,?)..... : OK
3 SELECT * FROM test..... : OK
Surprisingly the script worked. Even the first INSERT statement did not fail. Wonder why. In my script using square brackets does not work at all.
However, trying to create column "Value" as LONGCHAR does not work. In my project this column is of type LONGCHAR:
Status Affected rows Result
0 [Value] LONGCHAR
)..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3500][Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.
I think LONGCHAR is a valid MS Access data type.
Anyway, what speaks against to not add spaces using square brackets escapeing identities?
This is very strange as the following test runs quite well:
CREATE TABLE test ( one INT, two CHAR(100), [Value] LONGCHAR )
INSERT INTO test (one,two,[Value]) VALUES (1,'ABC','This is a longer text line that should be stored');
SELECT * FROM test;
UPDATE test SET [Value] = 'One other line of text' WHERE one = 1;
SELECT * FROM test;
Which shows in my opinion that creating, inserting, selecting and updating of a LONGCHAR column does work correctly in this program. The discovery pane shows a LONGCHAR column of size 1073741823 character bytes long. I never ever get the ODBC 42000 status for one of these statements. And alsoo the mixed case of the [Value] field does not matter.
I tested this again. And it works.
Probably I didn't execute the script from the beginning. I.e. the cursor was somewhere in the middle of the CREATE TABLE statement while executing the script. Sorry.
However, there is a small syntax error in the above test script. A ';' is missing at the end of the CREATE TABLE statement.
But there is still an open issue if the ODBC Query Tool should not generate spaces for escaped identifiers in square brackets?
Yes. The ';' at the end of the "CREATE TABLE" statement was missing. I executed this statement as an isolated statement, before I continued with the rest of the script. Sorry for the unclearity.
No: The querytool should definitly not generate any extra's like quotes for names or spaces in between. It is explicitly the design that this is up to the user to write someting like "CREATE * FROM tablename;" or SELECT * FROM "Tablename"; or "SELECT * FROM [TableName];"
Closing this issue.