Stitching SQL with Jsonarray

function Getjsontowheresql (const ajsonarray:tjsonarray): string;
Var
I:integer;
Ajsonobject:tjsonobject;
afieldname,afieldvalue:string;
sqlwherestring:string;
afieldoperator:string;
Begin
Sqlwherestring: = ";
For I: = 0 to Ajsonarray. Size-1 do
Begin
Ajsonobject: = Ajsonarray. Get (i) as tjsonobject;
Afieldname: = Ajsonobject. Pairs[0]. jsonstring.tostring;
Afieldname: = Replacestr (Afieldname, ' "', ');
Afieldvalue: = Ajsonobject. Pairs[0]. Jsonvalue.value;
Afieldoperator: = Ajsonobject. PAIRS[1]. Jsonvalue.value;
Sqlwherestring: = sqlwherestring + "+ afieldname +" + Afieldoperator +
' + quotedstr (afieldvalue) + ' and ';
End
Result: = ' where ' + leftstr (sqlwherestring,
Length (sqlwherestring)-5);
End

///////////////

function getjsontoinsertsql (const ajsonarray:tjsonarray; tablename:string): string;
Var
I,j:integer;
Ajsonobject:tjsonobject;
sqlstring:string;
afieldname,afieldvalue:string;
Begin
SqlString: = ";
For I: = 0 to Ajsonarray. Size-1 do
Begin
SqlString: = SqlString + ' INSERT INTO ' + tablename + ' (';
Ajsonobject: = Ajsonarray. Get (i) as tjsonobject;
For J: = 0 to Ajsonobject. Size-1 do
Begin
Afieldname: = Ajsonobject. PAIRS[J]. jsonstring.tostring;
SqlString: = SqlString + replacestr (afieldname, ' "', ') + ', ';
End
SqlString: = Leftstr (SqlString, Length (SqlString)-1);
SqlString: = SqlString + ') VALUES (';
For J: = 0 to Ajsonobject. Size-1 do
Begin
Afieldvalue: = Ajsonobject. PAIRS[J]. Jsonvalue.value;
SqlString: = SqlString + quotedstr (afieldvalue) + ', ';
End
SqlString: = Leftstr (SqlString, Length (SqlString)-1);
SqlString: = SqlString + ') ';
End
Result: = SqlString;
End

///////////

function tform1.getjsontoselectsql (const ajsonwherearray:tjsonarray;
Tablename:string;const thesql:string): string;
Var
sqlwherestring:string;
Begin
Sqlwherestring: = Getjsontowheresql (Ajsonwherearray);
Result: = Thesql + ' from ' + tablename + sqlwherestring;
End

/////////

function getjsontoupdadtesql (const ajsonupdatearray,ajsonwherearray:tjsonarray;
tablename:string): string;
Var
I,j:integer;
Ajsonobject:tjsonobject;
sqlstring:string;
afieldname,afieldvalue:string;
setvaluesql,sqlwherestring:string;
afieldoperator:string;
Begin
SqlString: = ";
For I: = 0 to Ajsonupdatearray.size-1 do
Begin
SqlString: = sqlstring + ' Update ' + tablename + ' set ';
Ajsonobject: = Ajsonupdatearray.get (i) as tjsonobject;
Setvaluesql: = ";
For J: = 0 to Ajsonobject. Size-1 do
Begin
Afieldname: = Ajsonobject. PAIRS[J]. jsonstring.tostring;
Afieldname: = Replacestr (Afieldname, ' "', ');
Afieldvalue: = Ajsonobject. PAIRS[J]. Jsonvalue.value;
Setvaluesql: = setvaluesql + afieldname + ' = ' + quotedstr (afieldvalue) + ', ';
End
Setvaluesql: = Leftstr (Setvaluesql, Length (setvaluesql)-1);
//////
Sqlwherestring: = Getjsontowheresql (Ajsonwherearray);
SqlString: = sqlstring + Setvaluesql + sqlwherestring;
End
Result: = SqlString;
End

////////////////

function getjsontodeletesql (const ajsonwherearray:tjsonarray; tablename:string): string;
Var
I:integer;
Ajsonobject:tjsonobject;
sqlstring:string;
afieldname,afieldvalue:string;
sqlwherestring:string;
afieldoperator:string;
Begin
Sqlwherestring: = ";
For I: = 0 to Ajsonwherearray.size-1 do
Begin
Ajsonobject: = Ajsonwherearray.get (i) as tjsonobject;
Afieldname: = Ajsonobject. Pairs[0]. jsonstring.tostring;
Afieldname: = Replacestr (Afieldname, ' "', ');
Afieldvalue: = Ajsonobject. Pairs[0]. Jsonvalue.value;
Afieldoperator: = Ajsonobject. PAIRS[1]. Jsonvalue.value;
Sqlwherestring: = sqlwherestring + "+ afieldname +" + Afieldoperator +
' + quotedstr (afieldvalue) + ' and ';
End
Sqlwherestring: = ' where ' + leftstr (sqlwherestring,length (sqlwherestring)-5);
SqlString: = ' Delete from ' + tablename + sqlwherestring;
Result: = SqlString;
End

Generate Jsonarray

Procedure Tform1.button1click (Sender:tobject);
Var
I:integer;
Jsoncars:tjsonarray;
Car, Price:tjsonobject;
Begin
Jsoncars: = tjsonarray.create;
Try
For I: = Low (cars) to high (cars) does
Begin
Car: = tjsonobject.create;
Jsoncars.addelement (Car);
Car.addpair (' manufacturer ', Cars[i][tcarinfo.manufacturer]);
Car.addpair (' name ', Cars[i][tcarinfo.name]);
Price: = tjsonobject.create;
Car.addpair (' Price ', cars[i][tcarinfo.name]);
Price.addpair (' Value ', Tjsonnumber.create (Cars[i][tcarinfo.price). Tointeger));
Price.addpair (' Currency ', cars[i][tcarinfo.currency]);
End
JSON: = jsoncars.tostring;
Memo3. Text: = JSON;
Finally
Jsoncars.free;
End
End

This article is from the "All for the law, such as Fleeting Dream" blog, please be sure to keep this source http://kaixinbuliao.blog.51cto.com/2567365/1611083

Stitching SQL with Jsonarray