Omnis Datafile to PostgreSQL
Nick Renders
omnis1 at arcict.com
Wed Dec 15 07:35:43 EST 2010
Thanks for tips, guys!
Since Omnis doesn't handle quotes well when doing an export, I
decided to just loop through the data and insert it into the
PostgreSQL database. It turns out the INSERT statement with Bind
Variables is much faster than using the standard Omnis $insert() on a
Row variable. The 3 million records were transfered in just over 100
minutes.
David, out of curiosity, why did you first read the Omnis data into a
list? Did you use OmniSQL or DML?
I just use the "Next" command to loop through all the records, but I
was wondering if I could make it even faster by using a list.
Nick
On 10 Dec 2010, at 16:20, David McKeone wrote:
> COPY is certainly the fastest way, but you may run into problems if
> you store any binary data or Omnis specific data types (lists,
> objects, pictures, etc...)
>
> The most reliable way we found to do the copy is to write code to
> read it out of the omnis data file into a list in chunks(we did 200
> records at a time) and then insert those into the postgresql
> database via table/schema classes. That way Omnis is still the
> mediator between the two and you can throw breakpoints in just in
> case something doesn't go across nicely.
>
> __________________________________
> David McKeone
> Arts Management Systems Ltd.
> mailto:david at artsman.com
> http://www.artsman.com
> Phone: (403) 536-1203 Fax: (403) 536-1210
>
>
>
>
> On 2010-12-10, at 4:08 PM, Alex Clay wrote:
>
>> Your best bet will be to export the data from the datafile into
>> separate files, one per table. Then use the COPY feature in
>> postgresql to import the data.
>>
>> http://www.postgresql.org/docs/9.0/interactive/sql-copy.html
>>
>> Alex
>>
>> On Dec 10, 2010, at 10:02 AM, Nick Renders wrote:
>>
>>> Hi List,
>>>
>>> I was wondering if anyone had some tips about exporting data from
>>> an Omnis Datafile into a PostgreSQL database?
>>>
>>> I used drag&drop in the Omnis Studio SQL browser for most tables,
>>> but there is one table with over 3 million records.
>>> After 3 days, the transfer was still only at 20%. I am hoping
>>> there is a faster way.
>>>
>>>
>>> Regards,
>>>
>>> Nick Renders
>>>
>>> ARC - your ICT service partner
>>> H. D. Saviolaan 8
>>> 1700 Dilbeek
>>> T: (00 32) (0)2 466 50 00
>>> F: (00 32) (0)2 466 88 33
>>> http://www.arcict.com
>>>
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
More information about the omnisdev-en
mailing list