Convert 4D data MSSQL

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Convert 4D data MSSQL

4D Tech mailing list
Does anyone have any advice on converting a 4D Database to MSSQL.  The database is quite large at 75+gb of data and doing the conversion with an SQL Login and looping the 4D records using Inserts into MSSQL is taking quite literally days to convert.

Any ideas are welcome.

Thanks
Justin Will
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
You might try to use arrays to update and not individual records

Regards

Chuck
------------------------------------------------------------------------------------------------
 Chuck Miller Voice: (617) 739-0306
 Informed Solutions, Inc. Fax: (617) 232-1064      
 mailto:cjmiller<AT SIGN>informed-solutions.com
 Brookline, MA 02446 USA Registered 4D Developer                
       Providers of 4D and Sybase connectivity
          http://www.informed-solutions.com 
------------------------------------------------------------------------------------------------
This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law.  These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited.  Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication.

> On Sep 7, 2017, at 11:23 AM, Justin Will via 4D_Tech <[hidden email]> wrote:
>
> Does anyone have any advice on converting a 4D Database to MSSQL.  The database is quite large at 75+gb of data and doing the conversion with an SQL Login and looping the 4D records using Inserts into MSSQL is taking quite literally days to convert.
>
> Any ideas are welcome.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
Maybe you can write 4D code to create blocks of insert statements in a text file like

LOCK TABLES `CatalogPricing` WRITE;

INSERT INTO `CatalogPricing` (`catPriceID`, `catPriceCatalogID`, `catPriceQty`, `catPriceAmount`, `catPriceStatus`)
VALUES
        (2,2,1,15.00,'1'),
        (7,3,1,15.00,'1'),
        (9,4,1,14.95,'1'),
        (12,6,1,14.95,'1'),
        (14,8,1,14.95,'1'),
        (17,7,1,14.95,'1'),
        (19,9,1,14.95,'1'),
        (20,10,1,14.95,'1'),
        (21,5,1,14.95,'1'),
        (22,11,1,14.95,'1'),
        (23,12,1,14.95,'1'),
        (24,13,1,14.95,'1'),
        (25,14,1,14.95,'1'),
        (26,15,1,14.95,'1'),
        (27,16,1,14.95,'1');

UNLOCK TABLES;

Then just import/run the SQL files in MSSQL.

There are some data types you might have to deal with in other ways, but this might handle most of what you need.

Randy

----------------------------------------------------------------------
Randy Jaynes
Senior Programmer and Customer Support

http://printpoint.com <http://printpoint.com/> • 845.687.3741 • PrintPoint, Inc • 57 Ludlow Lane • Palisades, NY 10964



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
I think this is what SQL EXPORT DATABASE and SQL EXPORT SELECTION do.  You might have some issues with date/time fields as well as object fields.

--
Jeffrey Kain
[hidden email]




> On Sep 7, 2017, at 11:40 AM, Randy Jaynes via 4D_Tech <[hidden email]> wrote:
>
> Maybe you can write 4D code to create blocks of insert statements in a text file like
>
> LOCK TABLES `CatalogPricing` WRITE;
>
> INSERT INTO `CatalogPricing` (`catPriceID`, `catPriceCatalogID`, `catPriceQty`, `catPriceAmount`, `catPriceStatus`)
> VALUES
> (2,2,1,15.00,'1'),
> (7,3,1,15.00,'1'),
> (9,4,1,14.95,'1'),
> (12,6,1,14.95,'1'),
> (14,8,1,14.95,'1'),
> [...]
> UNLOCK TABLES;
>
> Then just import/run the SQL files in MSSQL.
>
> There are some data types you might have to deal with in other ways, but this might handle most of what you need.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
Sure looks like it!

I wasn’t even aware of this command in 4D, but it calls it a ‘dump’, so that’s the kind of text file I would expect this to create.

Randy

----------------------------------------------------------------------
Randy Jaynes
Senior Programmer and Customer Support

http://printpoint.com <http://printpoint.com/> • 845.687.3741 • PrintPoint, Inc • 57 Ludlow Lane • Palisades, NY 10964





> On Sep 7, 2017, at 11:42 AM, Jeffrey Kain via 4D_Tech <[hidden email] <mailto:[hidden email]>> wrote:
>
> I think this is what SQL EXPORT DATABASE and SQL EXPORT SELECTION do.  You might have some issues with date/time fields as well as object fields.
>
> --
> Jeffrey Kain
> [hidden email] <mailto:[hidden email]>
>
>
>
>
>> On Sep 7, 2017, at 11:40 AM, Randy Jaynes via 4D_Tech <[hidden email]> wrote:
>>
>> Maybe you can write 4D code to create blocks of insert statements in a text file like
>>
>> LOCK TABLES `CatalogPricing` WRITE;
>>
>> INSERT INTO `CatalogPricing` (`catPriceID`, `catPriceCatalogID`, `catPriceQty`, `catPriceAmount`, `catPriceStatus`)
>> VALUES
>> (2,2,1,15.00,'1'),
>> (7,3,1,15.00,'1'),
>> (9,4,1,14.95,'1'),
>> (12,6,1,14.95,'1'),
>> (14,8,1,14.95,'1'),
>> [...]
>> UNLOCK TABLES;
>>
>> Then just import/run the SQL files in MSSQL.
>>
>> There are some data types you might have to deal with in other ways, but this might handle most of what you need.
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Convert 4D data MSSQL

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Jeffrey,

Thank you,  I was not aware of SQL EXPORT DATABASE.  I might be able to make it work by dumping to an intermediate SQL database and then converting the data to the slightly modified schema from there.

Thanks
Justin
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
In reply to this post by 4D Tech mailing list

> Le 7 sept. 2017 à 17:42, Jeffrey Kain via 4D_Tech <[hidden email]> a écrit :
>
> I think this is what SQL EXPORT DATABASE and SQL EXPORT SELECTION do.  You might have some issues with date/time fields as well as object fields.

Unfortunately these commands have been written in a 4d-speaking-to-4d way: brackets around table / fields names, issues you mention, field stored out of record exported as external files, cannot exclude some fields (all or none), etc. I gave up and wrote my own:
<http://forums.4d.com/Post/FR/18395274/1/18395275#18395275>

--
Arnaud de Montard



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Convert 4D data MSSQL

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Thanks Chuck, I'll give that a try.  Perhaps batches of 25k records as arrays will work and speed things up.

> You might try to use arrays to update and not individual records

Justin
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Convert 4D data MSSQL

4D Tech mailing list
One more thing. If you can create a true stored procedure in MS sql server.
and execute by passing in that would be faster ads well
Try this link
https://technet.microsoft.com/en-us/library/

It will show you transact sql server commands etc


On Thu, Sep 7, 2017 at 5:21 PM, Justin Will via 4D_Tech <
[hidden email]> wrote:

> Thanks Chuck, I'll give that a try.  Perhaps batches of 25k records as
> arrays will work and speed things up.
>
> > You might try to use arrays to update and not individual records
>
> Justin
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>



--
-----------------------------------------------------------------------------------------
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 4D Developer
       Providers of 4D, Sybase & SQL Server connectivity
          http://www.informed-solutions.com
-----------------------------------------------------------------------------------------
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************