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.

 

Hi

I would definitely use mysql, the MySQL command-line tool :
Of course, supposing you have local or TCP access to the database.
 

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

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

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.

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.

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

Thanks.

Hi

In case you use mysql, the MySQL command-line tool, there would be some alternatives :

• for use : connect or \r
• for #include : source or \.

No idea what exec and go does, so no suggestion on those. See help or \h for the list of available commands.

Regarding commit, 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/

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)

I tried source but no luck.

My createall.sql file looks like this:

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

 

Hi

Quote (sebes):

I tried source but no luck.

Any error message ?

And how are you executing createall.sql ?
 

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

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
 

Hi

Quote (sebes):

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

Quite hard to understand what are you doing there :

• There was no createaccid.sql involved previously. What is its content ? How it gets executed ?
• What are those spaces doing in the command and why none appears in the error message ?

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

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)

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^st sources the 2^nd and the 4^th, the 2^nd sources the 3^rd. All these executed from the command prompt :
 

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

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 ?

 

Hi

Quote (sebes):

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 "mysql, the MySQL command-line tool". The absence of related questions made me think my supposition was correct.

mysql — The MySQL Command-Line Tool is a simple client application, capable both to run interactively and to execute scripts. It is similar to psql of PostgreSQL, sqlplus of Oracle and sqlite of SQLite.
 
The line you quoted is just a line from the shell ( on Windows is cmd.exe ) meaning :

master # cat doit.sql
\______/ \_/ \______/
    |     |      `-- 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:\> )

I just used that to show what were used later by the mysql command.

Quote (sebes):

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 :
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/

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

Hi

• Download the only MySQL Installer.
• Start the installer.
• When the installer offers the possibility to choose Custom, pick that.
• From the component list deselect the server and keep selected only the client. This will require some libraries too, let them selected.
• Finalize the installation.

As far as I remember, only the server needs additional configuration, the client should be usable immediately.

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

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