CODEWITHSUNDEEP
jsondelphidatasnap
bpromas
bpromas

Reputation: 694

Inserting a JSON Object into a database

I am building a DataSnap server in Delphi XE2, and I'm having trouble figuring out the best way to insert the JSON Object that the client sends me into a Database.

Here's the format of the object I'm receiving:

{"PK":0,"FIELD1":"EXAMPLE","FIELD2":5, "DATE":""}

The solution I found was the following code

with qryDBMethods do
  begin
    SQL.Text := 'SELECT * FROM Table';
    Open;
    Append;
    FieldByName('PK')    .AsInteger := StrToInt(newId.ToString)
    FieldByName('FIELD1').AsString  := Object.Get('FIELD1').JsonValue.Value;
    FieldByName('FIELD2').AsInteger := StrToInt(Object.Get('FIELD2').JsonValue.Value); 
    FieldByName('DATE')  .AsDateTime:= Now;    
    Post;

After that, I would have my query component made into a JSON Object and returned to my client, but problem is, this is going to be a big application with dense tables and so doing a "SELECT * " every time I want to insert something isn't ideal. What is the best way to do this?

Upvotes: 2

Views: 2759

Answers (2)

M. P. R.
M. P. R.

Reputation: 215

If what the problem is the amount of data when you open a Select * From Table, why not do something like Select * From Table Where 1 <> 1?

This way you would be able to insert while not loading any result.

Other option would to make an Insert script instead.

Upvotes: 2

RRUZ
RRUZ

Reputation: 136401

Try using a INSERT sentence instead.

with qryDBMethods do
  begin
    SQL.Text := 'INSERT INTO Table (PK, FIELD1, FIELD2) VALUES (:PK, :FIELD1, :FIELD2)';
    ParamByName('PK')    .Value:= StrToInt(newId.ToString)
    ParamByName('FIELD1').Value := Object.Get('FIELD1').JsonValue.Value;
    ParamByName('FIELD2').Value:= StrToInt(Object.Get('FIELD2').JsonValue.Value);    
    ExecSQL();

Upvotes: 8

Related Questions