Running scripts batch

[sebes]

One more for today:

I use phpMyAdmin to administer my database. I have 60 scripts to create 60 tables and there will be other scripts.
Is it possible to run the scripts all at once in a batch ?

If this is not possible with phpMyAdmin, what's the alternative ?

Thanks.

 

[feherke]

Hi

I would definitely use [tt]mysql[/tt], the MySQL command-line tool :

[blue]master #[/blue] mysql mydatabasename < script.sql

[gray]# or[/gray]

[blue]master #[/blue] cat *.sql | mysql mydatabasename

Of course, supposing you have local or TCP access to the database.


Feherke.

http://free.rootshell.be/~feherke/

 

[lgarner]

And as I recall, you can upload the scripts via phpMyAdmin though you might hit a size limit or timeout.

 

[sebes]

I now have MySQL Workbench, where I understand that I can run batches of scripts comming from many files.

However I don't understand where in MySQLWorkbench oy phpMyAdmin I can do this.

Thanks.

 

[sebes]

I'll try to make this more clear.

I use SQL scripts as a model. This is a sample of createall.sql run on SQL Server. It calls all script files that are included.

So I'm looking for something similar in MySQL:
1. Is this possible ?
2. What's the syntax for "including" a file ?
3. How do I run it ?

SQL Server script used as a model:

==========================================
create database EMPL
exec sp_dbcmptlevel 'EMPL', 80
go

alter database EMPL collate latin1_general_ci_ai
go

use EMPL; /* In case already exists */

#include UDF.SQL

#include CreateTable1.SQL
#include CreateTable2.SQL
#include CreateTable3.SQL
...
...
#include CreateTable60.SQL

commit work
===============================

Thanks.

 

[sebes]

Still not clear how to run a scipt similar to the SQl server script in my previous post.

Thanks.

 

[feherke]

Hi

In case you use [tt]mysql[/tt], the MySQL command-line tool, there would be some alternatives :
[ul]
[li]for [tt]use[/tt] : [tt]connect[/tt] or [tt]\r[/tt][/li]
[li]for [tt]#include[/tt] : [tt]source[/tt] or [tt]\.[/tt][/li]
[/ul]
No idea what [tt]exec[/tt] and [tt]go[/tt] does, so no suggestion on those. See [tt]help[/tt] or [tt]\h[/tt] for the list of available commands.

Regarding [tt]commit[/tt], if that is the usual transaction finalization, then note that MySQL only supports transactions on tables handled by the InnoDB storage engine.

Feherke.

http://free.rootshell.be/~feherke/

 

[DonQuichote]

I use a separate php or python script to do the includes. See:

http://www.howtoforge.com/node/4833

Hope this helps.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[sebes]

I tried source but no luck.

My createall.sql file looks like this:

source CreateTable1.SQL;
source CreateTable2.SQL;
source CreateTable3.SQL

 

[feherke]

Hi

I tried source but no luck.

Any error message ?

And how are you executing createall.sql ?


Feherke.

http://free.rootshell.be/~feherke/

 

[sebes]

Error message:

SQL query:

source / zoo62 / mysql / scripts / createaccid.sql;

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source /zoo62/mysql/scripts/createaccid.sql' at line 1

 

[feherke]

Hi

source /[highlight] [/highlight]zoo62[highlight] [/highlight]/[highlight] [/highlight]mysql[highlight] [/highlight]/[highlight] [/highlight]scripts[highlight] [/highlight]/[highlight] [/highlight]createaccid.sql;

Quite hard to understand what are you doing there :
[ul]
[li]There was no createaccid.sql involved previously. What is its content ? How it gets executed ?[/li]
[li]What are those spaces doing in the command and why none appears in the error message ?[/li]
[/ul]


Feherke.

http://free.rootshell.be/~feherke/

 

[sebes]

Createaccid.sql creates the table accid(I used table1, table2...before as an example). This is the contents:

(All other 50 tables are created in a similar way. I want all these to be run from one place which calls each file.)

DROP TABLE IF EXISTS accid;

