Re: Mixing select and print statements using DBI, DBD::Sybase
From: David Goodman (dtzgdman_at_yahoo.com)
Date: 02/10/05
- Previous message: Jeffrey Seger: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- In reply to: Jeffrey Seger: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- Next in thread: Michael Peppler: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 9 Feb 2005 19:18:54 -0800 (PST) To: dbi-users@perl.org
Hello Jeff:
Thank you for your reply.
Isql does not have its own commands. So the print in
the sql is part of transact-sql, and is executed on
the server. This is foreign to Oracle. Regardless of
the tool, the print command will result in a "message"
result being sent back to the client.
Default DBI behaviour is to have those results turn up
in the DBI::errstr. DBD::Sybase lets you install an
error handler, which I have done. In this case, I am
trying to use the error handler to essentially include
the message results in with the row results, and in
the same order.
The problem seems to be that the error handler gets
its result before the rows return, even if the order
in the sql batch has the print executing later.
My questions would be: Is that the case? And is this
behaviour of DBD::Sybase considered correct?
I worked out how to intercept result status, which I
was asking about in the previous email.
regards,
David
--- Jeffrey.Seger@fairchildsemi.com wrote:
> I have seen similar questions regarding any number
> of sql based command
> line tools (mysql, isql, sql*plus, dbaccess, etc).
>
> The basic issue here is that there is SQL, (select,
> insert, update,
> create, alter, etc), and there are tool based
> commands (print, set,
> export, unload, load, etc). DBI and
> DBD::yourfavoriteRDBMS support just
> the sql commands, not the tool commands. You have
> to write perl code to
> emulate the output of your tool based commands if
> you want to intersperse
> it with SQL.
>
> Something like this:
>
> $sql{a} = "select one=1"; # I'll assume this is
> valid Sybase sql, since
> you had it in your post. It's not valid in Oracle.
> $sql{b} = "select two=2";
>
> foreach ('a','b'){
> $sth=$dbh->prepare($sql{$_});
> $sth->execute;
> while (($row) = $sth->fetchrow_array) {
> print $row . "\n";
> print "hello $_ world\n";
> }
> }
>
>
>
>
> ____________________________
> Jeff Seger
> Fairchild Semiconductor
> jeffrey.seger@fairchildsemi.com
> ____________________________
>
>
>
>
> David Goodman <dtzgdman@yahoo.com>
> 02/09/2005 03:07 PM
>
>
> To: dbi-users@perl.org
> cc:
> Subject: Mixing select and print
> statements using DBI, DBD::Sybase
>
>
> Here is my simple, syntactically correct test case:
>
> select ONE=1
> print "hello world!"
> select TWO=2
>
> Using isql I get the results in the expected order:
>
> ONE
> -----------
> 1
>
> (1 row affected)
> hello world!
> TWO
> -----------
> 2
>
> (1 row affected)
>
> Using perl with DBI, DBD::Sybase the message "hello
> world!" comes back first. I have permuted other
> print
> and select statements and I find that my perl
> program
> returns the messages first when the statements are
> run
> in one batch. I find the same thing is true when my
> perl program executes a stored procedure.
>
> My program catches messages with an error handler
> installed by setting the attribute
> $dbconn->{"dbhandle"}->{syb_err_handler}. The rows
> are
> retrieved using DBI call fetchall_arrayref.
>
> Questions:
> 1. Can I get the results back in the expected order
> when executing multiple statements from
> perl/DBI/DBD::Sybase?
> 2. Can I suppress the result status of a stored
> procedure from coming back as a row when using
> fetchall_arrayref?
>
> Thanks for your help.
>
> regards,
>
> David
>
>
>
>
>
>
>
- Previous message: Jeffrey Seger: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- In reply to: Jeffrey Seger: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- Next in thread: Michael Peppler: "Re: Mixing select and print statements using DBI, DBD::Sybase"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
