Professional Web Applications Themes

making SQL scripts more flexible??????? - MySQL

I have a number of SQL scripts that support batch processing of a number of data files. Right now, they have the paths to the data files hardcoded into the scripts ( in the INFILE clause of LOAD statements. One other requirement involves setting a field in a couple of the tables to one day prior to the timestamp on the datafile. I can easily get that date using Perl. Presently I have a perl script that does some preprocessing before running my SQL scripts using 'mysql', and it gets the relevant paths from a configuration file. What would be ...

  1. #1

    Default making SQL scripts more flexible???????

    I have a number of SQL scripts that support batch processing of a
    number of data files.

    Right now, they have the paths to the data files hardcoded into the
    scripts ( in the INFILE clause of LOAD statements.

    One other requirement involves setting a field in a couple of the
    tables to one day prior to the timestamp on the datafile. I can easily
    get that date using Perl.

    Presently I have a perl script that does some preprocessing before
    running my SQL scripts using 'mysql', and it gets the relevant paths
    from a configuration file.

    What would be good, if possible, is to pass data from my configuration
    file, or from a handful of statements in my perl script, to my SQL
    scripts. Alas, I have not found a way to do this. Therefore, I am
    looking at having my perl script replace the SQL scripts, constructing
    the statements presently in my SQL scripts, and using the Perl database
    related modules to execute these dynamically created scripts. Is there
    an easier or better (e.g. as defined by code maintenance requirements),
    or am I really constrainted to construct my SQL statements dynamically?


    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Re: making SQL scripts more flexible???????

    "Ted" <r.ted.byersrogers.com> wrote:

    <cut>
    > What would be good, if possible, is to pass data from my configuration
    > file, or from a handful of statements in my perl script, to my SQL
    > scripts.
    You could use MySQL user variables for that. Your Perl script would
    emit a list of SET statements, defining variables that are used in
    your "real" SQL scripts later. Example:

    ~ $cat aaa
    set x='World';
    ~ $cat bbb
    select concat('Hello ', x);
    ~ $cat aaa bbb | mysql -t test
    +----------------------+
    | concat('Hello ', x) |
    +----------------------+
    | Hello World |
    +----------------------+


    If you are using MySQL 5.0 you can write your own stored procedure and
    execute it from your Perl script (with appropriate parameters filled in
    by your script).


    If none of the above helps, you can write your Perl script as filter,
    substituting placeholders in your SQL script. Then do something like:

    cat template.sql | magic_script.pl <arguments> | mysql <database>


    HTH, XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  3. #3

    Default Re: making SQL scripts more flexible???????


    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.com> wrote:
    >
    > <cut>
    >
    > > What would be good, if possible, is to pass data from my configuration
    > > file, or from a handful of statements in my perl script, to my SQL
    > > scripts.
    >
    > You could use MySQL user variables for that. Your Perl script would
    > emit a list of SET statements, defining variables that are used in
    > your "real" SQL scripts later. Example:
    >
    > ~ $cat aaa
    > set x='World';
    > ~ $cat bbb
    > select concat('Hello ', x);
    > ~ $cat aaa bbb | mysql -t test
    > +----------------------+
    > | concat('Hello ', x) |
    > +----------------------+
    > | Hello World |
    > +----------------------+
    >
    >
    > If you are using MySQL 5.0 you can write your own stored procedure and
    > execute it from your Perl script (with appropriate parameters filled in
    > by your script).
    >
    >
    > If none of the above helps, you can write your Perl script as filter,
    > substituting placeholders in your SQL script. Then do something like:
    >
    > cat template.sql | magic_script.pl <arguments> | mysql <database>
    >
    >
    > HTH, XL
    > --
    Thanks Axel
    I have a some questions about this.

    1) I assume, based on the repeated use of the string "~ $cat" that
    you're using some flavour of unix and that $cat is a utility program
    that comes with unix. And, if I'm right, I'd expect that 'x' is a
    variable that lives within your commandline shell. This is just a
    guess because I haven't done any scripting within a unix environment.
    Is my guess close to being right? If so, do you know if there is a
    Windows equivalent? How might I do the same thing within a perl
    script? I presently have a perl script that does some preproccessing
    before running my SQL scripts.

    2) I am running the latest release of MySQL 5. But I thought stored
    procedures weren't allowed to refer to tables. Has that changed since
    I last looked at stored procedures in MySQL? Does it make a difference
    that my main scripts consist largely of LOAD INFILE statements?

    3) My first thought, here, was to create a perl script that functions
    as a filter, using a regular expression to replace path place holders
    by the real path detected by my main perl script, and then use DBI to
    submit the SQL statement to MySQL. I know how to do this from within
    my perl script. I am not sure, though how I could handle piping output
    from one program to another program from within a perl script.

    I find myself constrained to use perl for my scripting because it is
    one thing all of my colleagues have installed, in a situation where
    some of us are running Windows XP while others are running some flavour
    of Linux (I have SUSE, which I am still trying to learn, while another
    is running Debian), and the deployment machine is running XP. Perl
    plus MySQL is about all we have in common right now.

    Thanks again

    Ted

    Ted Guest

  4. #4

    Default Re: making SQL scripts more flexible???????

    "Ted" <r.ted.byersrogers.com> wrote:
    > 1) I assume, based on the repeated use of the string "~ $cat" that
    > you're using some flavour of unix and that $cat is a utility program
    > that comes with unix.
    Right, this is Linux. 'cat' OTOH is the simplest of all tools. It just
    reads all the files and writes their contents to the standard output,
    thus conCATenating the files. Windows has 'type' for this (IIRC).
    > And, if I'm right, I'd expect that 'x' is a
    > variable that lives within your commandline shell.
    Nope. As I wrote, it is a MySQL user variable. It lives inside the
    MySQL server. Read more about it here:

    [url]http://dev.mysql.com/doc/refman/5.0/en/user-variables.html[/url]

    One important detail is the session scope of user variables. You *must*
    feed both scripts (the one defining variables and the one referencing
    them) into *one* MySQL session. In the example I used 'cat' to pipe
    both scripts into the 'mysql' tool. You can also run 'mysql' in inter-
    active mode and SOURCE the scripts one after the other.
    > 2) I am running the latest release of MySQL 5. But I thought stored
    > procedures weren't allowed to refer to tables. Has that changed since
    > I last looked at stored procedures in MySQL?
    This was a restriction in very first alpha releases. Now you can access
    arbitrary tables from stored procedures. However LOAD DATA is still
    disallowed in stored routines. So this won't help you. Sorry.
    > 3) My first thought, here, was to create a perl script that functions
    > as a filter, using a regular expression to replace path place holders
    > by the real path detected by my main perl script, and then use DBI to
    > submit the SQL statement to MySQL. I know how to do this from within
    > my perl script. I am not sure, though how I could handle piping output
    > from one program to another program from within a perl script.
    Piping is handled by the commandline interpreter. On UN*X systems, this
    is called /shell/. Windows' command.exe (or whatever it's named today)
    can do piping as well.

    If you feel better, you can have your Perl script write an intermediate
    file and feed this into 'mysql'. Or just run the generated SQL state-
    ments via DBI. If you go the second way, make sure to

    a) implement a 'protocol' function to have a look at the generated SQL -
    you will *love* this as soon as you get SQL errors

    b) implement extensive error checking. You might even want to check for
    warnings right after each statement


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  5. #5

    Default Re: making SQL scripts more flexible???????

    Hi Axel,

    Thanks.

    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.com> wrote:
    >
    > > 1) I assume, based on the repeated use of the string "~ $cat" that
    > > you're using some flavour of unix and that $cat is a utility program
    > > that comes with unix.
    >
    > Right, this is Linux. 'cat' OTOH is the simplest of all tools. It just
    > reads all the files and writes their contents to the standard output,
    > thus conCATenating the files. Windows has 'type' for this (IIRC).
    >
    Right.
    > > 3) My first thought, here, was to create a perl script that functions
    > > as a filter, using a regular expression to replace path place holders
    > > by the real path detected by my main perl script, and then use DBI to
    > > submit the SQL statement to MySQL. I know how to do this from within
    > > my perl script. I am not sure, though how I could handle piping output
    > > from one program to another program from within a perl script.
    >
    > Piping is handled by the commandline interpreter. On UN*X systems, this
    > is called /shell/. Windows' command.exe (or whatever it's named today)
    > can do piping as well.
    >
    Right. I knew that. I was thinking in terms of what I'd put into
    $cmd_line in "qx/Scmd_line/;"
    > If you feel better, you can have your Perl script write an intermediate
    > file and feed this into 'mysql'. Or just run the generated SQL state-
    > ments via DBI. If you go the second way, make sure to
    >
    > a) implement a 'protocol' function to have a look at the generated SQL -
    > you will *love* this as soon as you get SQL errors
    >
    > b) implement extensive error checking. You might even want to check for
    > warnings right after each statement
    >
    I now have it working adequately. However, can I trouble you for a
    simple example of a 'protocol' function, and how you use it. I think I
    know what you're talking about, and suspect I have a fair number of
    such functions in my C++ code: just something I normally do due to my
    paranoia about code quality and data validity, but not something I'd
    put a name to. When I did it, I'd throw an exception (one of my own),
    and incorporate it in my traceable exception infrastructure (which
    gives me a stack trace that includes the values held by key variables
    as each statement in the trace was executed). I don't even want to
    think about trying to implement my traceable exceptions infrastructure
    in Perl!

    Thanks again,

    Ted

    Ted Guest

Similar Threads

  1. Flexible Height of Datagrid
    By sakura102384 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 21st, 11:54 AM
  2. flexible page
    By zimko in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: September 19th, 11:52 AM
  3. flexible layer width?
    By Lisa Simpson in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: February 25th, 03:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139