#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    2
    Rep Power
    0

    Call SQL script from other scripts


    I am using MySQL v4.0.11g with InnoDB tables.

    In order to practice modular programming, I would like to separate common SQL statements such as CREATE TABLE into standalone script files, then invoke those scripts/files from other SQL or shell orchestration script files.

    This would allow me to:
    • alter (say) a table definition in one place and have the definition statement automatically propagate to all other orchestration scripts that call it, such as automated database create, dump and rebuild scripts
    • avoid the fragility of writing hard-coded absolute paths that MySQL and the mysql client seems to require me to use, which I'd need to invoke SQL files located outside my db's MySQL data directory
    • avoid rewriting absolute paths in SQL, shell or mysql orchestration scripts in order to stage them to a production db server


    Can anyone suggest a way to do this, either from within an SQL script or how to use from a shell script allowing relative paths within the mysql basedir or datadir?

    I have looked through config-file help in the MySQL manual for clues (I would have thought I could use relative paths from the mysqld basedir option setting), but so far no luck.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    Location
    Binghamton, NY
    Posts
    536
    Rep Power
    29
    Since mysqld doesn't read files directly, then it depends on your client. If you are using the mysql command line client, you can include them by using the 'source' command. So the file would look like:
    Code:
    source otherfile.sql;
    source otherfile2.sql;
    The source command is relative to your current working directory. So if you were in /home/harry then it would look for /home/harry/otherfile.sql and so on.

    Keep in mind that source is a client command, nothing to do with the server. Depending on what API you use, the commands might be different.

IMN logo majestic logo threadwatch logo seochat tools logo