Newbie question

Hi,

I am a java programmer trying to get started w/perl. This is my shot
at perl, and any help is much appreciated..

I am trying t owrite a simple wrapper perl program. this program takes
in a sql command (probably in the form of exec MyStoredProc 'param1',
'param2', 'param3'). I need the perl program to establish connection
to the database, execute this command obtain the return status, and
return it back to the shell script which will be calling it.

Looking at a fwe programs on the server we seem to hvae a sybase
library that has a method, MakeDBConnection(dbName, dbServer) that i
could use. besides this i have this loop that looks like it gets the
return status but am not too sure of its syntax.

$sth = $db->prepare($query);
$sth->execute();

do {
while(my $data = $sth->fetch) {
if($sth->{syb_result_type} == CS_STATUS_RESULT) {
$returnStatus = $data->[0];
} else {
# Do nothing
}
}
} while($sth->{syb_more_results});

$sth->finish;
$db->disconnect;


With this, can someone help me setup some skeleton code for the perl
program? Any help i much appreciated, as I am a complete newbie, and
the syntax seems a little scary, to say the least!

cheers,

Re: Newbie question

wrote in message
news:1135025387.066623.183310@g43g2000cwa.googlegroups.com.. .
>
> I am trying t owrite a simple wrapper perl program. this program takes
> in a sql command (probably in the form of exec MyStoredProc 'param1',
> 'param2', 'param3'). I need the perl program to establish connection
> to the database, execute this command obtain the return status, and
> return it back to the shell script which will be calling it.
>
> Looking at a fwe programs on the server we seem to hvae a sybase
> library that has a method, MakeDBConnection(dbName, dbServer) that i
> could use. besides this i have this loop that looks like it gets the
> return status but am not too sure of its syntax.
>

Whoever wrote the code is using the DBI module, and my guess would be that
somewhere you have the line $db = DBI->connect(...) where the connection is
made.

> $sth = $db->prepare($query);

Here a statement handle is being prepared by calling the prepare function on
whatever sql command you have in $query. You should always error check your
code, though:

$sth = $db->prepare($query) or die $db->errstr;

You don't have to die, but you shouldn't continue on without a valid
statement handle. The only excuse is if you set RaiseError when you create
the handle, in which case the script will die for you.

> $sth->execute();

Again no error checking, but this is where the command you prepared above is
executed against the database.

>
> do {

This do block is a convention for looping over the result set from the
sybase database.

> while(my $data = $sth->fetch) {

fetch is an alias for fetchall_arrayref, so each iteration $data holds an
array ref containing whatever data was returned.

> if($sth->{syb_result_type} == CS_STATUS_RESULT) {
> $returnStatus = $data->[0];

This conditional just checks if the status is being returned and sets the
returnStatus variable accordingly ($data->[0] is dereferencing the array ref
above to access the first element).

> } else {
> # Do nothing
> }

I assume you're doing nothing here because you're running a stored procedure
that doesn't return anything but the result of its execution.

> }
> } while($sth->{syb_more_results});
>
> $sth->finish;

No need to call finish on the statement handle unless you haven't looped
through the entire result set.

> $db->disconnect;
>
>
> With this, can someone help me setup some skeleton code for the perl
> program? Any help i much appreciated, as I am a complete newbie, and
> the syntax seems a little scary, to say the least!
>

It looks like all you need to do is set $query to whatever sql command (or
stored procedure) you want to run and the rest of the code should be fine.
You'll need to find out where the $db connection is created and add that
code as well, but the loop should be enough to run a simple command. If you
want to use placeholders for your arguments, change the execute to:

$sth->execute($param1, $param2) or die $db->errstr;

Matt

Re: Newbie question

Thanks a lot for your help Matt. It is much appreciated.

With your help I was able to get a long way in starting out my perl
wrapper, and now I had a question about finishing it up. I have posted
it on this forum here..


http://groups.google.com/group/comp.lang.perl.misc/browse_fr m/thread/cbc4c9a6c5270a97/4636d2e0ab630985#4636d2e0ab630985

Thanks again. much appreciated.

cheers,
Matt Garrish wrote:
> wrote in message
> news:1135025387.066623.183310@g43g2000cwa.googlegroups.com.. .
> >
> > I am trying t owrite a simple wrapper perl program. this program takes
> > in a sql command (probably in the form of exec MyStoredProc 'param1',
> > 'param2', 'param3'). I need the perl program to establish connection
> > to the database, execute this command obtain the return status, and
> > return it back to the shell script which will be calling it.
> >
> > Looking at a fwe programs on the server we seem to hvae a sybase
> > library that has a method, MakeDBConnection(dbName, dbServer) that i
> > could use. besides this i have this loop that looks like it gets the
> > return status but am not too sure of its syntax.
> >
>
> Whoever wrote the code is using the DBI module, and my guess would be that
> somewhere you have the line $db = DBI->connect(...) where the connection is
> made.
>
> > $sth = $db->prepare($query);
>
> Here a statement handle is being prepared by calling the prepare function on
> whatever sql command you have in $query. You should always error check your
> code, though:
>
> $sth = $db->prepare($query) or die $db->errstr;
>
> You don't have to die, but you shouldn't continue on without a valid
> statement handle. The only excuse is if you set RaiseError when you create
> the handle, in which case the script will die for you.
>
> > $sth->execute();
>
> Again no error checking, but this is where the command you prepared above is
> executed against the database.
>
> >
> > do {
>
> This do block is a convention for looping over the result set from the
> sybase database.
>
> > while(my $data = $sth->fetch) {
>
> fetch is an alias for fetchall_arrayref, so each iteration $data holds an
> array ref containing whatever data was returned.
>
> > if($sth->{syb_result_type} == CS_STATUS_RESULT) {
> > $returnStatus = $data->[0];
>
> This conditional just checks if the status is being returned and sets the
> returnStatus variable accordingly ($data->[0] is dereferencing the array ref
> above to access the first element).
>
> > } else {
> > # Do nothing
> > }
>
> I assume you're doing nothing here because you're running a stored procedure
> that doesn't return anything but the result of its execution.
>
> > }
> > } while($sth->{syb_more_results});
> >
> > $sth->finish;
>
> No need to call finish on the statement handle unless you haven't looped
> through the entire result set.
>
> > $db->disconnect;
> >
> >
> > With this, can someone help me setup some skeleton code for the perl
> > program? Any help i much appreciated, as I am a complete newbie, and
> > the syntax seems a little scary, to say the least!
> >
>
> It looks like all you need to do is set $query to whatever sql command (or
> stored procedure) you want to run and the rest of the code should be fine.
> You'll need to find out where the $db connection is created and add that
> code as well, but the loop should be enough to run a simple command. If you
> want to use placeholders for your arguments, change the execute to:
>
> $sth->execute($param1, $param2) or die $db->errstr;
>
> Matt

Re: Newbie question

wrote in message
news:1135092875.142916.261570@g43g2000cwa.googlegroups.com.. .
> Thanks a lot for your help Matt. It is much appreciated.
>
> With your help I was able to get a long way in starting out my perl
> wrapper, and now I had a question about finishing it up. I have posted
> it on this forum here..
>

Sorry, I don't do forums. If you have any follow-up questions you're free to
post them here.

Matt