I am having a bit of a problem with tables streamed over HTTP - specifically
my issue is with query results.

What I am trying to do is as follows:

On a server application I access data from the database, I store this data
in a memory table and use RealThinClient to stream it to a client over HTTP.
On the client I create a table in memory, load the stream into a
TDBIsamTable that points to the memory table with the same name - and use it
there.

When the source of the data on the server is a table, I use the following
code:

function TVerMgrDataModule.ListProductsTable(
ProductList: TMemoryStream): boolean;
egin
 result := false;
 ProductsDS.Open;
 ProductsDS.CopyTable('MEMORY', 'Products');
 MemDS.Close;
 MemDS.TableName := 'Products';
 MemDS.Open;
 MemDS.SaveToStream(ProductList);
 result := true;
end;

ProductList is then sent over HTTP and on the client I do the following:

I create the database table when my data module is created:

procedure TAccessModule.CreateProductsTable;
begin
  with ProductsDS do begin
     with FieldDefs do begin
        Add('ProductID', ftString, 50, true);
        Add('ProductKey', ftInteger, 0, true);
     end;
     if (not Exists) then
        CreateTable;
  end;
end;

and later, when I receive the data from the HTTP stream I do:

     if (not ProductsDS.Active) then begin
        ProductsDS.Open;
     end else begin
        while (ProductsDS.RecordCount > 0) do begin
           ProductsDS.Delete;
        end; // while
     end;
     ProductsDS.LoadFromStream(Result.asByteStream);

---

This seems to work just fine.

I am trying to do the same with data that is retrieved from a query:

function TVerMgrDataModule.ListMessageTable(const ProductName : String;
 MessageList: TMemoryStream; var ErrMsg: String): boolean;
var
  Query : String;
begin
  result := false;
  Query := 'Select MsgText, MsgType, MsgParm, MsgID, MsgDate, MsgActive,
Expires ' +
           ' from ProductMessages AS PM, Products AS P ' +
           ' where PM.Product = P.ProductKey and p.ProductID = ''' +
