Jump to content
Sign in to follow this  
IElite

sqLite's TSQLiteResult Values

Recommended Posts

TSQLiteResult Values is an array of Variant. How do I get the correct type from it? In Delphi, you just use implicit type casting, but that doesn't seem to work here/

example as integer

function TfrmMain.getCount: integer;
var
 SqlResult: TSQLiteResult;
begin
 try
  SqlResult := FDataBase.Exec("SELECT COUNT(*) FROM workstations;");
 finally
  result:= sqlResult.Values[0];  //does not work
  result:= '';
 end;
end;

 

I have also tried explicit type casting

result:= Integer(sqlResult.Values[0]);   //does not work

 

Please create a Database forum group :)

Share this post


Link to post
Share on other sites

actually @Igor Savkic, I am sure that works, it looks like I have bigger issues

Values is of type TSQLiteRowValues which is a record

and it has a property of values which is the array of variant

I have tried something such as this, but it doesnt work either

TVariant.AsInteger(TSQLiteRowValues(sqlResult.Values[0]).values[0]));

 

Share this post


Link to post
Share on other sites

writeln(JSON.Stringify(sqlResult.Values));

Syntax Error: Argument 0 expects type "Variant" instead of "array of record TSQLiteRowValues" [line: 98, column: 30, file: frmMain]


writeln(JSON.Stringify(sqlResult.Values[0]));

gives me the json structure

but how do i get to the individual data values

var J: variant

  asm
   @j = JSON.parse(JSON.Stringify(sqlResult.Values[0]));
  end;

writeln(J[0]);

but it doesnt work

Share this post


Link to post
Share on other sites

> writeln(JSON.Stringify(sqlResult.Values));

> Syntax Error: Argument 0 expects type "Variant" instead of "array of record TSQLiteRowValues" [line: 98, column: 30, file: frmMain]

Try casting to Variant like: writeln(JSON.Stringify(Variant(sqlResult.Values)));

or otherwise use asm to assign sqlResult.Values to some Variant variable, something like:

asm @Temp = @sqlResult.Values; end;

> writeln(JSON.Stringify(sqlResult.Values[0]));

> gives me the json structure, but how do i get to the individual data values

It's easy if you know structure, how does it look like?

 

On unrelated note, is there any way to control formatting in this forum editor, my reply really looks bad but I don't see a way to change a simple thing.

Share this post


Link to post
Share on other sites

"On unrelated note, is there any way to control formatting in this forum editor, my reply really looks bad but I don't see a way to change a simple thing."

I had mentioned this in the past to someone else......i think for some reason, this NEW forum has that shut off for regular members and needs to be changed. Try making a post, or asking @jarto or @lennart if they can have the settings changed.

Share this post


Link to post
Share on other sites
16 hours ago, Igor Savkic said:

> writeln(JSON.Stringify(sqlResult.Values));

> Syntax Error: Argument 0 expects type "Variant" instead of "array of record TSQLiteRowValues" [line: 98, column: 30, file: frmMain]

Try casting to Variant like: writeln(JSON.Stringify(Variant(sqlResult.Values)));

or otherwise use asm to assign sqlResult.Values to some Variant variable, something like:

asm @Temp = @sqlResult.Values; end;

> writeln(JSON.Stringify(sqlResult.Values[0]));

> gives me the json structure, but how do i get to the individual data values

It's easy if you know structure, how does it look like?

that gives me the same result

[{"values$1":["WS0202","Maintenance","jdoe"]}]

 

I need to be able to get the individual data

e.g.  

WS0202

Maintenance

jdoe

 

Share this post


Link to post
Share on other sites

> that gives me the same result

> [{"values$1":["WS0202","Maintenance","jdoe"]}]

> I need to be able to get the individual data, WS0202, Maintenance, jdoe


OK, so you first have array where elements are objects that have string array called "values$1". 

