Devart Forums

nszmnsky

Thanks Maxim! That is the route I had taken. Think I understand why TVirtualtable has .AddField but TLiteTable does not.

nszmnsky

I need to add the field to the SQLite table. I know how to do it via "ALTER TABLE", so can go that route if can't be done by calling a procedure in the TLiteTable component.

nszmnsky

Is it possible to use LiteTable component to add a field - like is seen in the demo for VirtualTable?

nszmnsky

Using TLiteConnection in Rio10.3, the compiled program runs on W10 64 but faults when .Connect is called when running on Mac OSX10.13.6 Downgraded back to 3.4.9 and program runs correctly in both places. Is this same issue as "Direct mode crashes" topic?

nszmnsky

I'm getting error when I try to use TCustomDAConnection.GetDatabaseNames in Firemonkey.

Code: Select all

var
  DBlist:TStrings;
  LTS:string;
begin
  DBList:=TStringList.create;
  try
    CRTT.GetDatabaseNames(DBlist); //GetTableNames(DBlist,false,true);
    if(DBlist.Count=1)
    then begin
        LTS := DBM.CRTT.Database;
        LTS := leftstr(LTS,length(LTS)-3)+' - Data.db';
        DBM.LiteSQL1.SQL.Clear;
        DBM.LiteSQL1.SQL.Add('ATTACH DATABASE "'+LTS+'" as "LTS";');
        DBM.LiteSQL1.Execute;
    end;
  finally
    DBlist.Free;
  end;

When using the GetDatabaseNames I get an "Unsupported metadata kind", however the GetTableNames does work and returns the number of tables in the Main db.

I didn't find online documentation, can you provide example of proper use of GetDatabaseNames?

Thanks
Neil

nszmnsky

The code below is not behaving as I thought it would. When this code runs, the [UpdateFN] and [SQL] fields remain null but [Applied] and [Status] get correct data. I know there is something in each of the variables, both since the LiteSQL1.Execute actually does its job and updates database AND because on debug I see correct text assigned to FN

Code: Select all

  UpdatesApplied.Open;                                 
  UpdatesApplied.Edit;
  UpdatesApplied.Append;
  FN:=extractfilename(SQL_FN);
  UpdatesApplied.FieldByName('UpdateFN').AsString:=quotedstr(FN);
  UpdatesApplied.FieldByName('SQL').AsString:=quotedstr(LiteSQL1.SQL.GetText);
  UpdatesApplied.Fieldbyname('Applied').AsDateTime:=Now();
  UpdatesApplied.Fieldbyname('Status').AsString:='PENDING';
  updatesapplied.Post;

Am I missing something? I'm really puzzled because the code below, which also executes on startup does correctly insert data into the table.

Code: Select all

         DBM.UpdatesApplied.Open;
         DBM.UpdatesApplied.Append;
         DBM.UpdatesApplied.FieldByName('UpdateFN').AsString:=extractfilename(Application.Title)+' '+VersionTxt;
         DBM.UpdatesApplied.FieldByName('SQL').AsString:=VersionTxt;
         DBM.UpdatesApplied.FieldByName('Applied').AsDateTime:=now();
         DBM.UpdatesApplied.FieldByName('Status').AsString:='1ST_RUN';
         DBM.UpdatesApplied.Post;

nszmnsky

I'm working on app tethering idea for a project, and want to share the current record of an SQLite table between the two programs. Is there a way to stream a record? I looked at .SaveToXML but that seems to work on entire table not just current record.
Thanks!

nszmnsky

After some offline communications with Maxim, he did find a solution to getting the structure to update. I was closing the tables prior to the DROP but it appears that need to close after drop and create, then re-open prior to use.

However still seeing a table lock when trying to drop table occurring when I run the program outside of the Delphi IDE, but doesn't occur while in the IDE. Maxim suggests that it is due to an incomplete SELECT but changing the compenents FetchAll to true hasn't worked, so trying to close tables and queries before trying brute force of closing and re-opening connection.

nszmnsky

Maxim, I've forward link to download the request items. I also forgot to mention that the behavior is different when not running within XE7, when running the exe directly I get database lock errors.

nszmnsky

Hello
I'm working on an app that needs to create statistics from differing sets of columns in a table. In my code, I drop the existing STATS table then create new version with the desired columns. There are 10 different sets of stats that need to be calculated from two different tables (which is set in TMPtable string) and between 5 and 12 columns (in SQLselect string).

Code: Select all

  DBM.LiteSQL1.SQL.Clear;
  DBM.LiteSQL1.SQL.Add('DROP TABLE IF EXISTS '+TMPtable+'STATS');
  DBM.LiteSQL1.Execute;

  DBM.LiteSQL1.SQL.Clear;
  DBM.LiteSQL1.SQL.Add('CREATE TABLE '+TMPtable+'STATS AS ' + SQLselect);
  DBM.LiteSQL1.SQL.Add(' WHERE '+SQLwhere);
  DBM.LiteSQL1.Execute;

The problem seems to be that the structure of the existing table is "cached" and used even after the table is re-created. So when re-created table contains new column, such as "scSIZE1", accessing using STATS.FieldByName("scSIZE1").asInteger returns error indicating column does not exist. When I check db using "SQLite Expert Pro" the column is in fact there.
I've tried opening and closing, changing active state, and ApplyUpdates but problem still exists. I'm sure I'm missing something obvious but just not seeing the solution so any insights would be appreciated

nszmnsky

I'm using LiteDAC's LiteSQL component to run SQLs to make database updates and have code set to rollback if an error occurs using try..except blocks. That seems to be working well, but I'd also like to capture what the SQL error was but can't find anything that appears to be the error. Is there a way, with LiteSQL or another LiteDAC component, to capture what caused the SQL failure?

nszmnsky

Thanks for the update, I had already started writing code to walk thru the query records to calculate the StdDev.

nszmnsky

Can TLiteUserFunction be used to build an aggregate function? Specifically, I'd like to implement standard deviation and median/mode calculations. But the demo only shows examples (ABS, PERCENT) for function acting on a record.
Thanks
Neil

nszmnsky

That was the solution. Thanks! I simply overlooked that connection would control unicode use.

nszmnsky

Using SQLite in an FMX application, I have entries in database that include a special character ⌡(0x2321). If I cut and past from the DB into text of component (TMSFMXHTMLText) the character displays correctly however if I read text from database in the program (setting a string variable Prmt := DB.TBL.FieldByName('prmtTEXT').AsString) the displayed character is right paren ')' (character 0x0029). If I edit Prmt variable in debug and insert character (using Character Map from system tools) the display is correct. I also tried using WideString but that didn't work either.

It seems that the issue is on the read from the DB but am I missing something?
Thanks in advance for any insight or troubleshooting suggestions

Devart Forums

Search found 15 matches

Re: Add a field to an existing table?

Re: Add a field to an existing table?

Add a field to an existing table?

On Mac V3.4.10 faults at .Connect call but not on W10

GetDatabaseNames documentation

Some fields not getting written correctly

Save current record to stream?

Re: Droppiing table and recreating seems to hold old structure.

Re: Droppiing table and recreating seems to hold old structure.

Droppiing table and recreating seems to hold old structure.

Capture SQL error

Re: Aggregate user function

Aggregate user function

Re: Loss of unicode characters when read from DB

Loss of unicode characters when read from DB