ProductName + '''';
  MessagesDS.SQL.Text := Query;
  MessagesDS.Open;
  MessagesDS.SaveToTable('MEMORY', 'Messages');

  MemDS.Close;
  MemDS.TableName := 'Messages';
  MemDS.Open;
  MemDS.SaveToStream(MessageList);
  result := true;

end;

MessageList is now sent over HTTP just like ProductList was sent earlier

On the client I create the table using the same technique:

procedure TAccessModule.CreateMessageTable;
begin
  with MessageDS do begin
     with FieldDefs do begin
        Add('MsgText', ftString, 250, true);
        Add('MsgType', ftInteger, 0, true);
        Add('MsgParm', ftString, 250);
        Add('MsgID', ftInteger, 0, true);
        Add('MsgDate', ftDateTime, 0, true);
        Add('MsgActive', ftBoolean, 0, true);
        Add('Expires', ftDateTime, 0, true);
     end;
     if (not Exists) then
        CreateTable;
  end;
end;


and I load it once I get it from the HTTP stream with:

    if (not MessageDS.Active) then begin
       MessageDS.Open;
    end else begin
       while (MessageDS.RecordCount > 0) do begin
          MessageDS.Delete;
       end; // while
    end;
    MessageDS.LoadFromStream(Result.asByteStream);

For some reason - when I call LoadFromStream on MessageDS I get an exception
11312 - about invalid data value in stream data.

Any idea what the cause might be - it seems like the code is identical with
the exception that I bring it from a query and use SaveToTable instead of
CopyTable (because I use a query instead of a table).

Ron,

<< For some reason - when I call LoadFromStream on MessageDS I get an
exception 11312 - about invalid data value in stream data. >>

Make sure that any string fields in the table that you're loading the stream
into match the size of the string fields in the query result set *exactly*.
Otherwise, you'll get that error.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:38E50C63-277D-48EC-8E24-151258286729@news.elevatesoft.com...
> Ron,
>
> << For some reason - when I call LoadFromStream on MessageDS I get an
> exception 11312 - about invalid data value in stream data. >>
>
> Make sure that any string fields in the table that you're loading the
> stream into match the size of the string fields in the query result set
> *exactly*. Otherwise, you'll get that error.
>

It seems to me that they are:

Here is the schema used for the database -

CREATE TABLE "ProductMessages" (
 "Product" INTEGER NOT NULL,
 "MsgText" VARCHAR(250) NOT NULL,
 "MsgType" INTEGER NOT NULL,
 "MsgParm" VARCHAR(250),
 "MsgID" INTEGER NOT NULL,
 "MsgDate" DATE NOT NULL,
 "MsgActive" BOOLEAN NOT NULL,
 "Expires" DATE NOT NULL,
 PRIMARY KEY ("MsgID")

 DESCRIPTION 'Messages downloaded for the product'
);

Here is the code that creates the memory table on the client:

procedure TAccessModule.CreateMessageTable;
begin
  with MessageDS do begin
     with FieldDefs do begin
        Add('MsgText', ftString, 250, true);
        Add('MsgType', ftInteger, 0, true);
        Add('MsgParm', ftString, 250);
        Add('MsgID', ftInteger, 0, true);
        Add('MsgDate', ftDateTime, 0, true);
        Add('MsgActive', ftBoolean, 0, true);
        Add('Expires', ftDateTime, 0, true);
     end;
     if (not Exists) then
        CreateTable;
  end;
end;


MsgText and MsgParm are 250 characters long in both cases.

Any other ideas? I would hate to go through the process of dumping the
dataset to XML on one side and parsing it and populating it on the other.

You have 8 fields in the CreateTable and only 7 in the second.... you missed
the Key, the First Field....

Is this missing??

     Add('Product', ftInteger, 0, true);

Walter

"Ron L." <info@nospam-pireporting.com> wrote in message
news:92130758-895C-4D67-AAAF-3949F16028D8@news.elevatesoft.com...
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:38E50C63-277D-48EC-8E24-151258286729@news.elevatesoft.com...
>> Ron,
>>
>> << For some reason - when I call LoadFromStream on MessageDS I get an
>> exception 11312 - about invalid data value in stream data. >>
>>
>> Make sure that any string fields in the table that you're loading the
>> stream into match the size of the string fields in the query result set
>> *exactly*. Otherwise, you'll get that error.
>>
>
> It seems to me that they are:
>
> Here is the schema used for the database -
>
> CREATE TABLE "ProductMessages" (
>  "Product" INTEGER NOT NULL,
>  "MsgText" VARCHAR(250) NOT NULL,
>  "MsgType" INTEGER NOT NULL,
>  "MsgParm" VARCHAR(250),
>  "MsgID" INTEGER NOT NULL,
>  "MsgDate" DATE NOT NULL,
>  "MsgActive" BOOLEAN NOT NULL,
>  "Expires" DATE NOT NULL,
>  PRIMARY KEY ("MsgID")
>
>  DESCRIPTION 'Messages downloaded for the product'
> );
>
> Here is the code that creates the memory table on the client:
>
> procedure TAccessModule.CreateMessageTable;
> begin
>   with MessageDS do begin
>      with FieldDefs do begin
>         Add('MsgText', ftString, 250, true);
>         Add('MsgType', ftInteger, 0, true);
>         Add('MsgParm', ftString, 250);
>         Add('MsgID', ftInteger, 0, true);
>         Add('MsgDate', ftDateTime, 0, true);
>         Add('MsgActive', ftBoolean, 0, true);
>         Add('Expires', ftDateTime, 0, true);
>      end;
>      if (not Exists) then
>         CreateTable;
>   end;
> end;
>
>
> MsgText and MsgParm are 250 characters long in both cases.
>
> Any other ideas? I would hate to go through the process of dumping the
> dataset to XML on one side and parsing it and populating it on the other.
>

Sorry not the Key the field 'Product' ....

"Walter Matte" <walter_@_interlog.com> wrote in message
news:77232FE8-B713-460D-93A9-76B32A8C473B@news.elevatesoft.com...
> You have 8 fields in the CreateTable and only 7 in the second.... you
> missed the Key, the First Field....
>
> Is this missing??
>
>      Add('Product', ftInteger, 0, true);
>
> Walter
>
> "Ron L." <info@nospam-pireporting.com> wrote in message
> news:92130758-895C-4D67-AAAF-3949F16028D8@news.elevatesoft.com...
>> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
>> message news:38E50C63-277D-48EC-8E24-151258286729@news.elevatesoft.com...
>>> Ron,
>>>
>>> << For some reason - when I call LoadFromStream on MessageDS I get an
>>> exception 11312 - about invalid data value in stream data. >>
>>>
>>> Make sure that any string fields in the table that you're loading the
>>> stream into match the size of the string fields in the query result set
>>> *exactly*. Otherwise, you'll get that error.
>>>
>>
>> It seems to me that they are:
>>
>> Here is the schema used for the database -
>>
>> CREATE TABLE "ProductMessages" (
>>  "Product" INTEGER NOT NULL,
>>  "MsgText" VARCHAR(250) NOT NULL,
>>  "MsgType" INTEGER NOT NULL,
>>  "MsgParm" VARCHAR(250),
>>  "MsgID" INTEGER NOT NULL,
>>  "MsgDate" DATE NOT NULL,
>>  "MsgActive" BOOLEAN NOT NULL,
>>  "Expires" DATE NOT NULL,
>>  PRIMARY KEY ("MsgID")
>>
>>  DESCRIPTION 'Messages downloaded for the product'
>> );
>>
>> Here is the code that creates the memory table on the client:
>>
>> procedure TAccessModule.CreateMessageTable;
>> begin
>>   with MessageDS do begin
>>      with FieldDefs do begin
>>         Add('MsgText', ftString, 250, true);
>>         Add('MsgType', ftInteger, 0, true);
>>         Add('MsgParm', ftString, 250);
>>         Add('MsgID', ftInteger, 0, true);
>>         Add('MsgDate', ftDateTime, 0, true);
>>         Add('MsgActive', ftBoolean, 0, true);
>>         Add('Expires', ftDateTime, 0, true);
>>      end;
>>      if (not Exists) then
>>         CreateTable;
>>   end;
>> end;
>>
>>
>> MsgText and MsgParm are 250 characters long in both cases.
>>
>> Any other ideas? I would hate to go through the process of dumping the
>> dataset to XML on one side and parsing it and populating it on the other.
>>
>
>

"Walter Matte" <walter_@_interlog.com> wrote in message
news:77232FE8-B713-460D-93A9-76B32A8C473B@news.elevatesoft.com...
> You have 8 fields in the CreateTable and only 7 in the second.... you
> missed the Key, the First Field....
>
> Is this missing??
>
>      Add('Product', ftInteger, 0, true);

I do no think so - since the data that is sent over the wire is the result
of the following SQL Query (it is not sent from a TDBIsamTable that
represents the physical data on disk):

function TVerMgrDataModule.ListMessageTable(const ProductName : String;
 MessageList: TMemoryStream; var ErrMsg: String): boolean;
var
  Query : String;
begin
  result := false;
  Query := 'Select MsgText, MsgType, MsgParm, MsgID, MsgDate, MsgActive,
Expires ' +
           ' from ProductMessages AS PM, Products AS P ' +
           ' where PM.Product = P.ProductKey and p.ProductID = ''' +
