Recent

Author Topic: [Solved] MySQL 2 SQLite3 problem with type bit(1)  (Read 4286 times)

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
[Solved] MySQL 2 SQLite3 problem with type bit(1)
« on: May 10, 2012, 06:42:47 pm »
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
« Last Edit: May 11, 2012, 12:10:39 am by jeanchristophe »

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: MySQL 2 SQLite3 problem with type bit(1)
« Reply #1 on: May 10, 2012, 08:42:03 pm »
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

  • Jr. Member
  • **
  • Posts: 88
Re: MySQL 2 SQLite3 problem with type bit(1)
« Reply #2 on: May 11, 2012, 12:10:14 am »
Thank you very much ludob!

Best Regards
Jean-Christophe

Lacak2

  • Guest
Re: [Solved] MySQL 2 SQLite3 problem with type bit(1)
« Reply #3 on: May 11, 2012, 07:23:53 am »
I 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
« Last Edit: May 11, 2012, 07:29:07 am by Lacak2 »

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
Re: [Solved] MySQL 2 SQLite3 problem with type bit(1)
« Reply #4 on: May 11, 2012, 12:07:25 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: [Solved] MySQL 2 SQLite3 problem with type bit(1)
« Reply #5 on: May 11, 2012, 12:17:54 pm »
SQLDB+TSQLite3Connection does not require a server - so your point 2 is invalid ;)

Regarding point 1: see the database tutorials/pages on the wiki...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
Re: [Solved] MySQL 2 SQLite3 problem with type bit(1)
« Reply #6 on: May 11, 2012, 01:06:26 pm »
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