Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Re: Multiple SQL statements in DBI

by chacham (Prior)
on Sep 07, 2012 at 10:33 UTC ( #992275=note: print w/replies, xml ) Need Help??

in reply to Multiple SQL statements in DBI

1. If the error is with setting nickname = 'three', then why doesn't nickname end up being = 'two'?

Because the entire second batch is rejected. The error does not mean that the second statement in the second batch failed; it means that because of the second statement, the entire batch failed.

2. Does DBI allow multiple SQL statements to be run in one invocation? If so, how can that be done?

The DBI documentation states "Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server)."

Technically, DBI allows multiple statements to be passed in one call, though it is up to the specific driver to allow multiple statement in one batch.

As a block in considered one statement (like multiple lines of code in curly braces), most implementations allow passing an anonymous block with multiple statements. Such as:

BEGIN statement 1; statement 2; END

3. Or if not, why not? Is it to help prevent SQL injection, perhaps? That's fine, as that's the reason for my test, but I'd like to see how to make it happen before I see if my methods of prevention are working.

Why not? Because database CLI drivers generally allow exactly one statement to be processed, and DBI can't change that.

SQL Injection? No. SQL Injection is only a concern with dynamic SQL. Multiple statements and dynamic SQL are two completely different things.

Replies are listed 'Best First'.
Re^2: Multiple SQL statements in DBI
by mje (Curate) on Sep 07, 2012 at 14:26 UTC

    The DBI documentation states "Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server)."

    Thanks for reminding me of that. I need to change that as although it is true for MS SQL Server it is full of problems e.g., you may need to call SQLMoreResults to move from one statement to the next, it will break horribly with certain types of procedure calls and array fetching is affected too.

Re^2: Multiple SQL statements in DBI
by tel2 (Monk) on Sep 07, 2012 at 22:41 UTC
    Thanks very much for all that, chacham!

    I'm not sure where CLI driver is, but if it's part of MySQL (the DB I'm using), then note that from the Linux prompt I can echo multiple SQL commands and pipe them to a mysql command and they work.  Good point re the DBI documentation, though - I should have seen that.

      I'm not sure where CLI driver is,

      CLI is the Call Level Interface. when you type in mysql and get a prompt, that program is using the CLI to communicate with the database. (At least that how everyone else does it.) In a sense, you are never "in" the database, you are always making calls to it instead. But those nice people at mysql have a nifty program to make basic calls for you, behind the scenes, per se.

      When you echo multiple statement in a client program, it sends each statement separately through the CLI. That's two statements, two cursors, et al. It hides it from you, but there are two calls. When you are using DBI, you are taking control of when to call the CLI (via DBI, which does it for you), so when you have two statements, the CLI gets called only once, and it doesn't support multiple statements in the same batch.

        Thanks again chacham.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://992275]
[thezip]: Our cat is single-handedly (pawed-ly?) solving our field mouse problem. About four critters in the last week alone.
[thezip]: Amazing speed and stealth in that one.
[GotToBTru]: You can't tunafish, but some seafood companies can tunafish. More fun with English.
[virtualsue]: a veterinarian told me to cut down on my cat's food. i said, when i do that she just catches more mice
[thezip]: My dogs are also excellent mousers, at least that's what they tell me.
[GotToBTru]: our beagle has dispatched 3 rabbits to the Great Hutch in the Sky since March.
[thezip]: I can always tell when the dogs have been mousing, as there is a concentric ring of mud around the end of their snouts when they do.
[thezip]: As for more physical evidence, that always seems to be missing somehow
[Rabbi Bob]: We have a Jack Russell: off the leash she is a torpedo against mice, rats, chipmunks and squirrels

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (15)
As of 2017-11-17 19:55 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (272 votes). Check out past polls.