Reading sqlite largeint values with Zeos

[reiser]

Hi,

I'm trying to make function which will import data from one sqlite database to other. Specifically what I'm after is to make function that can import cookies to Chrome cookies database (which is in sqlite format).

I have this code:

Code: Select all

procedure ImportToChromeZ(const ASourceDatabase, ATargetDatabase: String);
var
  zconn_source, zconn_target: TZConnection;
  zquery_read, zquery_write : TZQuery;
  creation_utc,
  expires_utc,
  last_access_utc           : Int64;
  host_key,
  name,
  value,
  path                      : String;
  secure,
  httponly,
  has_expires,
  persistent,
  priority                  : Integer;
begin
  zconn_source := TZConnection.Create(nil);
  try
    zconn_source.Protocol := 'sqlite-3';
    zconn_source.Database := ASourceDatabase;
    zconn_source.Connect;
    if zconn_source.Connected then
    try
      zconn_target := TZConnection.Create(nil);
      try
        zconn_target.Protocol := 'sqlite-3';
        zconn_target.Database := ATargetDatabase;
        zconn_target.Connect;
        if zconn_target.Connected then
        try
          zquery_read := TZQuery.Create(nil);
          try
            zquery_read.Connection := zconn_source;
            zquery_read.SQL.Text := 'SELECT * FROM cookies';
            zquery_read.Active := TRUE;

            zquery_write := TZQuery.Create(nil);
            try
              zquery_write.Connection := zconn_target;
              zquery_write.SQL.Text := 'INSERT INTO ' +
              'cookies(creation_utc, host_key, name, value, path, expires_utc, secure, httponly, last_access_utc, has_expires, persistent, priority) ' +
              'VALUES(:PCreationUTC, :PHostKey, :PName, :PValue, :PPath, :PExpiresUTC, :PSecure, :PHTTPOnly, :PLastAccessUTC, :PHasExpires, :PPersistent, :PPriority)';

              zconn_target.StartTransaction;
              try
                while not zquery_read.Eof do
                begin
                  creation_utc := zquery_read.FieldByName('creation_utc').AsLargeInt;
                  expires_utc := zquery_read.FieldByName('expires_utc').AsLargeInt;
                  last_access_utc := zquery_read.FieldByName('last_access_utc').AsLargeInt;
                  host_key := zquery_read.FieldByName('host_key').AsString;
                  name := zquery_read.FieldByName('name').AsString;
                  value := zquery_read.FieldByName('value').AsString;
                  path := zquery_read.FieldByName('path').AsString;
                  secure := zquery_read.FieldByName('secure').AsInteger;
                  httponly := zquery_read.FieldByName('httponly').AsInteger;
                  has_expires := zquery_read.FieldByName('has_expires').AsInteger;
                  persistent := zquery_read.FieldByName('persistent').AsInteger;
                  priority := zquery_read.FieldByName('priority').AsInteger;

                  zquery_write.ParamByName('PCreationUTC').Value := creation_utc;
                  zquery_write.ParamByName('PHostKey').Value := host_key;
                  zquery_write.ParamByName('PName').Value := name;
                  zquery_write.ParamByName('PValue').Value := value;
                  zquery_write.ParamByName('PPath').Value := path;
                  zquery_write.ParamByName('PExpiresUTC').Value := expires_utc;
                  zquery_write.ParamByName('PSecure').Value := secure;
                  zquery_write.ParamByName('PHTTPOnly').Value := httponly;
                  zquery_write.ParamByName('PLastAccessUTC').Value := last_access_utc;
                  zquery_write.ParamByName('PHasExpires').Value := has_expires;
                  zquery_write.ParamByName('PPersistent').Value := persistent;
                  zquery_write.ParamByName('PPriority').Value := priority;

                  zquery_write.ExecSQL;
                  zquery_read.Next;
                end;
              finally
                zconn_target.Commit;
              end;
            finally
              zquery_write.Free;
            end;
          finally
            zquery_read.Free;
          end;
        finally
          zconn_target.Disconnect;
        end;
      finally
        zconn_target.Free;
      end;
    finally
      zconn_source.Disconnect;
    end;
  finally
    zconn_source.Free;
  end;
end;

However, when I try to retrieve values of creation_utc, expires_utc and last_access_utc from database (zquery_read.FieldByName('creation_utc').AsLargeInt line, and following 2 lines in code), they are all zero, while they shouldn't be. Other fields are retrieved properly, but these three fail for some reason. Below are the actual values in table, when I open it with sqlite manager:



As you can see, values should be 13025658039828020, and so on..

And table structure is following:



I've tried with different components, and they retrieved values properly, so I guess that I'm doing something wrong with Zeos, or it is a bug? Any hints/tips would be greatly appreciated.

Thanks!

[EgonHugeist]

Zeos is a common access component. I know we could assume Int64 types for the Integer fields.

Actually we use BIGINT to assume TLongInt-Fields. Which the most RDBM's are using for.

This is an open discussion. Internaly SQLite accepts two Integer-bindings for prepared statments. Not a problem to make this patch..

So others can post here if we should change this or not. On 7.2 if you doubt, not on 7.0 or 7.1. Think about: This is a long-standing code. And it could make loads of trouble if the users do update there components.

[EgonHugeist]

done...