ProductName + '''';
  MessagesDS.SQL.Text := Query;
  MessagesDS.Open;
  MessagesDS.SaveToTable('MEMORY', 'Messages');

  MemDS.Close;
  MemDS.TableName := 'Messages';
  MemDS.Open;
  MemDS.SaveToStream(MessageList);
  result := true;
end;

These are the 7 fields I need and that I send - so I still think that there
is something strange happening.

I actually put a break point on the MemDS.SaveToStream line and checked the
fielddefs there to be sure that the correct size is created - and both the
string fields have 250 characters just like the physical database they
originate from.

I still think there is something else that is happening there. If I can not
find it - I will just do the extra roundtrip to XML - but I would really
love to avoid it if possible - it will be faster and more efficient this
way.

Ron.



>
> Walter
>
> "Ron L." <info@nospam-pireporting.com> wrote in message
> news:92130758-895C-4D67-AAAF-3949F16028D8@news.elevatesoft.com...
>> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
>> message news:38E50C63-277D-48EC-8E24-151258286729@news.elevatesoft.com...
>>> Ron,
>>>
>>> << For some reason - when I call LoadFromStream on MessageDS I get an
>>> exception 11312 - about invalid data value in stream data. >>
>>>
>>> Make sure that any string fields in the table that you're loading the
>>> stream into match the size of the string fields in the query result set
>>> *exactly*. Otherwise, you'll get that error.
>>>
>>
>> It seems to me that they are:
>>
>> Here is the schema used for the database -
>>
>> CREATE TABLE "ProductMessages" (
>>  "Product" INTEGER NOT NULL,
>>  "MsgText" VARCHAR(250) NOT NULL,
>>  "MsgType" INTEGER NOT NULL,
>>  "MsgParm" VARCHAR(250),
>>  "MsgID" INTEGER NOT NULL,
>>  "MsgDate" DATE NOT NULL,
>>  "MsgActive" BOOLEAN NOT NULL,
>>  "Expires" DATE NOT NULL,
>>  PRIMARY KEY ("MsgID")
>>
>>  DESCRIPTION 'Messages downloaded for the product'
>> );
>>
>> Here is the code that creates the memory table on the client:
>>
>> procedure TAccessModule.CreateMessageTable;
>> begin
>>   with MessageDS do begin
>>      with FieldDefs do begin
>>         Add('MsgText', ftString, 250, true);
>>         Add('MsgType', ftInteger, 0, true);
>>         Add('MsgParm', ftString, 250);
>>         Add('MsgID', ftInteger, 0, true);
>>         Add('MsgDate', ftDateTime, 0, true);
>>         Add('MsgActive', ftBoolean, 0, true);
>>         Add('Expires', ftDateTime, 0, true);
>>      end;
>>      if (not Exists) then
>>         CreateTable;
>>   end;
>> end;
>>
>>
>> MsgText and MsgParm are 250 characters long in both cases.
>>
>> Any other ideas? I would hate to go through the process of dumping the
>> dataset to XML on one side and parsing it and populating it on the other.
>>
>
>

Hi Ron,

I guess you've already tried testing to load the stream on the server
itself to be sure the stream format from the query is as you expect it
to be. If that's the case, there must be something being corrupted
while streaming over http. Some thoughts:

1. Have you mime encoded the stream before sending it over http? (also
check if the mime encoding you use breaks the lines at about 70 chars)

2. Are you using a proxy server, and if so are you sure there are no
restrictions to the size of the posted data?

3. At the receiving end of the http post - are you sure you're
receiving all the data, and not only the first 48kb data chunk?

HTH,
Robert.


Ron L. wrote:

> I am having a bit of a problem with tables streamed over HTTP -
> specifically my issue is with query results.
>
> What I am trying to do is as follows:
>
> On a server application I access data from the database, I store this
> data in a memory table and use RealThinClient to stream it to a
> client over HTTP. On the client I create a table in memory, load the
> stream into a TDBIsamTable that points to the memory table with the
> same name - and use it there.
>
> When the source of the data on the server is a table, I use the
> following code:
>
> function TVerMgrDataModule.ListProductsTable(
> ProductList: TMemoryStream): boolean;
> egin
>  result := false;
>  ProductsDS.Open;
>  ProductsDS.CopyTable('MEMORY', 'Products');
>  MemDS.Close;
>  MemDS.TableName := 'Products';
>  MemDS.Open;
>  MemDS.SaveToStream(ProductList);
>  result := true;
> end;
>
> ProductList is then sent over HTTP and on the client I do the
> following:
>
> I create the database table when my data module is created:
>
> procedure TAccessModule.CreateProductsTable;
> begin
>   with ProductsDS do begin
>      with FieldDefs do begin
>         Add('ProductID', ftString, 50, true);
>         Add('ProductKey', ftInteger, 0, true);
>      end;
>      if (not Exists) then
>         CreateTable;
>   end;
> end;
>
> and later, when I receive the data from the HTTP stream I do:
>
>      if (not ProductsDS.Active) then begin
>         ProductsDS.Open;
>      end else begin
>         while (ProductsDS.RecordCount > 0) do begin
>            ProductsDS.Delete;
>         end; // while
>      end;
>      ProductsDS.LoadFromStream(Result.asByteStream);
>
> ---
>
> This seems to work just fine.
>
> I am trying to do the same with data that is retrieved from a query:
>
> function TVerMgrDataModule.ListMessageTable(const ProductName :
> String;  MessageList: TMemoryStream; var ErrMsg: String): boolean;
> var
>   Query : String;
> begin
>   result := false;
>  Query := 'Select MsgText, MsgType, MsgParm, MsgID, MsgDate,
> MsgActive, Expires ' +            ' from ProductMessages AS PM,
> Products AS P ' +           ' where PM.Product = P.ProductKey and
> p.ProductID = ''' + ProductName + '''';   MessagesDS.SQL.Text :=
> Query;   MessagesDS.Open;
>   MessagesDS.SaveToTable('MEMORY', 'Messages');
>
>   MemDS.Close;
>   MemDS.TableName := 'Messages';
>   MemDS.Open;
>   MemDS.SaveToStream(MessageList);
>   result := true;
>
> end;
>
> MessageList is now sent over HTTP just like ProductList was sent
> earlier
>
> On the client I create the table using the same technique:
>
> procedure TAccessModule.CreateMessageTable;
> begin
>   with MessageDS do begin
>      with FieldDefs do begin
>         Add('MsgText', ftString, 250, true);
>         Add('MsgType', ftInteger, 0, true);
>         Add('MsgParm', ftString, 250);
>         Add('MsgID', ftInteger, 0, true);
>         Add('MsgDate', ftDateTime, 0, true);
>         Add('MsgActive', ftBoolean, 0, true);
>         Add('Expires', ftDateTime, 0, true);
>      end;
>      if (not Exists) then
>         CreateTable;
>   end;
> end;
>
>
> and I load it once I get it from the HTTP stream with:
>
>     if (not MessageDS.Active) then begin
>        MessageDS.Open;
>     end else begin
>        while (MessageDS.RecordCount > 0) do begin
>           MessageDS.Delete;
>        end; // while
>     end;
>     MessageDS.LoadFromStream(Result.asByteStream);
>
> For some reason - when I call LoadFromStream on MessageDS I get an
> exception 11312 - about invalid data value in stream data.
>
> Any idea what the cause might be - it seems like the code is
> identical with the exception that I bring it from a query and use
> SaveToTable instead of CopyTable (because I use a query instead of a
> table).



--

"Robert Cram" <rcram@knoware.nl> wrote in message
news:xn0esm28s2l1jw7000@news.elevatesoft.com...
> Hi Ron,
>
> 2. Are you using a proxy server, and if so are you sure there are no
> restrictions to the size of the posted data?
>
> 3. At the receiving end of the http post - are you sure you're
> receiving all the data, and not only the first 48kb data chunk?

No proxies and I am pretty sure that I am getting it all just fine (as I
said - if I stream data obtained from a TDBIsamTable it is working just
fine - but I will perform some more tests. Thanks for the pointers - some of
them might be helpful in finding the problem.

Ron.

"Ron L." <info@nospam-pireporting.com> wrote in message
news:9DCB7492-2CEC-4A0D-9736-14D8F03BAB6F@news.elevatesoft.com...
> "Robert Cram" <rcram@knoware.nl> wrote in message
> news:xn0esm28s2l1jw7000@news.elevatesoft.com...
>> 3. At the receiving end of the http post - are you sure you're
>> receiving all the data, and not only the first 48kb data chunk?
>
> No proxies and I am pretty sure that I am getting it all just fine (as I
> said - if I stream data obtained from a TDBIsamTable it is working just
> fine - but I will perform some more tests. Thanks for the pointers - some
> of them might be helpful in finding the problem.
>

I verified it now - I am getting the exact same stream on the client - but
for some reason I can not load it there while I can on the server. This has
to be something so stupid that when I find it I am going to try and hide for
a week...

>
> No proxies and I am pretty sure that I am getting it all just fine (as I
> said - if I stream data obtained from a TDBIsamTable it is working just
> fine - but I will perform some more tests. Thanks for the pointers - some
> of them might be helpful in finding the problem.
>

OK, error found. Apparently the database date fields on the server were
ftDate but on the client ftDateTime...

I always thought that ftDate was just a subset of ftDateTime, apparently it
is not.