Lazarus

Programming => Databases => Topic started by: willbprog9933 on October 09, 2019, 04:13:33 am

Title: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: willbprog9933 on October 09, 2019, 04:13:33 am
Hello friends,

Here is the code I am using to connect to an MS Access (.mdb) database without DSN using ODBConn and the ODBC driver provided by MDB Tools:

Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2.  
  3. var
  4.   conn: TODBCConnection;
  5.   trans: TSQLTransaction;
  6.   query: TSQLQuery;
  7.  
  8. begin
  9.   conn := TODBCConnection.Create(nil);
  10.   trans := TSQLTransaction.Create(nil);
  11.   query := TSQLQuery.Create(nil);
  12.  
  13.   // connection
  14.   conn.Driver := 'MDBTools';
  15.   conn.Params.Add('DBQ=/home/will/test_data/office1');
  16.   conn.Connected := True;
  17.   conn.KeepConnection := True;
  18.  
  19.   // transaction
  20.   trans.DataBase := conn;
  21.   trans.Action := caCommit;
  22.   trans.Active := True;
  23.  
  24.   // query
  25.   query.DataBase := conn;
  26.   query.UsePrimaryKeyAsKey := False;
  27.   query.SQL.Text := 'SELECT FirstName, LastName FROM Patients WHERE LastName = ''SMITH''';
  28.   query.Open;
  29.  
  30.   ListBox1.Items.Add(query.FieldByName('LastName').Value + ', ' + query.FieldByName('FirstName').Value);
  31. end;

When I run this code, I get the following error:
Code: Text  [Select]
  1. Could not get updatable attribute for column 1. ODBC error details: LastReturnCode: SQL_ERROR;.

Column 1 is an autoincrementing large integer, which is also the primary key.

I have read other posts on the forums, and people generally say "Use a *real* database" when Access is brought up.  Well, the issue here is that I'm trying to write a converter from Access to Firebird or possibly MariaDB, so I initially need to successfully read Access databases.

Does anyone have guidance on this issue?

Thanks everyone! :D

- Debian Linux 10 - amd64
- Lazarus 2.0.4
- FPC 3.0.4
Title: Re: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: Zvoni on October 09, 2019, 09:01:06 am
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?
I would have expected something like this
Code: Pascal  [Select]
  1. Connection:=TODBCConnection.Create(nil);
  2. Connection.DatabaseName:=PathToMyDatabase;
  3. Transaction:=TSQLtransaction.Create(Connection);
  4. Connection.Transaction:=Transaction;
  5. Query:=TSQLQuery.Create(nil);
  6. Query.Database:=Connection;
  7. Query.Transaction:=Transaction;
  8. //Set some Driver-Params
  9. Connection.Open;

On a sidenote:
1) In your SELECT --> Use Parameters, then you don't have to escape the single apostrophs
2) Are you sure that your Last Names in the Database are in UPPER-Case? IIRC SQL is case-sensitive, so SMITH<>Smith
3) After a SQL-Query it's good practice (at least IMO) to first check the count of returned Recordsets (just if Recordcount is >0) BEFORE doing anything with it.
Title: Re: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: willbprog9933 on October 09, 2019, 04:55:18 pm
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?
I would have expected something like this
Code: Pascal  [Select]
  1. Connection:=TODBCConnection.Create(nil);
  2. Connection.DatabaseName:=PathToMyDatabase;
  3. Transaction:=TSQLtransaction.Create(Connection);
  4. Connection.Transaction:=Transaction;
  5. Query:=TSQLQuery.Create(nil);
  6. Query.Database:=Connection;
  7. Query.Transaction:=Transaction;
  8. //Set some Driver-Params
  9. Connection.Open;

On a sidenote:
1) In your SELECT --> Use Parameters, then you don't have to escape the single apostrophs
2) Are you sure that your Last Names in the Database are in UPPER-Case? IIRC SQL is case-sensitive, so SMITH<>Smith
3) After a SQL-Query it's good practice (at least IMO) to first check the count of returned Recordsets (just if Recordcount is >0) BEFORE doing anything with it.

Thanks for that.  I'll check it out and see how it goes.  The code example I provided was just minimal to get the point across so the things noted in your sidenote aren't there.

Thanks again :)
Title: Re: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: willbprog9933 on October 09, 2019, 06:33:57 pm
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?

I just gave your corrections a try, but I'm still getting the same error message.  :(
Title: Re: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: Zvoni on October 10, 2019, 08:10:52 am
Hmm, looking at the Source-code of odbcconn there seems to be a problem with the "Is updatable?"-Property of your Column 1
Have you checked your Access-DB? Could you provide the Properties of your Column 1 (I'm guessing it's the PrimaryKey, ID or something)
Title: Re: 'Could not get updatable attribute' when using ODBC to use MS Access
Post by: willbprog9933 on October 10, 2019, 06:59:50 pm
Hmm, looking at the Source-code of odbcconn there seems to be a problem with the "Is updatable?"-Property of your Column 1
Have you checked your Access-DB? Could you provide the Properties of your Column 1 (I'm guessing it's the PrimaryKey, ID or something)

Column 1 is an autoincrementing large integer, which is also the primary key.

Thanks for your research and help Zvoni! :)

I had forgotten that I had coded up a Visual Basic 6 app a while ago that converts our customer databases to sqlite3 as well as CSV, so I think I'll probably just go that route instead of banging my head against the MS Access ODBC problem.  It's too bad that MDB Tools and other open-source Access things didn't work right, but that's life, I guess.  Being an open-source programmer myself, I understand the challenges and struggles of maintaining open-source projects.