[Solved] MySQL 2 SQLite3 problem with type bit(1)
lazarus / 16-Databases

----------------------------------------------------------------------
jeanchristophe · 2012-05-10 16:42

Hello

I have a problem with the type bit(1) from MySQL.
I try to get it "asboolean" or "asinteger" and lately with
"asvariant", but it is not working:
Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var countrecord: longint; recordnumber: longint;
begin
  countrecord:= Ztable1.RecordCount;
  for recordnumber:=0 to (countrecord) do
  begin
     ZTable1.RecNo:= recordnumber;
     Sqlite3Dataset1.Insert;
     ...
     Sqlite3Dataset1.FieldByName('Stemplet').Asinteger
:=ZTable1.FieldByName('Stemplet').AsVariant;
     Sqlite3Dataset1.FieldByName('Varianter').Asinteger
:=ZTable1.FieldByName('Varianter').Asvariant;
     ...
     Sqlite3Dataset1.Post;
     Sqlite3Dataset1.ApplyUpdates;
  end
end;       

With variant, I got the following message:
Code: [Select]Project ... raised exception class "EVariantError" with
message: Invalid variant type cast
I change the field types for SQLite3 from integer to boolean, but it
doesn't helps.
Do you have an idea on how I can change their types?
I can maybe make a variable like this:
Code: [Select]a:= ZTable1.FieldByName('Varianter').Asvariant;

But wich type do I have to use then for "a"?

For me it is no problem if it is integer ot boolean, because the
fields are "0" or "1"

Do you have an idea?

Best Regards
Jean-Christophe

----------------------------------------------------------------------
ludob · 2012-05-10 18:42

Zeos doesn't seem to support bit(1) correctly. It returns field type
FTBLOB. sqldb doesn't support bit(1) neither.

A solution is to cast the bit(1) to an integer:
Code: [Select]select id, convert(Stemplet, unsigned) as Stemplet from
table
If you have a lot of columns in the table and don't fancy naming them
all do
Code: [Select]select *, convert(Stemplet, unsigned) as IStemplet from
table
and use ZTable1.FieldByName('IStemplet') instead of
ZTable1.FieldByName('Stemplet').

----------------------------------------------------------------------
jeanchristophe · 2012-05-10 22:10

Thank you very much ludob!

Best Regards
Jean-Christophe

----------------------------------------------------------------------
Lacak2 · 2012-05-11 05:23

Quote from: jeanchristophe on May 10, 2012, 06:42:47 pmI have a
problem with the type bit(1) from MySQL.
...
I change the field types for SQLite3 from integer to boolean, but it
doesn't helps.
...
For me it is no problem if it is integer ot boolean, because the
fields are "0" or "1"

Do you have an idea?
AFAIR TSQLite3Connection from sqlDB supports BOOLEAN datatype (as 0/1)
So changing from bit(1) to boolean should work!
Note that in your example you used not TSQLite3Connection from sqlDB
package, but TSQLite3Dataset (which is another data accessing
component for SQLite3 db)

Note that BIT is propertiary data type, not a part of sql standard

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 10:07

Thank you for your answer lacak2.

No I am not using TSQLite3 connection for 2 reason:
1 I don't know how to use it (I am honest!)
2 My database is local (and again I dont know how I can make it run on
a server).

Best Regards
Jean-Christophe

----------------------------------------------------------------------
BigChimp · 2012-05-11 10:17

SQLDB+TSQLite3Connection does not require a server - so your point 2
is invalid

Regarding point 1: see the database tutorials/pages on the wiki...

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 11:06

Hello Bigchimp

I read the tutorial since, and it works fine!
I went back to the forum because I have a new question...

Best Regards
Jean-Christophe