Hi

Im developing a synchronizing apllication for a accounting system. file transfer is ready , but there are some files that using  blob fields to save someting data,. I need Synchronize this data in two locations, but by example the sfixed file contain record than storing data for customer, suppliers and products but in one location update records for products,customers and accounts receivable in another updates records for suppliers and accounts payables .

I have 3 questions:

1.Theres some way to save data in blob fields only for a selected set of data (customer, products etc)

2 This information saved is possible loading o restoring in the other location but affecting only the blob data for the
records than have the same primary key.

3 Is posible combine information from one location with information in another on the blob fields.

Thankyou in advance

Regards

Juan Carlos Carta

On 8/24/2013 9:24 AM, jccarta wrote:
> 1.Theres some way to save data in blob fields only for a selected set of data (customer, products etc)

Check out SavetoStream/LoadFromStream. In general you can do a query
that only includes selected fields and then save that to stream and you
should be able to loadfromstream on the other side. However see next
answer as well.


> 2 This information saved is possible loading o restoring in the other location but affecting only the blob data for the
> records than have the same primary key.

DBISAM does not have replication so no it will not match your primary
key and update fields. EDB has this built-in (replication).
For DBISAM I would suggest you loadfromstream into temporary table or
query  and then write a routine to merge the changes with the main table
(all you're doing is just looping thru and saving changed blob fields to
another table).

The other option is that if both location are always online you can just
open a remote sessions to another location and local sessions for local
data and run a merge routine directly (without having to use teh streams).

> 3 Is posible combine information from one location with information in another on the blob fields.

How do you define combine? it depends on what you're storing - DBISAM
treats blobs as just binary data.

If you use a merge routine like in previous point then you could for
example append new data to the blob fields but again this would only
make sense if the data you store is useable this way in your app.

Raul

jccarta


General point: in DBISAM the blob fields are easily accessible using navigational methods and .AsString I used this approach to store images, documents and everything. It may not be recommended but it works, and its often easier then trying to do it with SQL

>1.Theres some way to save data in blob fields only for a selected set of data (customer, products etc)

Yes, as Raul says using SQL and LoadToStream/SaveToStream but I'd like to understand in a bit more detail just what it is you want to store in the blobs before commenting. Raul's answer applies to stuffing an entire table / query result set into a blob and that may not be what you want. It is good for transport since you only have one object to move but you may be better with XML, structured stringlist or mime.

>2 This information saved is possible loading o restoring in the other location but affecting only the blob data for the
>records than have the same primary key.

Yes but only as with any other form of table manipulation. Its not built in so you have to manage it. If you transfer an entire table.query result set you can reconstitute that at the other site into a table and then use an SQL UPDATE statement if appropriate.

>3 Is posible combine information from one location with information in another on the blob fields.

Not in that form. The only realistic way would be to reconstitute both as tables, merge the data and then stuff back into a blob.

Whilst I doubt any of us will be willing to write the app for you if you can give us some more info (eg table structures, data to be transfered, frequency of transfer) we should be able to help more.

Roy Lambert [Team Elevate]

Roy

THis sotware is synchronizing software for  an accounting application in my country.

I succesful use savetostream loadfromstream to add information to tables but in the other files i need

than information in the record that is identified by the primary key abc of  file  X in location A its the same that information

in the record that is identified by the primary key abc of  file  X in location B. With normal fields its easy with update sql but

with blob fields I can still do it using update? o or should I use another method?

jccarta

Roy Lambert wrote:



jccarta


General point: in DBISAM the blob fields are easily accessible using navigational methods and .AsString I used this approach to store images, documents and everything. It may not be recommended but it works, and its often easier then trying to do it with SQL

>1.Theres some way to save data in blob fields only for a selected set of data (customer, products etc)

Yes, as Raul says using SQL and LoadToStream/SaveToStream but I'd like to understand in a bit more detail just what it is you want to store in the blobs before commenting. Raul's answer applies to stuffing an entire table / query result set into a blob and that may not be what you want. It is good for transport since you only have one object to move but you may be better with XML, structured stringlist or mime.

>2 This information saved is possible loading o restoring in the other location but affecting only the blob data for the
>records than have the same primary key.

Yes but only as with any other form of table manipulation. Its not built in so you have to manage it. If you transfer an entire table.query result set you can reconstitute that at the other site into a table and then use an SQL UPDATE statement if appropriate.

>3 Is posible combine information from one location with information in another on the blob fields.

Not in that form. The only realistic way would be to reconstitute both as tables, merge the data and then stuff back into a blob.

Whilst I doubt any of us will be willing to write the app for you if you can give us some more info (eg table structures, data to be transfered, frequency of transfer) we should be able to help more.

Roy Lambert [Team Elevate]

jccarta


Unfortunately that doesn't really give me anything more that you'd already posted.

You may find this section in the manual useful:

2.35 Loading and Saving Streams with Tables and Query Result Sets