Try something like:

		
		var
	  v: Variant;
	  i: Integer;		
		  procedure ReadItem(const AValue: Variant);
	  var
	    i: Integer;
	  begin
	    for i := 0 to AValue.length - 1 do
	      WriteLn(AValue[i]);
	  end;		
		begin
	  v := JSON.Parse('[{"values$1":["WS0202","Maintenance","jdoe"]}]');
	  for i := 0 to v.length - 1 do
	    ReadItem(v[i].values$1);    
	end;		
		

Share this post


Link to post
Share on other sites

@Igor Savkic

it doesn't recognize the $ in values$1

procedure TfrmMain.UpdateList;
var
 SqlResult: TSQLiteResult;
 I: Integer;
 fJSONStr: String;
 fData: Variant;
begin
 try
  SqlResult := FDataBase.Exec("SELECT * FROM workstations;");
 finally
  fListBox.BeginUpdate;
  fListBox.Clear;
  for I:= 0 to SqlResult.Values.Count do
  begin
   fJSONStr:= JSON.Stringify(sqlResult.Values[0]);
   fData:= JSON.Parse(fJSONStr);
   //fListBox.Add(fData.values$1);  //doesn't work
  end;
  fListBox.EndUpdate;
 end;
end;

 

OR

procedure TfrmMain.UpdateList;
var
 SqlResult: TSQLiteResult;
 I: Integer;
 fJSONStr: String;
 fData: Variant;

  function ReadItem(const AValue: Variant): String;
      var
        i: Integer;
      begin
      result:= '';
      for i := 0 to AValue.length - 1 do
          result:= result + AValue;
      end;

begin
 try
  SqlResult := FDataBase.Exec("SELECT * FROM workstations;");
 finally
  fListBox.BeginUpdate;
  fListBox.Clear;
  for I:= 0 to SqlResult.Values.Count do
  begin
  fJSONStr:= JSON.Stringify(sqlResult.Values[0]);
  fData:= JSON.Parse(fJSONStr);
  fListBox.Add(
               ReadItem(fData.values$1)
               );

  end;
  fListBox.EndUpdate;
 end;
end;

 

Share this post


Link to post
Share on other sites

Please edit the System.Sqlite unit

  TSQLiteRowValues = Record
    values:  Array of Variant; external "values";  // --> this is trick, obfuscation don't change the field
  End;

  TSQLiteResult = Record
    columns:  Array of String; external 'columns';
    values:   Array of TSQLiteRowValues; external 'values';
  end;

 TSQLiteDatabase = partial class(TObject,ISQLiteProvider)
  // ...
    Procedure     Run(SQL: String); overload;
    Procedure     Run(SQL: String; Values: TVarArray); overload; --> create this overload method

implementation

Procedure TSQLiteDatabase.Run(SQL: String; Values: TVarArray);
begin
  if (FHandle) then
  FHandle.run(SQL, Values) else
  Raise ESQLite.Create
  ('Invalid state for operation, database reference is null error');
end;

Example:

var
  db   : TSQLiteDatabase;
  valor: array of variant;
  res  : TSQLiteResult;
  rows : TSQLiteRowValues;

begin
  db := TSQLiteDatabase.Create;
  db.Run('CREATE TABLE friends (id INTEGER PRIMARY KEY, name TEXT)');
  valor := ['Warley Alex'];
  db.Run("INSERT INTO friends VALUES (NULL, ?)", valor);

  res := db.Exec("SELECT name FROM friends");
  rows := res.values[0];
  console.log(rows.values[0]); // Warley Alex

 

Share this post


Link to post
Share on other sites

On a back-note, if you ever see $ in names, 90% of the time that means you are dumping out managed values. The RTL uses different names inside the code, than the data you get when you extract something from that code.
Like you noticed, the rows are "values" as an array, and you can iterate from there.
This is probably not so intuitive -- i cant wait until we get to the DB stage where we have a framework to build from.
A bit like what DBExpress etc. did for delphi, where you have a fixed number of classes that represents various bits.
I think the websql wrapper came out quite nice, but it can be better.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×