Re: Move WebDNA DB to MySQL
This WebDNA talk-list message is from 2005
It keeps the original formatting.
numero = 60733
interpreted = N
texte = This import procedure is extremely fast and flexible:[SQLExecute conn_ref=conn1&result_var=UPL]LOAD DATA LOCAL INFILE 'path to/datafile.txt' INTO TABLE tablename FIELDS TERMINATED BY '' LINES TERMINATED BY '\n' (sqltablefield1, sqltablefield2, sqltablefield3, sqltablefield4,...);[/SQLExecute]This code uses a space delimited file - the length of the field in the table determines the length of the data to grab.For tab delimited, simple substitute:FIELDS TERMINATED BY '\t'The Insert function below works, but is very slow moving large amounts of data. We upload a file with approximately 80,000 lines from one of our county election clients; using INSERT (the first way I programmed it) took 3-4 minutes to complete; using the code above is is almost instantaneous.CheersLarryOn Jan 11, 2005, at 2:37 PM, Clint Davis wrote:> I want to move a tab-delimited members database that WebDNA was using > to a> MySQL database. What would be the proper syntax to search the > tab-delimited> file with WebDNA and INSERT all founditems into the MySQL database? Is > the> methodology below acceptable?>> [SQLConnect> dbType=MySQL&host=xxx&database=xxx&uid=xxx&pwd=xxx&conn_var=conn1]> [/SQLConnect]>> [search db=my.db&neIDdata=find_all]> [founditems]> [SQLExecute conn_ref=conn1&result_var=rs1]> INSERT INTO xxx VALUES ('[value1]', '[value2]', '[value3]', '[value4]')> [/SQLExecute]> [/founditems]> [/search]>> Is there a better/easier way to INSERT multiple rows?>>> ---------------------------------------------------------------------> Clint Davis> Webmaster / Interactive Media Specialist> Gray Loon Marketing Group, Inc. http://www.grayloon.com> 204 Main Street | Evansville, IN 47708 | Phone:812-422-9999> --------------------------------------------------------------------->>>> -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list
.> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/>>Larry Hewitt360.807.1100 OFFICEHCO, LLC 360.807.1103 FAX109 No. Tower360.880.4855 CELLP.O. Box 1017larryh@hewittco.comCentralia, WA 98531www.votewashington.orgwww.hewittco.com-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
Associated Messages, from the most recent to the oldest:
This import procedure is extremely fast and flexible:[SQLExecute conn_ref=conn1&result_var=UPL]LOAD DATA LOCAL INFILE 'path to/datafile.txt' INTO TABLE tablename FIELDS TERMINATED BY '' LINES TERMINATED BY '\n' (sqltablefield1, sqltablefield2, sqltablefield3, sqltablefield4,...);[/SQLExecute]This code uses a space delimited file - the length of the field in the table determines the length of the data to grab.For tab delimited, simple substitute:FIELDS TERMINATED BY '\t'The Insert function below works, but is very slow moving large amounts of data. We upload a file with approximately 80,000 lines from one of our county election clients; using INSERT (the first way I programmed it) took 3-4 minutes to complete; using the code above is is almost instantaneous.CheersLarryOn Jan 11, 2005, at 2:37 PM, Clint Davis wrote:> I want to move a tab-delimited members database that WebDNA was using > to a> MySQL database. What would be the proper syntax to search the > tab-delimited> file with WebDNA and INSERT all founditems into the MySQL database? Is > the> methodology below acceptable?>> [SQLConnect> dbType=MySQL&host=xxx&database=xxx&uid=xxx&pwd=xxx&conn_var=conn1]> [/SQLConnect]>> [search db=my.db&neIDdata=find_all]> [founditems]> [SQLExecute conn_ref=conn1&result_var=rs1]> INSERT INTO xxx VALUES ('[value1]', '[value2]', '[value3]', '[value4]')> [/SQLExecute]> [/founditems]> [/search]>> Is there a better/easier way to INSERT multiple rows?>>> ---------------------------------------------------------------------> Clint Davis> Webmaster / Interactive Media Specialist> Gray Loon Marketing Group, Inc. http://www.grayloon.com> 204 Main Street | Evansville, IN 47708 | Phone:812-422-9999> --------------------------------------------------------------------->>>> -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/>>Larry Hewitt360.807.1100 OFFICEHCO, LLC 360.807.1103 FAX109 No. Tower360.880.4855 CELLP.O. Box 1017larryh@hewittco.comCentralia, WA 98531www.votewashington.orgwww.hewittco.com-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
Larry Hewitt
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
MY SOLUTION.... Send massmail (2000)
All choice in popups (1997)
New Command prefs ... (1997)
[URL] and [showif] or [hideif] comparisons (2003)
Some Questions (1997)
Search returns all, not 20 (1997)
Frames and WebCat (1997)
Testing band width (2002)
redirect not passing vars (2004)
math on date? (1997)
passing info to correct frame (2000)
WebCat2b15MacPlugin - [protect] (1997)
Summing fields (1997)
LOOP and IF statements (1997)
[WebDNA] Record click-thrus (2008)
Reversed words (1997)
customizing the color of user's pages (1997)
is sku a REQUIRED field on NT (1997)
Banner ads example (1997)
WebCat2: multiple currency support (1997)