I just build the JSON data on request.
Yes I know the code could be optimized and generalized. I just did this as a test.. it works great though.. and my client side can reference the data by fieldname.
procedure TWebModule1.WebModule1waJSONAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
i: integer;
j: integer;
tempstr: string;
begin
//JSON Test Function returns a JSON String
if query1.Active then query1.Close; //close if open
Query1.Sql.Text := 'select * from "member.dat" ';
query1.Open;
//build header with fields and records
tempstr := '';
response.Content := '{';
response.content := response.content + '"fields" : "'; //beginning of field data
for i := 0 to query1.FieldDefs.Count - 1 do begin
if i <> 0 then tempstr := tempstr + ',';
tempstr := tempstr + query1.Fielddefs[i].Name;
end;
response.content := response.content + tempstr; //field data
response.content := response.content + '",'; //end of field data
// response.content := response.content + '{ "fields" : "field1,field2",';
//write the number of records
response.content := response.content + '"records" : ' + inttostr(query1.RecordCount) + ',';
response.content := response.content + '"rows" : ['; //beginning of rows
//write row data
// repeat
for j := 1 to 375 do begin
response.content := response.content + '{'; //beginning of row
tempstr := '';
for i := 0 to query1.FieldDefs.Count - 1 do begin
if i <> 0 then tempstr := tempstr + ',';
{
unhandled types: ftADT,ftArray,ftReference,ftDataSet
}
//check for illegal characters
if (pos('"', query1.Fields[i].AsString) = 0) and
(pos('á', query1.Fields[i].AsString) = 0) and
(pos('é', query1.Fields[i].AsString) = 0) and
(pos('ó', query1.Fields[i].AsString) = 0) and
(pos('[', query1.Fields[i].AsString) = 0) and
(pos(']', query1.Fields[i].AsString) = 0) and
(pos('{', query1.Fields[i].AsString) = 0) and
(pos('}', query1.Fields[i].AsString) = 0) and
(pos('''', query1.Fields[i].AsString) = 0) then begin
//is a boolean javascript bools are lowercase
if query1.FieldDefs[i].DataType = ftBoolean then
tempstr := tempstr + '"' + query1.FieldDefs[i].Name + '":' + lowercase(booltostr(query1.Fields[i].asboolean)) + '';
//is a string
if (query1.FieldDefs[i].DataType = ftString) or
(query1.FieldDefs[i].DataType = ftUnknown) or
(query1.FieldDefs[i].DataType = ftblob) or
(query1.FieldDefs[i].DataType = ftVarBytes) or
(query1.FieldDefs[i].DataType = ftbytes) or
(query1.FieldDefs[i].DataType = ftDate) or
(query1.FieldDefs[i].DataType = ftTime) or
(query1.FieldDefs[i].DataType = ftDateTime) then
tempstr := tempstr + '"' + query1.FieldDefs[i].Name + '":"' + query1.Fields[i].AsString + '"';
//is a integer or float or bcd
if (query1.FieldDefs[i].DataType = ftsmallint) or
(query1.FieldDefs[i].DataType = ftinteger) or
(query1.FieldDefs[i].DataType = ftFloat) or
(query1.FieldDefs[i].DataType = ftBCD) or
(query1.FieldDefs[i].DataType = ftWord) or
(query1.FieldDefs[i].DataType = ftLargeInt) then
tempstr := tempstr + '"' + query1.FieldDefs[i].Name + '":' + query1.Fields[i].AsString;
end
else begin
tempstr := tempstr + '"' + query1.FieldDefs[i].Name + '":"---Contains Invalid JSON Character---"';
end;
end;
response.content := response.content + tempstr; //row data
response.content := response.content + '}'; //end of row
query1.Next;
// if query1.Eof = false then response.content := response.content + ','; //separate the rows
if j<>375 then response.content := response.content + ','; //separate the rows
end; //for loop
// until (query1.eof);
response.content := response.content + ']'; //end of rows
response.content := response.content + '}'; // end of JSON object
handled := true;
end;