suekawar has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks , I have this problem , my compnay is using an older version of perl which doesn't support lots of perl module , I have to write scripts that interact with the databas using sql , however I can't use MySql module. so I have to come up with a way to tell the sql prompt command line what I want ,, I have tried to use the system() , qx, qq but no luck. Does anyone knows a way to go over this. thanks sample of trying: 
my $updStr="select ARKET_CODE from envent where CODE='${vCode}';";
system (qq( select ARKET_CODE from envent where CODE='${vCode}';));
$command = qx[ select ARKET_CODE from envent where CODE='${vCode}'];

[download]
so how can I print to the sql command using perl ,, thanks all

Replies are listed 'Best First'.
Re: perl and sql
by Zaxo (Archbishop) on Feb 28, 2003 at 14:28 UTC

    You are confusing shell commands with SQL. An SQL client needs to establish a connection with the db to execute the SQL instructions.

    What version of perl are you talking about? DBI is conservatively written and so is not particularly fussy about perl version. If you mean perl4, that doesn't support modules at all. Look around for a binary called perl5, if that is the case.

    You should insist on obtaining DBI and its DBD::mysql driver. You can install those in a private directory, but presumably you are doing this for others to use, and so a system installation will be needed.

    If your work really has so little support that you can't obtain that, you can kluge up something with system calls to the 'mysql' client, perhaps in batch mode. That is a highly distasteful option.

    Update: The current DBI (v1.33) requires perl 5.005_03. Checking what earlier versions are available - It appears that version 1.20 and previous work with 5.004; all say they prefer 5.004_04 or later.

    After Compline,
    Zaxo

[reply]
      i am using 5.004 ,, will this work.
[reply]
        Can you read? Yes. Just try it.
[reply]
Re: perl and sql
by Bilbo (Pilgrim) on Feb 28, 2003 at 14:24 UTC

    I don't think that what you have tried has any hope of working. system will run the command that you give it though the shell, not through the MySQL command line, so using system would be the same as running select ARKET_CODE from envent where CODE='${vCode}'; directly at the shell prompt (not the MySQL command line), so it will do nothing unless you happen to have a command/program called 'select', in which case it might do something, but not what you meant.

    What version of Perl are you using? Is it really too old to use the DBI modules or are they just not installed?

[reply]
[d/l]
[select]
      I am using 5.004
[reply]
[reply]
Re: perl and sql
by derby (Abbot) on Feb 28, 2003 at 14:38 UTC
    I kinda hate to answer this because as others have pointed out DBI/DBD are really the way to go; however, that being said, most databases provide a program to query the database. You should be able to do something like this:

    mysql < text_file_containing_sql

    I'll leave it up to you to figure out how to put the sql on the command line instead of a file and also how to execute a shell command from perl. My suggestion is to get the command correct from the command line and then move that into your script.

    -derby

[reply]
[d/l]
Re: perl and sql
by OM_Zen (Scribe) on Feb 28, 2003 at 16:14 UTC
    Hi ,

    The select has to be implemented from inside the MySQl and not from the OS and qx[$select_cmd] or backticks or system would only have it run in the OS .

    If the administrator does not want to install the DBI then do it youselves in any other directory and access it if that is somehting you can entertain doing or else try invoking

     system " sqlplus $usernm/$passwd\@$sid \@sql_command_in_an_os_file";

    This could work in Oracle and there is corresponding MySQL command that invokes the sql prompt and also kicks off the select statement if the select statement can be had in an os file of as an here document in the program itself .But DBI is THE way to do the database's level works

[reply]
[d/l]
[select]
Re: perl and sql
by trs80 (Priest) on Feb 28, 2003 at 19:38 UTC
    You should be able to do something similar to this without installing any additional modules. You will need to parse the $capture string into columns (they are seperated by tabs) 
    
use IPC::Open2;
use FileHandle;
use strict;

my ($rdr,$wtr) = (FileHandle->new , FileHandle->new);
my $pid = open2($rdr , $wtr , "mysql -uuser -ppassword database");
my $capture;
    print $wtr qq"select ARKET_CODE from envent where CODE='${vCode}';
+\n";
    close($wtr);
    while (<$rdr>) {
        $capture .= $_;
    }
print $capture , "\n";

1;

    [download]
[reply]
[d/l]
Re: perl and sql
by robartes (Priest) on Feb 28, 2003 at 14:26 UTC
    If you want to capture the output, qx or backticks should do: 
    my $string="Hello";
my $output=qx("echo $string");
print $output;
__END__
Hello

    [download]
    BTW, is there a reason you are using a symbolic reference to access your variable?

    Update: Never mind my blabbering. It's difficult to talk around the foot in my mouth :). That there is not a symbolic reference, they're disambiguating braces (thx chromatic).

    CU
    Robartes-

[reply]
[d/l]

Back to Seekers of Perl Wisdom