(MySQL) Tips Are Appreciated

cavebutter · 05-21-2014, 03:07 PM

I have a feeling that I'm going to be a pest on this sub-forum for a little while.

I'm using MySQL Workbench on a Mac.

I configured the MySQL dump and now have 67 sql files with DROP/CREATE statements. I have created a database called OOTP. My (first) question is this:

How do I execute all of these sql files without having to open and manually execute each one individually?

Workbench has an import feature but doesn't recognize the game's output as a dump file and so can't import everything at once.

Any hints or tips are kindly appreciated.
Thanks!

cavebutter · 05-23-2014, 02:30 PM

I figured it out and figured I would post the solution for those 40 or so on the boards who looked at this question.

For whatever reason, MySQL Workbench (and all other MySQL front end tools, if I understand correctly) won't let you execute .sql files from within the program. You can, however, execute a sql statement from the shell that will allow you to load and execute as many .sql files as you want. So, open a text file and type out "source" followed by the full path for each .sql file followed by a semicolon. Save this file (for future use), select all, and copy.

Get into MySQL shell, logging in with -u and -p.
Get into the right database with USE databasename
Paste your text file in and hit enter.

Since OOTP overwrites the SQL files every time it dumps, you have to repeat this process for every dump.

cavebutter · 05-23-2014, 02:41 PM

I suppose the next step is to turn that text file into its own SQL file and use SOURCE to execute that.

OK, I'm through with this thread. Thanks for reading!

MarkInCincy · 05-23-2014, 05:22 PM

Quote:

Originally Posted by cavebutter

I figured it out and figured I would post the solution for those 40 or so on the boards who looked at this question.

For whatever reason, MySQL Workbench (and all other MySQL front end tools, if I understand correctly) won't let you execute .sql files from within the program. You can, however, execute a sql statement from the shell that will allow you to load and execute as many .sql files as you want. So, open a text file and type out "source" followed by the full path for each .sql file followed by a semicolon. Save this file (for future use), select all, and copy.

Get into MySQL shell, logging in with -u and -p.
Get into the right database with USE databasename
Paste your text file in and hit enter.

Since OOTP overwrites the SQL files every time it dumps, you have to repeat this process for every dump.

Which is why MySQL is not suitable for true, real life business application/SQL development, I have used DB/2 , DB2/400, SQL server in many versions, Informix, Oracle and they all have nowhere near the limitations of My SQL. One prime example is the support for temp tables, in MySQL I have always had to put CREATE table into the script where as the others let you do Select into....

just my experience

sbailey85 · 06-05-2014, 09:33 PM

If you are doing this on Mac or Linux you have access to bash you could use this in the directory of your sql files find ./ -type f -iname '*.sql | while read lines ; do mysql -u user -p'password' dbname < $lines ; done

05-21-2014, 03:07 PM	#1
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,158	(MySQL) Tips Are Appreciated I have a feeling that I'm going to be a pest on this sub-forum for a little while. I'm using MySQL Workbench on a Mac. I configured the MySQL dump and now have 67 sql files with DROP/CREATE statements. I have created a database called OOTP. My (first) question is this: How do I execute all of these sql files without having to open and manually execute each one individually? Workbench has an import feature but doesn't recognize the game's output as a dump file and so can't import everything at once. Any hints or tips are kindly appreciated. Thanks! __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

05-23-2014, 02:30 PM	#2
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,158	I figured it out and figured I would post the solution for those 40 or so on the boards who looked at this question. For whatever reason, MySQL Workbench (and all other MySQL front end tools, if I understand correctly) won't let you execute .sql files from within the program. You can, however, execute a sql statement from the shell that will allow you to load and execute as many .sql files as you want. So, open a text file and type out "source" followed by the full path for each .sql file followed by a semicolon. Save this file (for future use), select all, and copy. Get into MySQL shell, logging in with -u and -p. Get into the right database with USE databasename Paste your text file in and hit enter. Since OOTP overwrites the SQL files every time it dumps, you have to repeat this process for every dump. __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery Last edited by cavebutter; 05-23-2014 at 02:40 PM. Reason: thought of something else

05-23-2014, 02:41 PM	#3
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,158	I suppose the next step is to turn that text file into its own SQL file and use SOURCE to execute that. OK, I'm through with this thread. Thanks for reading! __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

06-05-2014, 09:33 PM	#5
sbailey85 Minors (Single A) Join Date: Apr 2010 Posts: 94	If you are doing this on Mac or Linux you have access to bash you could use this in the directory of your sql files find ./ -type f -iname '*.sql \| while read lines ; do mysql -u user -p'password' dbname < $lines ; done