Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Advice Needed, import v3 table to v4
Fri, Feb 5 2010 1:33 PMPermanent Link

"John Taylor"
Hi All,

I need a bit of advice...

I have a dbisam version 3.30 table that I need to upgrade and then
import into a version 4 table.

The version 3 table's structure is slightly different, in that it has
1 blob field which is actually a zipped tiff file, that is the tiff file
is zipped and then the zip file was stored in the table to the blob field.

In the version 4 table, this field is defined with compression so there
is no zipping done, it is just stored directly to the blob field.

What I want to do is...

1.  Copy the v3 table's .dat, .idx and .blb files to a working directory
2.  Use sql in dbisam 4 to 'upgrade table oldtablename'

No problem so far

I want to now use sql to...

INSERT INTO newv4tablename (field1,field2, field3)

SELECT (field1, field2, field3) FROM oldv3tablename

but blob field (say it is field3) needs to be unzipped before
storing to the new v4 table.

Is this possible using SQL or am I going to have to do the import
by iterating through the table to unzip the blob field before storing
to the version 4 table ?  Seems that would be very slow as the
table might have 10's of thousands of records.

What would be the mose efficient way to do this ?

Thanks
John Taylor
Fri, Feb 5 2010 1:43 PMPermanent Link

"Eduardo [HPro]"
John

> The version 3 table's structure is slightly different, in that it has
> 1 blob field which is actually a zipped tiff file, that is the tiff file
> is zipped and then the zip file was stored in the table to the blob field.
> In the version 4 table, this field is defined with compression so there
> is no zipping done, it is just stored directly to the blob field.

> Is this possible using SQL or am I going to have to do the import
> by iterating through the table to unzip the blob field before storing
> to the version 4 table ?  Seems that would be very slow as the
> table might have 10's of thousands of records.
What method have you used to zip the field ?

There is no way to do this using SQL.
You have to unzip the blob contents using version 3 and then use import
methods from version 4.
While the processing the import DBISAM will compress the blob field
according with compression level defined for the field.

Eduardo


Fri, Feb 5 2010 2:01 PMPermanent Link

"John Taylor"
Eduardo,

After I run the sql 'upgrade table oldtablename' I have
two version 4 tables, the only issue is that the source
table I'll be using for importing to the newly structured
table will be a zipped tiff file that needs to be unzipped
before storing to the new table's blob field.

I was hoping that the Engine component's
OnCustomFunction event handler might somehow be used to
return the unzipped tiff file (?)

Also, can Params be used somehow with

Insert into newtablename (field1, field2,field3)
select from oldtablename (field1,field2,field3)
in any way ?

Thank you for your reply.

John


"Eduardo [HPro]" <contato@hpro.com.br> wrote in message
news:83A5096B-4695-421B-B915-EE78B70BA77B@news.elevatesoft.com...
> John
>
>> The version 3 table's structure is slightly different, in that it has
>> 1 blob field which is actually a zipped tiff file, that is the tiff file
>> is zipped and then the zip file was stored in the table to the blob
>> field.
>> In the version 4 table, this field is defined with compression so there
>> is no zipping done, it is just stored directly to the blob field.
>
>> Is this possible using SQL or am I going to have to do the import
>> by iterating through the table to unzip the blob field before storing
>> to the version 4 table ?  Seems that would be very slow as the
>> table might have 10's of thousands of records.
> What method have you used to zip the field ?
>
> There is no way to do this using SQL.
> You have to unzip the blob contents using version 3 and then use import
> methods from version 4.
> While the processing the import DBISAM will compress the blob field
> according with compression level defined for the field.
>
> Eduardo
>
>
>
Mon, Feb 8 2010 8:39 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

John,

<< I was hoping that the Engine component's OnCustomFunction event handler
might somehow be used to
return the unzipped tiff file (?) >>

It could, but unfortunately DBISAM's SQL does not include support for binary
BLOB constants using hex notation, so there's no way to pass it in an INSERT
statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Feb 8 2010 9:59 AMPermanent Link

"John Taylor"
Thanks Tim, I'll do it the 'old fashioned way'  Smile

John


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:EC48BEF5-9163-409D-B657-B8FAF323186A@news.elevatesoft.com...
> John,
>
> << I was hoping that the Engine component's OnCustomFunction event handler
> might somehow be used to
> return the unzipped tiff file (?) >>
>
> It could, but unfortunately DBISAM's SQL does not include support for
> binary BLOB constants using hex notation, so there's no way to pass it in
> an INSERT statement.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
Image