From what you've posted so far I think you'll need to save the data using whatever method you want, transfer to the new site, transfer that data into a new table and then use that to carry out whatever operation it is you want. At this point in time I don't know if you're talking about updating or overwriting  account master records, transactions, multi-book postings or what.

If there isn't to much data I'd be very tempted to use TDBISAMQuery.SaveToTable and transfer the resulting table files.

Roy Lambert [Team Elevate]

ok I'll try to explain better.
There is a table containing information of the products and some of that information is in a blob field. But I think with a detailed example can be understood better to see if you can help me .I have table sfixed which has a field fx_costos blob that is where they are stored prices and product costs fx_codigohas  the following value = '02 ' and the record type is '1' indicating that a product is stored in the fx_tipo field = '1 'in the blob field fx_costos has recorded the price = 3500 in the location "A" the first time you synchronize creating the record at location "B" and everything is ok, match prices. Then modify the price at location "A" to 4500. What is the way that in the next syincronizacion the price for the product identified as fx_tipo = '1 'fx_codigo = '02' in the location "B" changes to 4500. It is simply an Update-SQL or another procedure must be used to achieve this?, since the prices are stored in a blob field. Ultimately what I want to achieve is that in both locations the blob field content is the same each type, you cannot make this copying a table on the other because the location "A" records are modified type "1" and "2"
and location "B" records re modified type "A" "B" and "C". have to do the update basing on the primary key of each record
which is is fx_tipo+fx_codigo

jccarta

Roy Lambert wrote:

jccarta


Unfortunately that doesn't really give me anything more that you'd already posted.

You may find this section in the manual useful:

2.35 Loading and Saving Streams with Tables and Query Result Sets

From what you've posted so far I think you'll need to save the data using whatever method you want, transfer to the new site, transfer that data into a new table and then use that to carry out whatever operation it is you want. At this point in time I don't know if you're talking about updating or overwriting  account master records, transactions, multi-book postings or what.

If there isn't to much data I'd be very tempted to use TDBISAMQuery.SaveToTable and transfer the resulting table files.

Roy Lambert [Team Elevate]

ok I'll try to explain better.
There is a table containing information of the products and some of that information is in a blob field. But I think with a detailed example can be understood better to see if you can help me .I have table sfixed which has a field fx_costos blob that is where they are stored prices and product costs fx_codigohas  the following value = '02 ' and the record type is '1' indicating that a product is stored in the fx_tipo field = '1 'in the blob field fx_costos has recorded the price = 3500 in the location "A" the first time you synchronize creating the record at location "B" and everything is ok, match prices. Then modify the price at location "A" to 4500. What is the way that in the next syincronizacion the price for the product identified as fx_tipo = '1 'fx_codigo = '02' in the location "B" changes to 4500. It is simply an Update-SQL or another procedure must be used to achieve this?, since the prices are stored in a blob field. Ultimately what I want to achieve is that in both locations the blob field content is the same each type, you cannot make this copying a table on the other because the location "A" records are modified type "1" and "2"
and location "B" records re modified type "A" "B" and "C". have to do the update basing on the primary key of each record
which is is fx_tipo+fx_codigo

jccarta

Roy Lambert wrote:

jccarta


Unfortunately that doesn't really give me anything more that you'd already posted.

You may find this section in the manual useful:

2.35 Loading and Saving Streams with Tables and Query Result Sets

From what you've posted so far I think you'll need to save the data using whatever method you want, transfer to the new site, transfer that data into a new table and then use that to carry out whatever operation it is you want. At this point in time I don't know if you're talking about updating or overwriting  account master records, transactions, multi-book postings or what.

If there isn't to much data I'd be very tempted to use TDBISAMQuery.SaveToTable and transfer the resulting table files.

Roy Lambert [Team Elevate]

jccarta


I'm still having trouble following you. Let's see if my understanding is anything like correct:

You have three sites A, B, C

There is a table sfixed which has a blob column fx_costos in which are stored price & cost information

This is where I get lost.

Is the primary key for sfixed fx_tipo+fx_codigo? If so does that mean there is one row for each product and that fx_costos is unique for each product.

Are the keys at sites A,B,C the same?

What is actually stored in fx_costos? Is it a table, is it csv data or what? If its a table what is its structure? Is the information in fx_costos from site A usable at site B without any alteration?

This <<you cannot make this copying a table on the other because the location "A" records are modified type "1" and "2" and location "B" records re modified type "A" "B" and "C">> confuses me. If my guess is right then you can't simply move the data but will need to transform it. If that's the case SQL can be used to extract the records, SaveToStream / transfer / LoadToStream used to create a table at site B or C and then you'll have to navigate through that table extracting and altering the content of fx_costos and you might as well put the data into a local table at the same time rather than back into the transfer table and then use an UPDATE query.

Roy Lambert [Team Elevate]

Roy

There are two sites A ,B.

This is the information stored in fx_costos

