views:

668

answers:

7

I'm building a little personal project; I wish to have a Windows Service that will act as a simple HTTP server accepting requests via HTTP, run queries against an Interbase 6/7 server and then return results in JSON format.

Could anyone recommend some tutorials, free components or just a quick plan of how they would develop the various parts: - Standalone Webserver - Running an Interbase Query - Converting Rows into JSON format

I've tried searching but as a novice programmer I'm getting lost into whats best/recent and whats no longer recommended. I'm interesting in building this project with Delphi 2009 as it would interface with Interbase without needing a ODBC or other driver installed. I also do not wish to have a separate HTTP server (e.g. IIS or Apache).

Any help would be appreciated.

+2  A: 

DataSnap (Midas) got some much needed attention in Delphi 2009 and apparently now uses JSON to communicate between the client and server. Your DataSnap server app can accept requests from clients without needing a web server and can communicate with Interbase (or any database) without the clients needing to know anything about the back end.

Here is a getting started article by Pawel Glowacki to get the ball rolling.

I'm sure you aren't doing it (since IB6 is open source), but this could be considered an underhanded way to use a single named database user for a distributed application. Even if someone pulls it off on a technical level, it's still a license violation.

Bruce McGee
Unfortunately the new JSON part in Delphi is "baked" into the DataSnap core, it can not be used for simple data <-> JSON conversions.
mjustin
No, but you can have a DataSnap server publish data and server side methods to be used by any JSON aware client, not just ataSnap clients.
Bruce McGee
A: 

You can use the HttpServer example given with Indy or ICS or Snapse (for D2009 get the latest source from trunk and the examples from the previous stable version). Then use Delphi DB components to access the Interbase server and use one of the Json libraries from Torry - http://www.torry.net/pages.php?id=216

Make sure you create a regular application and test the code. After it works convert it to a service using the Delphi service project template.

TwinForms
+4  A: 

Habari does JSON for Delphi. It is commercial, but does the job well.

--jeroen

Jeroen Pluimers
Better: JSON - SuperObject http://www.progdigy.com/?page_id=6
inzKulozik
Habari uses SuperObject. SuperObject only does the conversion between JSON and objects. Habari adds the communication.
Jeroen Pluimers
+2  A: 

You can use UIB for accessing Interbase and JSON superobject. All this is made by the same developper and this is OpenSource. He will publish a new project about what you need soon.

Stay tuned

Hugues Van Landeghem
A: 

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;
General Jackson Tackett
A: 

Our Open Souce Framework is a pure JSON based client/server framework. It can use HTTP as transport layer, and uses either its own embedded database or SQLite3 for data persistency (ORM). It's possible to change the database layer (see how SQLite3.pas unit implements the SQLite3 database engine) into Interbase, if you want to. See http://blog.synopse.info/category/Open-Source-Projects/SQLite3-Framework

A. Bouchez
A: 

For a Windows Service code sample you can have a look at my article @ Delphi3000.com: http://www.delphi3000.com/article.asp?id=3379&amp;SK=service

K.Sandell