CREATE TABLE accid
(
AC_PERSID numeric(6) not null default 0 ,
AC_ACCID char(8) not null default '' primary key,
AC_ORIGID char(8) not null default '' ,
AC_EFFDT datetime default 0 ,
AC_EFFDTM datetime default 0 ,
AC_GRAVITY char(4) not null default '' ,
AC_DOSSNO char(22) not null default '' ,
AC_JOBID char(8) not null default '' ,
AC_LOCN char(8) not null default '' ,
AC_TASKID char(8) not null default '' ,
AC_SCHEDID char(6) not null default '' ,
AC_BOSSID numeric(6) not null default 0 ,
AC_ATYPE char(4) not null default '' ,
AC_CATEGRY numeric(1) not null default 0 ,
AC_REOCCUR char(1) not null default '' ,
AC_ACSTAT char(6) not null default '' ,
AC_ENTITY1 char(20) not null default '' ,
AC_ENTITY2 char(20) not null default '' ,
AC_DECLDT datetime default 0 ,
AC_DOSSDT datetime default 0 ,
AC_STOPWDT datetime default 0 ,
AC_LIGHTDT datetime default 0 ,
AC_RETRNDT datetime default 0 ,
AC_INCAPDT datetime default 0 ,
AC_DIEDDT datetime default 0 ,
AC_INJURY char(6) not null default '' ,
AC_LIMB char(6) not null default '' ,
AC_BPLANID char(10) not null default '' ,
AC_CAUSE varchar(4000) not null default '' ,
AC_OFFSITE bit default 0 ,
AC_LATE bit default 0 ,
AC_OVERTIM bit default 0 ,
AC_OPEN bit default 0 ,
AC_DESCR varchar(4000) not null default '' ,
AC_CORRECT varchar(4000) not null default '' ,
AC_LOCATN varchar(4000) not null default '' ,
AC_DOCTOR varchar(4000) not null default '' ,
AC_NOTES varchar(4000) not null default '' ,
AC_1STAID varchar(4000) not null default '' ,
AC_EXTPERS varchar(4000) not null default '' ,
AC_INVOLVE varchar(4000) not null default '' ,
AC_LEGAL varchar(4000) not null default '' ,
AC_LITEWRK varchar(4000) not null default '' ,
AC_CONFID varchar(4000) not null default '' ,
AC_DOCS varchar(4000) not null default '' ,
AC_DAMAGE numeric(11,2) not null default 0 ,
AC_SALPAID numeric(8,2) not null default 0 ,
AC_DAYSPD numeric(2) not null default 0 ,
AC_AFTER14 bit default 0 ,
AC_MNT14 numeric(8,2) not null default 0 ,
AC_FREQ14 numeric(1) not null default 0 ,
AC_FRODT14 datetime default 0 ,
AC_TODT14 datetime default 0 ,
AC_ADRDT datetime default 0 ,
AC_ADREXDT datetime default 0 ,
AC_EVENTID char(16) not null default '' ,
AC_USER char(8) not null default '' ,
AC_MODDT datetime default 0 ,
AC_MODSITE char(8) not null default '' ,
AC_TOSYNC varchar(4000) not null default '' ,
AC_MORE varchar(4000) not null default ''
);

create index AC_ACCID on ACCID (AC_ACCID);
create index AC_EFFDT on ACCID (AC_EFFDT);
create index AC_DOSSNO on ACCID (AC_DOSSNO);
create index AC_ORIGID on ACCID (AC_ORIGID);
create index AC_PERSID on ACCID (AC_PERSID)

 

[feherke]

Hi

That still not answers my questions. ( Well, maybe my questions are not as clear as I imagine... )

I try again with another example. Here I use 4 SQL script files : the 1[sup]st[/sup] sources the 2[sup]nd[/sup] and the 4[sup]th[/sup], the 2[sup]nd[/sup] sources the 3[sup]rd[/sup]. All these executed from the command prompt :

[blue]master #[/blue] cat doit.sql 
source create.sql

source select.sql

[blue]master #[/blue] cat create.sql 
create table sebes (
  id integer primary key auto_increment,
  value varchar(10)
);

source insert.sql

[blue]master #[/blue] cat insert.sql 
insert into sebes (value) values ('one');
insert into sebes (value) values ('two');
insert into sebes (value) values ('three');

[blue]master #[/blue] cat select.sql 
select * from sebes;

[blue]master #[/blue] mysql -Dtest < doit.sql 
id      value
1       one
2       two
3       three

Feherke.

http://free.rootshell.be/~feherke/

 

[sebes]

Sorry amigo, I appreciate your effort but as a beginner I don't understand what "master # cat doit.sql" means or where it should be run.

I suppose these should be done from the MySQL server somewhere, I mean where MySQL is installed.

Is this correct ?

 

[feherke]

Hi

as a beginner I don't understand what "master # cat doit.sql" means or where it should be run.

Ah, sorry. I supposed it was enough mentioning at the very beginning that I am talking about "[tt]mysql[/tt], the MySQL command-line tool". The absence of related questions made me think my supposition was correct.

[tt]mysql[/tt] — The MySQL Command-Line Tool is a simple client application, capable both to run interactively and to execute scripts. It is similar to [tt]psql[/tt] of PostgreSQL, [tt]sqlplus[/tt] of Oracle and [tt]sqlite[/tt] of SQLite.

The line you quoted is just a line from the shell ( on Windows is [tt]cmd.exe[/tt] ) meaning :
[tt]
[blue]master #[/blue] cat doit.sql
[gray]\______/ \_/ \______/
| | `-- file to display
| `-------- Unix command to display a file's content ( on Windows there is type )
`-------------- prompt of the command interpreter ( on Windows it is like C:\> )[/gray]
[/tt]
I just used that to show what were used later by the [tt]mysql[/tt] command.

I suppose these should be done from the MySQL server somewhere, I mean where MySQL is installed.

My example indeed used a locally installed MySQL server, but that is not a requirement. You can specify where the MySQL server runs by using additional parameters :

mysql -D[i]database_name[/i] -h[i]host_name_or_ip_address[/i] -P[i]tcp_port_number[/i] -u[i]database_user_name[/i] -p[i]password[/i]

When those parameters are not specified, their default values are used : localhost for host name, 3306 for TCP port, operating system user name for user name and nothing for password.


Feherke.

http://free.rootshell.be/~feherke/

 

[sebes]

How do I install MySQL Command Line tool on my locan computer ?

 

[feherke]

Hi
[ul]
[li]Download the only MySQL Installer.[/li]
[li]Start the installer.[/li]
[li]When the installer offers the possibility to choose Custom, pick that.[/li]
[li]From the component list deselect the server and keep selected only the client. This will require some libraries too, let them selected.[/li]
[li]Finalize the installation.[/li]
[/ul]
As far as I remember, only the server needs additional configuration, the client should be usable immediately.

[gray]( Do not shoot me if I was wrong. I installed MySQL on Windows ~5 years ago. )[/gray]


Feherke.

http://free.rootshell.be/~feherke/