Jsondataobjects Sequence and restore
Jsondataobjects is known as the fastest JSON library in Delphi and supports cross-platform.
Cxg 2017-9-12
Use Jsondataobjects (cross platform JSON Library)
Use Delphi 10.2.1
Unit Ujson;
Interface
Uses
System.sysutils, soap. ENCDDECD, Web.httpapp, system.netencoding, Data.db,
System.classes, jsondataobjects;
{"Data": [{"field1": value1, "Field2": value2}]};
function Datasettojson (dataset:tdataset): string;
{"Data": [{"field1": value1, "Field2": value2}]};
Procedure Jsontodataset (const json:string; dataset:tdataset);
{"Cols": [{"Name": "Field1", "size": 0, "type": "int"}] "data": [{"Field1": Value1}]};
function Datasettojsoncols (dataset:tdataset): string;
// {
"Update": [{"Table": "Table 1", "where": "Field 1=1", "Field 1": "1", "Field 2": 0}]
, "Insert": [{"Table": "1", "Field 1": "1", "Field 2": 0}]
, "delete": [{"Table": "1", "where": "Field 1=1"}]
// };
Procedure Parsejsonsql (const json:string; outsql:tstrings);
Implementation
Procedure Parsejsonsql (const json:string; outsql:tstrings);
// {
"Update": [{"Table": "Table 1", "where": "Field 1=1", "Field 1": "1", "Field 2": 0}]
, "Insert": [{"Table": "1", "Field 1": "1", "Field 2": 0}]
, "delete": [{"Table": "1", "where": "Field 1=1"}]
// };
Var
obj, Childobj:tjsonobject;
TableName, SQL, lname, lvalue, where:string;
I, J:integer;
function _getvalue (value:pjsondatavalue): string;
{
Tjsondatatype = (
Jdtnone, Jdtstring, Jdtint, Jdtlong, Jdtulong, Jdtfloat, Jdtdatetime
, Jdtbool, Jdtarray, Jdtobject
);
}
Begin
Case Value.typ of
Jdtstring:result: = Quotedstr (Utf8tostring (rawbytestring (TNetEncoding.URL.Decode (value. Value))); Decoding
Jdtbool:result: = Booltostr (value. Boolvalue);
Jdtfloat:result: = Floattostr (value. Floatvalue);
Jdtint, Jdtlong, Jdtulong:result: = IntToStr (value. Intvalue);
Jdtdatetime:result: = Datetimetostr (value. Datetimevalue);
End
End
Begin
If Outsql = Nil Then
Exit;
Outsql. Clear;
Obj: = Tjsonobject.parse (JSON) as Tjsonobject;
Try
Parse and generate the insert SQL begin-----------------------------------
For I: = 0 to obj. a[' Insert ']. Count-1 do
Begin
Childobj: = obj. a[' Insert ']. O[i];
LName: = ";
Lvalue: = ";
For J: = 0 to Childobj. Count-1 do
Begin
If Childobj. NAMES[J] = ' table ' Then
Begin
TableName: = Childobj. ITEMS[J]. Value;
Continue;
End
If lname = "Then
LName: = Childobj. NAMES[J]
Else
LName: = lname + ', ' + childobj. NAMES[J];
If Lvalue = "Then
Lvalue: = _getvalue (childobj. ITEMS[J])
Else
Lvalue: = Lvalue + ', ' + _getvalue (childobj. ITEMS[J]);
End
sql: = ' INSERT INTO ' + tablename + ' (' + lname + ') VALUES (' + lvalue + ') ';
Outsql. ADD (SQL);
End
Parse and generate the insert SQL End----------------------------------------
Parse and generate the update SQL begin---------------------------------------
For I: = 0 to obj. a[' Update '. Count-1 do
Begin
Childobj: = obj. a[' Update '. O[i];
LName: = ";
Lvalue: = ";
For J: = 0 to Childobj. Count-1 do
Begin
If Childobj. NAMES[J] = ' table ' Then
Begin
TableName: = Childobj. ITEMS[J]. Value;
Continue;
End
Else
If Childobj. NAMES[J] = ' where ' then
Begin
Where: = Childobj. ITEMS[J]. Value;
Continue;
End
If lname = "Then
LName: = Childobj. NAMES[J] + ' = ' + _getvalue (childobj. ITEMS[J])
Else
LName: = lname + ', ' + childobj. NAMES[J] + ' = ' + _getvalue (childobj. ITEMS[J]);
End
sql: = ' Update ' + tablename + ' Set ' + lname + ' where ' + where;
Outsql. ADD (SQL);
End
Parse and generate the update SQL end--------------------------------------------
Parse and generate the delete SQL begin--------------------------------------------
For I: = 0 to obj. a[' Delete '. Count-1 do
Begin
Childobj: = obj. a[' Delete '. O[i];
LName: = ";
Lvalue: = ";
For J: = 0 to Childobj. Count-1 do
Begin
If Childobj. NAMES[J] = ' table ' Then
Begin
TableName: = Childobj. ITEMS[J]. Value;
Continue;
End
Else
If Childobj. NAMES[J] = ' where ' then
Begin
Where: = Childobj. ITEMS[J]. Value;
Continue;
End
End
sql: = ' Delete from ' + tablename + ' where ' + where;
Outsql. ADD (SQL);
End
Parse and generate the delete SQL end-------------------------------------------
Finally
Obj.free;
End
End
function Datasettojsoncols (dataset:tdataset): string;
{"Cols": [{"Name": "Field1", "size": 0, "type": "int"}] "data": [{"Field1": Value1}]};
Var
I:integer;
obj, Childobj:tjsonobject;
Field:tfield;
Blob:tstringstream;
function _getfieldtype (Fld:tfield): string;
Begin
Case FLD. DataType of
Ftboolean:
Result: = ' bool ';
Ftsmallint, Ftinteger, Ftword, Ftautoinc:
Result: = ' int ';
Ftlargeint:
Result: = ' int64 ';
Ftfloat, FTBCD, ftcurrency:
Result: = ' float ';
Fttimestamp, Ftdate, Fttime, Ftdatetime:
Result: = ' datetime ';
Ftstring, Ftfixedchar, Ftmemo, ftwidestring:
Result: = ' string ';
Ftbytes, Ftvarbytes, Ftblob, Ftgraphic, Ftorablob, Ftoraclob:
Result: = ' blob ';
End
End
Begin
{"Cols": [{"Name": "C1", "size": 0, "type": "int"}] "data": [{"C1": 1}]};
Result: = ' {' result ': ' false '} ';
if (DataSet = nil) or (not dataset. Active) Then
Exit;
obj: = tjsonobject.create;
Obj. a[' cols '];
Obj. a[' data ';
DataSet. First;
For I: = 0 to DataSet. FieldCount-1 do//fill cols Array
Begin
Field: = DataSet. Fields[i];
Childobj: = obj. a[' cols '. AddObject;
Childobj. s[' name ']: = field. FieldName;
Childobj. i[' size ']: = field. Size;
Childobj. s[' type ']: = _getfieldtype (field);
Childobj. b[' Required ']: = field. Required;
Childobj. b[' readonly ']: = field. ReadOnly;
End
Fill Data Array
DataSet. First;
While not dataset. Eof do
Begin
Childobj: = obj. a[' data '. AddObject;
For I: = 0 to DataSet. FieldCount-1 do
Begin
Field: = DataSet. Fields[i];
If field. IsNull Then
Childobj. S[field. FieldName]: = "
Else
Begin
Case field. DataType of
Ftboolean:
Childobj. B[field. FieldName]: = field. Asboolean;
Ftsmallint, Ftinteger, Ftword, Ftautoinc:
Childobj. I[field. FieldName]: = field. Asinteger;
Ftlargeint:
Childobj. L[field. FieldName]: = Tlargeintfield (field). Aslargeint;
Ftfloat, FTBCD, ftcurrency:
Childobj. F[field. FieldName]: = field. Asfloat;
Fttimestamp, Ftdate, Fttime, Ftdatetime:
Childobj. D[field. FieldName]: = field. Asdatetime;
Ftstring, Ftfixedchar, Ftmemo, ftwidestring:
Childobj. S[field. FieldName]: = TNetEncoding.URL.Encode (String (Utf8encode (field). (asstring))); Coding
Ftbytes, Ftvarbytes, Ftblob, Ftgraphic, Ftorablob, Ftoraclob:
Begin
Blob: = Tstringstream.create (');
Try
Tblobfield (field). Savetostream (BLOB);
Childobj. S[field. FieldName]: = Encodestring (BLOB. datastring); Base64 encoding
Finally
Blob. Free;
End
End
End
End
End End for
DataSet. Next;
End End While
Result: = obj. ToString;
End
Procedure Jsontodataset (const json:string; dataset:tdataset);
{"Data": [{"field1": value1, "Field2": value2}]};
Var
obj, Childobj:tjsonobject;
I, J:integer;
Field:tfield;
Blob:tstringstream;
Begin
if (DataSet = nil) or (not dataset. Active) or (json = ' {"Result": "false"} ') then
Exit;
Obj: = Tjsonobject.parse (JSON) as Tjsonobject;
DataSet. Disablecontrols;
Try
For I: = 0 to obj. a[' data '. Count-1 do
Begin
DataSet. Append;
Childobj: = obj. a[' data '. O[i];
If childobj = Nil Then
Continue
For J: = 0 to DataSet. FieldCount-1 do
Begin
Field: = DataSet. FIELDS[J];
If field = Nil Then
Continue;
Case Field.datatype of
Ftboolean:
Field. Asboolean: = Childobj. B[field. FieldName];
Ftfloat, FTBCD, ftcurrency:
Field. Asfloat: = Childobj. F[field. FieldName];
Ftsmallint, Ftinteger, Ftword, Ftautoinc:
Field. Asinteger: = Childobj. I[field. FieldName];
Ftstring, Ftfixedchar, Ftmemo, ftwidestring:
Field. Asstring: =utf8tostring (rawbytestring (TNetEncoding.URL.Decode (childobj. S[field. FieldName])); Decoding
Fttimestamp, Ftdate, Fttime, Ftdatetime:
Field. Asdatetime: = Childobj. D[field. FieldName];
Ftlargeint:
Tlargeintfield (field). Aslargeint: = Childobj. L[field. FieldName];
Ftbytes, Ftvarbytes, Ftblob, Ftgraphic, Ftorablob, Ftoraclob:
Begin
Blob: = Tstringstream.create (Decodestring (childobj. S[field. FieldName]));
Try
Tblobfield (field). Loadfromstream (BLOB);
Finally
Blob. Free;
End
End
End
End
DataSet. Post;
End
Finally
DataSet. Enablecontrols;
Obj.free;
End
End
function Datasettojson (dataset:tdataset): string;
{"Data": [{"field1": value1, "Field2": value2}]};
Var
I:integer;
obj, Childobj:tjsonobject;
Field:tfield;
Blob:tstringstream;
Begin
Result: = ' {' result ': ' false '} ';
if (DataSet = nil) or (not dataset. Active) Then
Exit;
obj: = tjsonobject.create;
Try
Obj. a[' data ';
DataSet. First;
While not dataset. Eof do
Begin
Childobj: = obj. a[' data '. AddObject;
For I: = 0 to DataSet. FieldCount-1 do
Begin
Field: = DataSet. Fields[i];
If field. IsNull Then
Childobj. S[field. FieldName]: = "
Else
Begin
Case Field.datatype of
Ftboolean:
Childobj. B[field. FieldName]: = field. Asboolean;
Ftsmallint, Ftinteger, Ftword, Ftautoinc:
Childobj. I[field. FieldName]: = field. Asinteger;
Ftlargeint:
Childobj. L[field. FieldName]: = Tlargeintfield (field). Aslargeint;
Ftcurrency, Ftfloat, FTBCD:
Childobj. F[field. FieldName]: = field. Asfloat;
Fttimestamp, Ftdate, Fttime, Ftdatetime:
Childobj. D[field. FieldName]: = field. Asdatetime;
Ftstring, Ftfixedchar, Ftmemo, ftwidestring:
Childobj. S[field. FieldName]: = TNetEncoding.URL.Encode (String (Utf8encode (field). (asstring)));
Ftbytes, Ftvarbytes, Ftblob, Ftgraphic, Ftorablob, Ftoraclob:
Begin
Blob: = Tstringstream.create (');
Try
Tblobfield (field). Savetostream (BLOB);
Childobj. S[field. FieldName]: = Encodestring (BLOB. datastring); Base64 encoding
Finally
Blob. Free;
End
End
End
End
End End for
DataSet. Next;
End End While
Result: = obj. ToString;
Finally
Obj. Free;
End
End
End.
Jsondataobjects Sequence and restore