Schema Version Retrieval and Setting

[LanceRas]

There is a PRAGMA user_version feature in SQLite to obtain and set user versioning for the schema to know easily check to see if updates to the schema are needed to be made.

How can this be done using LiteDAC. I'm currently using Delphi XE7.

Thank you for any assistance.

Lance

[AlexP]

Hello,

PRAGMA are the SQLite commands, therefore they are absent in our modules. So, to run them, you can use any components allowing to run commands, for example:

Code: Select all

  LiteConnection1.ExecSQL('PRAGMA user_version=1');

or

Code: Select all

  LiteQuery1.SQL.Text := 'PRAGMA user_version=1';
  LiteQuery1.Execute;

[LanceRas]

That makes sense for setting the value.

What about retrieval of the value?

[AlexP]

Code: Select all

  LiteQuery1.SQl.Text := 'PRAGMA user_version=1';
  LiteQuery1.Execute;
  LiteQuery1.SQl.Text := 'PRAGMA user_version';
  LiteQuery1.Open;
  ShowMessage(LiteQuery1.FieldByName('user_version').asString);

[LanceRas]

Alex,

Thank you.

I tried the following:

Code: Select all

  LiteQuery1.SQl.Text := 'PRAGMA user_version=1';
  LiteQuery1.Execute;
  LiteQuery1.SQl.Text := 'PRAGMA user_version';
  LiteQuery1.Open;
  ShowMessage(LiteQuery1.FieldByName('user_version').asString);

The dialog shows: 'TLargeintField'.

So something isn't quite right.

[AlexP]

Please specify:
1) the exact LiteDAC version;
2) the exact IDE version;
3) the exact SQLite3.dll library version;
4) the bitness of the created application.

[LanceRas]

Latest LiteDac available on website as of Jan 9th, 2014. Used precompiled installer, not source.
Delphi XE7 with all update/service packs.
Using Delphi VCL project and used embedded option.

[AlexP]

We cannot reproduce the problem on the latest LiteDAC version and XE7. Please run the following code, and let us know the result.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, LiteAccess;

var
  LiteConnection: TLiteconnection;
  LiteQuery: TLiteQuery;
begin
  LiteConnection := TLiteconnection.Create(nil);
  try
    LiteConnection.ConnectString := 'DataBase=:memory:;LoginPrompt=False;Direct=True';
    LiteConnection.Connect;
    WriteLn('SQLite Verion: ' + LiteConnection.ClientLibraryVersion); //SQLite Verion: 3.8.7.1 (direct access)
    WriteLn('LiteDAC Verion: ' + LiteDACVersion); //LiteDAC Verion: 2.4.12
    LiteQuery := TLiteQuery.Create(nil);
    try
      LiteQuery.Connection := LiteConnection;
      LiteQuery.SQl.Text := 'PRAGMA user_version=1';
      LiteQuery.Execute;
      LiteQuery.SQl.Text := 'PRAGMA user_version';
      LiteQuery.Open;
      WriteLn('user_version: ' + LiteQuery.FieldByName('user_version').asString); //user_version: 1
    finally
      LiteQuery.Free;
    end;
  finally
    LiteConnection.Free;
    ReadLn;
  end;
end.

[LanceRas]

Strange. I just set up a new project. Did the following and all works. Will have to figure what is different with my other project.

procedure TForm1.Button1Click(Sender: TObject);
var
MyDBFile: string;
begin
MyDBFile := IncludeTrailingPathDelimiter(ExtractFilePath(
Application.ExeName))+'MyDB.db3';

LiteConnection1.Options.Direct := True;
LiteConnection1.Options.ForceCreateDatabase := True;
LiteConnection1.Database := MyDBFile;
LiteConnection1.Connect();

LiteSQL1.Connection := LiteConnection1;
LiteSQL1.SQL.Clear();
LiteSQL1.SQL.Text :=
'CREATE TABLE IF NOT EXISTS SWPDBLS(' +
'B1ID Integer, ' +
'B2ID Integer, ' +
'SID Integer, ' +
'SEnt Numeric, ' +
'HEnt Numeric, ' +
'PRIMARY KEY (B1ID, B2ID))';
LiteSQL1.Execute;

LiteSQL1.SQL.Clear();
LiteSQL1.SQL.Text :=
'CREATE UNIQUE INDEX IF NOT EXISTS idxSWPDBLS ON SWPDBLS(B1ID,B2ID,SID)';
LiteSQL1.Execute;
LiteConnection1.Disconnect();

LiteQuery1.Connection := LiteConnection1;
LiteQuery1.SQl.Text := 'PRAGMA user_version=3';
LiteQuery1.Execute;
LiteQuery1.SQl.Text := 'PRAGMA user_version';
LiteQuery1.Open;
ShowMessage(LiteQuery1.FieldByName('user_version').asString);

end;

[AlexP]

We cannot reproduce the issue even on your sample. Please specify the exact LiteDAC version. The information about the LiteDAC version can be found in the LiteConnection dialog (double-click on the component) at the About tab, or via the main menu Delphi -> LiteDAC -> About LiteDAC.

[LanceRas]

Wow. Apologies.

I found the stupid little mistake. Instead of .AsString for the result, I had used .ToString. No idea what I was thinking and couldn't see the snake.

All works great. Thank you!

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.