Page 1 of 1
Schema Version Retrieval and Setting
Posted: Fri 09 Jan 2015 04:55
by 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
Re: Schema Version Retrieval and Setting
Posted: Fri 09 Jan 2015 07:24
by 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;
Re: Schema Version Retrieval and Setting
Posted: Fri 09 Jan 2015 08:02
by LanceRas
That makes sense for setting the value.
What about retrieval of the value?
Re: Schema Version Retrieval and Setting
Posted: Fri 09 Jan 2015 08:21
by 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);
Re: Schema Version Retrieval and Setting
Posted: Fri 09 Jan 2015 23:14
by 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.
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 06:22
by 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.
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 06:47
by 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.
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 07:17
by 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.
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 07:35
by 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;
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 07:49
by 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.
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 07:54
by 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!
Re: Schema Version Retrieval and Setting
Posted: Mon 12 Jan 2015 08:20
by AlexP
Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.