:  RegCostos
                      RegUnPrecio         =  Record
                                    PorcUtil            ,
                                    PorcUtilEx         :  Boolean;
                                    Utilidad             ,
                                    UtilidadEx         ,
                                    SinImpuesto     ,
                                    MtoImpuesto1 ,
                                    MtoImpuesto2 ,
                                    TotalPrecio       ,
                                    TotalPrecioEx   :  Currency;
                                    TipoRound        :  Byte;
                                     end;

           RegDePrecios       =  Array[1..6] of RegUnPrecio;

          RegCostos             = Record
                                 CodeCompra       :  String[50];   
                                 VImpuesto1         ,
                              VImpuesto2         : Boolean;  //Impuestos Activados
                                 CostoAnteriorBs ,
                                 CostoAnteriorEx  ,
                                 CostoActualBs      ,
                                 CostoActualEx       ,
                                 CostoPromedioBs ,
                                 CostoPromedioEx ,
                                 MImpuesto1          ,
                                 MImpuesto2          : Currency;
                                 PorcentImp1           ,
                                 Exento1                    ,
                                 PorcentImp2           ,
                                 Excnto2                 : Boolean;
                                 FechaVencimiento : TDateTime;
                                 NumeroDeLote        : String[42];
                                 CostoReferencia      : Double;      
                                 Precios                       : RegDePrecios;
                                         end;

The primary Key for sfixed is Fx_tipo+fxcodigo

In sfixed are records of type:

SFixed
           Fx_Tipo
               '1'  : Departments
               '2'  : Seller Geografical Zones
               '3'  : Warehouse
               '4'  : Currencys
               '5'  : Seller
               '6'  : Banks
               '7'  : Check Receivers
               '8'  : Check descriptions
               '9'  : Customers
               'A'  : Providers
               'B'  : Products
               'C'  : Dredit Cards

Fx_codigo is a unique key that with Fx_tipo is the primary key for each tipe of registers.

In location A is point of sale in location b is taxes and account payables.

The company need have the same informetion from sales in A stored in B for management purposes but without affecting the information in B in the other record for other tipes

The question is :

Can i replace the information in blob field fx_costos in sfixed file without affectin the informacion stored in blob fileld fx_stadisticas in the same sfixed file for other record types.

I Can achieve this using only updtate Sql? assuming of course that I transferred a copy of sfixed file on the site "A" to a file on the site "B" tmp_sfixed .




Roy Lambert wrote:

jccarta


I'm still having trouble following you. Let's see if my understanding is anything like correct:

You have three sites A, B, C

There is a table sfixed which has a blob column fx_costos in which are stored price & cost information

This is where I get lost.

Is the primary key for sfixed fx_tipo+fx_codigo? If so does that mean there is one row for each product and that fx_costos is unique for each product.

Are the keys at sites A,B,C the same?

What is actually stored in fx_costos? Is it a table, is it csv data or what? If its a table what is its structure? Is the information in fx_costos from site A usable at site B without any alteration?

This <<you cannot make this copying a table on the other because the location "A" records are modified type "1" and "2" and location "B" records re modified type "A" "B" and "C">> confuses me. If my guess is right then you can't simply move the data but will need to transform it. If that's the case SQL can be used to extract the records, SaveToStream / transfer / LoadToStream used to create a table at site B or C and then you'll have to navigate through that table extracting and altering the content of fx_costos and you might as well put the data into a local table at the same time rather than back into the transfer table and then use an UPDATE query.

Roy Lambert [Team Elevate]

Juan,

<< 1.Theres some way to save data in blob fields only for a selected set of
data (customer, products etc)  >>

Sure, as long as the "type of data" is in an actual DBISAM field, and not
part of the BLOB data itself.  If the field is part of the BLOB data itself,
then you'll need to add a custom engine function to DBISAM in order to be
able to query this information from DBISAM SQL.

<< 2 This information saved is possible loading o restoring in the other
location but affecting only the blob data for the records than have the same
primary key. >>

Same answer as above.

<< 3 Is posible combine information from one location with information in
another on the blob fields. >>

Yes, but you'll definitely need a custom engine function in order to do this
via SQL.

In general, any time you've got application-specific, binary data stored in
BLOB fields, and you need to do special process on them from SQL, you're
going to need to create custom engine functions that allow you to
read/manipulate such binary data.  It may be simply easier to skip using SQL
and just do navigational processing in code to do what you need.

What you can do on the source end is:

1) Create an in-memory table with the desired structure necessary to
transfer the data.
2) Navigate the source table, copying over the records/data that satisfy
your criteria to the in-memory table.
3) Save the in-memory table to a stream using SaveToStream

On the other end, do this:

1) Create an in-memory table with the same structure as the in-memory table
above.
2) Load the data for the table from a stream using LoadFromStream.
3) Navigate the in-memory table, copying over the records/data that match up
with the target data based upon the primary key, etc.

Tim Young
Elevate Software
www.elevatesoft.com