3 Replies Latest reply on Apr 7, 2011 1:08 PM by reid abel

    Flash Builder 4 Beta 2 <-> Delphi 2007 web service - best practices?

    reidabel

      Hi - I am trying to use FB4b2 as the front end for a Delphi 2007/Oracle back end, using web services built in Delphi 2007. I have been able to import the delphi sample web service, including the methods: echoEnum, echoDouble, echoDoubleArray, and echoMyEmployee. FB4b2 works properly if I right-click and do "Generate Form" with any of these. So things start out well.

       

      However, I want to be able to use a DataGrid to display (and later, edit) the results of a database query by using a method in the web service. I have found tutorials that show the use of "Configure Return Type". However, NONE of the web service methods I've allow this - the button is ALWAYS disabled. This excellent tutorial in particular, though it uses HTTPService instead of a web service, was what I was trying to follow:

       

      http://flexedup.wordpress.com/2009/10/07/connecting-to-a-static-xml-with-http-service-usin g-the-data-centric-development-dcd-feature-in-flash-builder-4/#comment-16

       

      If I had been able to do "Configure Return Type" for my web service "operation", it would have taken me to the step:

       

      Auto-detect the return type from sample data

       

      ... where I would have been able to get FB4b2 to pull in the result of a call to my web service. It would pull back a string containing XML, say:

       

      <?xml version="1.0" encoding="UTF-8">

      <rows>

      <row><name>A</name><number>1</number></row>

      <row><name>B</name><number>2</number></row>

      </rows>

       

      ... and from there, I think that FB4b2 would set up a return type for the operation. Then, a datagrid could be set up for this return type and it would configure the columns and column headers automatically for me. At least that's what I THINK would have happened.

       

      Any ideas on how to get "Configure Return Type" enabled?

       

      -------------------------------------------------

       

      I then came across another way to get the result of my web service method, coming in as a plain string containing xml as above, into a DataGrid. The tutorial

       

      http://blog.flexexamples.com/2008/03/29/dynamically-loading-xml-files-using-the-httpservic e-tag/

       

      ... showed me how to go about this using an XMLListCollection. While this worked for me, it didn't do anything automatically - I had to set up the columns of the datagrid manually (name and number columns corresponding to the example xml above).

       

      So this is my question - what is the "best practice" way to go about getting query results into a Flash Builder DataGrid? Keep in mind that I also want to be able to allow the user to edit the data at some point.

       

      1. What return type should the Delphi 2007 web service method have? Is String the way to go? Or is it Object? Or array of String?

      2. How do I go about getting Flash Builder to detect what's coming through the web service (wsdl, actually) and automate things for me in terms of setting up the calls and determining the columns?

       

      Thanks for your help, and working code examples would be appreciated.

       

      Reid

        • 1. Re: Flash Builder 4 Beta 2 <-> Delphi 2007 web service - best practices?
          reidabel Level 1

          Okay, no responses to this question, maybe there aren't many people using Delphi to create web services? Anyway, with help from a Delphi forum response I was able to track down an approach that worked. With this approach, I can create a Delphi 2007 web service that FB4b2 can use, creating a Data Type automatically, and, when I "Enable Data Management" with the data type, allows me to put the data into a datagrid and have it be editable. There may be one issue left to solve (regarding the primary key field), but at the moment this is looking decent. So here's an example of a unit that I have built (actually, I wrote a program that code-generated it for me from the definition of an Oracle table - I may have to do quite a few of these tables in the future):

           

          Note that the big trick is creating a class that is a descendent of the TRemotable class, to hold the values of one record, then defining a type that is an array of that class, like:

           

          TCOMMISSIONSFields = class(TRemotable)

          TCOMMISSIONSFieldsArray = array of TCOMMISSIONSFields;

           

          I'm not going to paste the hundreds of lines of code in here, I'll just include the key bits of code so that you can (hopefully) understand this.

           

          --------------------------- the "Intf" unit created by the Delphi 2007 SOAP Server application needs to be modified to look like:

           

          unit Agt_CommissionsIntf;

          interface

          uses InvokeRegistry, Types, XSBuiltIns,
            CommissionsFields;

          type

            { Invokable interfaces must derive from IInvokable }
            IAgt_Commissions = interface(IInvokable)
            ['{B273D9E6-<etc>}']

              { Methods of Invokable interface must not use the default }
              { calling convention; stdcall is recommended }
              function GetCommissions(CustID, StartDate, EndDate: string): TCommissionsFieldsArray; stdcall;

              {CRUD methods to use with Flash Builder 4 beta 2's "Enable Data Management" on the TCOMMISSIONSFields data type that is auto-generates from importing the WSDL}
              function CreateCommission(Item: TCommissionsFields): TCommissionsFields; stdcall;
              function RetrieveCommission(CommissionID: Integer): TCommissionsFields; stdcall;
              function UpdateCommission(Item: TCommissionsFields): TCommissionsFields; stdcall;
              procedure DeleteCommission(CommissionID: Integer); stdcall;
            end;

          implementation

          initialization
            { Invokable interfaces must be registered }
            InvRegistry.RegisterInterface(TypeInfo(IAgt_Commissions));

          end.

           

          -----------------------------the "Impl" unit created by the Delphi 2007 SOAP Server application needs to be modified to look like:

           

          { Invokable implementation File for TAgt_Commissions which implements IAgt_Commissions }

          unit Agt_CommissionsImpl;

          interface

          uses InvokeRegistry, Types, XSBuiltIns, Agt_CommissionsIntf, SysUtils, Classes, SqlExpr,
            Utils_FlashBuilder_Delphi, CommissionsFields;

           

          type

            { TAgt_Commissions }
            TAgt_Commissions = class(TInvokableClass, IAgt_Commissions)
            private
              SQL: string;
              DM: TDataModule;
              Con: TSQLConnection;
              Qry: TSQLQuery;

              function ConnectToDatabase(EnvironmentToUse: string): Boolean;
            public
              {Method to retrieve sets of records}
              function GetCommissions(CustID, StartDate, EndDate: string): TCommissionsFieldsArray; stdcall;

              {CRUD methods to use with Flash Builder 4 beta 2}
              function CreateCommission(Item: TCommissionsFields): TCommissionsFields; stdcall;
              function RetrieveCommission(CommissionID: Integer): TCommissionsFields; stdcall;
              function UpdateCommission(Item: TCommissionsFields): TCommissionsFields; stdcall;
              procedure DeleteCommission(CommissionID: Integer); stdcall;

           

              procedure AfterConstruction; override;
              procedure BeforeDestruction; override;
          end;

           

          procedure TAgt_Commissions.AfterConstruction;
          begin
            inherited;

            if ConnectToDatabase(Dev) then
            begin
              {no way to return success or failure, just try to connect}
            end;
          end;

          procedure TAgt_Commissions.BeforeDestruction;
          begin
            inherited;

            {Do NOT free the DM or Con - it leads to errors}
          end;

           

          ------------ Here's how to put your query's returned dataset into an array so that Flash Builder 4 Beta 2 can load it.

           

          function TAgt_Commissions.GetCommissions(CustID, StartDate, EndDate: string): TCommissionsFieldsArray;

          var
            Commission: TCommissionsFields;
          begin

            cnt := -1;

           

            Qry := TSQLQuery.Create(nil);

            try
              Qry.SQLConnection := Con;
              Qry.SQL.Clear;
              Qry.SQL.Add(SQL); //make SQL contain a query that uses the input parameters for your own purposes

              try
                Qry.Open;

                while not Qry.Eof do
                begin
                    cnt := cnt + 1;
                    SetLength(Result, cnt + 1);
                    Commission := TCommissionsFields.Create;
                    Commission.LoadFields(Qry); //does things like: COMMISSIONID := Qry.FieldByName('COMMISSIONID').AsInteger;

           

                    Result[cnt] := Commission;

           

                    Qry.Next;
                end;
              except
                on E: Exception do
                  Commission.<some string field> := Commission.<some string field>+ 'Qry error: ' + E.Message + ' ';
              end;
            finally
              Qry.Free;
            end;

           

           

          ----------- The "object layer" unit that relates to the Oracle database table, objects of this class will be able to contain the data from one database table record.

           

          unit COMMISSIONSFields;

          interface

          uses
            InvokeRegistry, SqlExpr;

          type
            TCOMMISSIONSFields = class(TRemotable)
              private
                DateHolder1, DateHolder2, DateHolder3: string; //there are some tricky date things to handle, these are used to contains date strings

                procedure SetCOMMISSIONID(const Value: Integer);
                procedure SetTRANSID(const Value: Integer);
                procedure SetTAXID(const Value: Integer);
                procedure SetCUSTID(const Value: string);
                procedure SetAMOUNT(const Value: Double);
                procedure SetPREMPAID(const Value: Double);
                procedure SetCOMMPERIOD(const Value: TDateTime);
                procedure SetPAIDDATE(const Value: TDateTime);
                procedure SetREASON(const Value: string);
                procedure SetPOSTED_OLD(const Value: string);
                procedure SetCOMMISSIONDATE_OLD(const Value: TDateTime);
                procedure SetINVOICENUM(const Value: string);

           

                function GetNewID(Con: TSQLConnection): Integer; //for dealing with Oracle sequences
              protected
                FCOMMISSIONID: Integer;
                FTRANSID: Integer;
                FTAXID: Integer;
                FCUSTID: string;
                FAMOUNT: Double;
                FPREMPAID: Double;
                FCOMMPERIOD: TDateTime;
                FPAIDDATE: TDateTime;
                FREASON: string;
                FPOSTED_OLD: string;
                FCOMMISSIONDATE_OLD: TDateTime;
                FINVOICENUM: string;
              public
                constructor Create; override;

                procedure Assign(aCOMMISSIONSFields: TCOMMISSIONSFields);
                procedure LoadFields(Qry: TSQLQuery);
                procedure SaveToDatabase(Connection: TSQLConnection);
                procedure DeleteFromDatabase(Connection: TSQLConnection);
                procedure Update(Connection: TSQLConnection);

                function GetCreateSQL: string;
                function GetUpdateSQL: string;
                function GetDeleteSQL: string;
              published
                property COMMISSIONID: Integer read FCOMMISSIONID write SetCOMMISSIONID;
                property TRANSID: Integer read FTRANSID write SetTRANSID;
                property TAXID: Integer read FTAXID write SetTAXID;
                property CUSTID: string read FCUSTID write SetCUSTID;
                property AMOUNT: Double read FAMOUNT write SetAMOUNT;
                property PREMPAID: Double read FPREMPAID write SetPREMPAID;
                property COMMPERIOD: TDateTime read FCOMMPERIOD write SetCOMMPERIOD;
                property PAIDDATE: TDateTime read FPAIDDATE write SetPAIDDATE;
                property REASON: string read FREASON write SetREASON;
                property POSTED_OLD: string read FPOSTED_OLD write SetPOSTED_OLD;
                property COMMISSIONDATE_OLD: TDateTime read FCOMMISSIONDATE_OLD write SetCOMMISSIONDATE_OLD;
                property INVOICENUM: string read FINVOICENUM write SetINVOICENUM;
            end;

          {Note: this can be here only AFTER TCOMMISSIONSFields is defined above. Note that putting
          these two lines in Intf and Impl individually caused a misleading compiler
          error about a function declaration differing between intf and impl when it was
          identical}
          type
            TCOMMISSIONSFieldsArray = array of TCOMMISSIONSFields;

           

          ------------------------------------------------------------------------------------------ -----

           

          Okay, I'm leaving out the hundreds of lines of implementation code, but it should be straightforward programming if you've done Delphi database programming before: dbExpress connections and query objects, retrieving and saving to a database, creating SQL statements, etc.

           

          I hope this helps somebody - it would have helped me out a LOT!

           

          Reid

          • 2. Re: Flash Builder 4 Beta 2 <-> Delphi 2007 web service - best practices?
            Antonio Marcelo

            Hello raidabel
            Forgive me for asking but I'm beginner
            How do you send your TCommissionsFields into the function
            CreateCommission function (Item: TCommissionsFields): TCommissionsFields; stdcall;
            by flex

            • 3. Re: Flash Builder 4 Beta 2 <-> Delphi 2007 web service - best practices?
              reid abel

              I'm not quite sure what you mean, and it has been a while, but I dug this out from the code I have:

               

              function TAgtCom.CreateCommission(Item: TCommissionFields): TCommissionFields;
              begin
                Result := TCommissionFields.Create;
                Result.Assign(Item);

               

                {Note that SaveToDatabase will create a new COMMISSIONID from the sequence}
                Result.SaveToDatabase(Con);
              end;

               

              This creates and returns an object of type TCommissionFields, with field values copied from an object of the same type. I don't remember why I did it like this, but to use this function, you would create an object of the TCommissionFields type and set its properties.

               

              var

                Junk: TCommissionFields;

              begin

                Junk := TCommissionFields.Create();

                Junk.CustID := '<some customer id>';

                Junk.Amount := 5.99;

                Junk.PaidDate := <some datetime>;

               

              I don't know if that answers your question, but probably not. Here is some more information about the class. I've trimmed it down to show an integer, a string, a double, and a tdatetime.

               

              type
                TCommissionFields = class(TRemotable)
                  private
                    procedure SetCOMMISSIONID(const Value: Integer);
                    procedure SetCUSTID(const Value: string);
                    procedure SetAMOUNT(const Value: Double);
                    procedure SetPAIDDATE(const Value: TDateTime);
                  protected
                    FCOMMISSIONID: Integer;
                    FCUSTID: string;
                    FAMOUNT: Double;
                    FPAIDDATE: TDateTime;
                  public
                    constructor Create; override;

                    procedure Assign(aCommissionFields: TCommissionFields);
                    procedure LoadFields(Qry: TSQLQuery);
                    procedure SaveToDatabase(Connection: TSQLConnection; CreateNewID: Boolean = True);
                    procedure DeleteFromDatabase(Connection: TSQLConnection);
                    procedure Update(Connection: TSQLConnection);

                    function GetCreateSQL: string;
                    function GetUpdateSQL: string;
                    function GetDeleteSQL: string;
                    function GetCommissionSQL(CustID, TaxID: string): string;
                    function GetPageSQL(StartIndex, NumberOfRecords: Integer): string;
                  published
                    property COMMISSIONID: Integer read FCOMMISSIONID write SetCOMMISSIONID;
                    property CUSTID: string read FCUSTID write SetCUSTID;
                    property AMOUNT: Double read FAMOUNT write SetAMOUNT;
                    property PAIDDATE: TDateTime read FPAIDDATE write SetPAIDDATE;
                end;

               

              Here is the Assign function:

               

              procedure TCommissionFields.Assign(aCommissionFields: TCommissionFields);
              begin
                if Assigned(aCommissionFields) then
                begin
                  COMMISSIONID := aCommissionFields.COMMISSIONID;
                  CUSTID := aCommissionFields.CUSTID;
                  AMOUNT := aCommissionFields.AMOUNT;
                  PAIDDATE := aCommissionFields.PAIDDATE;
                end;
              end;

               

              I hope this puts you onto the right track, but if you have more questions, let me know.

               

              Reid