Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBI SQL statement in while loop

by peppiv (Curate)
on Dec 03, 2002 at 20:58 UTC ( #217344=perlquestion: print w/ replies, xml ) Need Help??
peppiv has asked for the wisdom of the Perl Monks concerning the following question:

Here's something I haven't been able to figure out (but have to, urgently).
I have this e-commerce package on our site and since I'm running the CC verification through a socket program I wrote, I also want to send this information to a DB for reporting purposes (the commerce package sucks, but alas, I don't get to make purchasing recs).
Here's the problem: The commerce program sends me transaction details through a form. Their form sends (1) transaction info - name, time, etc. (2) and transaction details - quantity 1 of product B, quantity 2 of product C, etc. Their form iterates through the transaction details, if there's more than one item per transaction, and sends the data to my program. The data is sent through set variables but as the program iterates over to the next item, it sends the info again to the same variables. Hence, if I do a single DBI INSERT I'm only collecting info on the Last item.

I thought that using a while loop might solve this problem but I'm not sure if it's the right thing to do or I've just totally screwed it up :0

inoperative code to follow

my $sql2 = qq/ INSERT INTO transaction_details (quantity, gross_sales) VALUES ($ +quantity, convert (money, $gross_sales)) /; while (<>) { my $quantity = param("quantity"); $quantity = $dbh->quote( $quantity ); my $gross_sales = param("gross_sales"); $gross_sales = $dbh->quote( $gross_sales ); my $rv = $dbh->do( $sql2 ); }
I get no errors. I just know that I'm not passing any data to this table (I pass other data to another table earlier in the program so I'm sure the DB connection works). Can I accept variables passed through a form to this script in a while statement?

Any suggestions or quick pointers to where I'm falling down would be appreciated.

Supa Thanx, for any help

peppiv

Comment on DBI SQL statement in while loop
Download Code
Re: DBI SQL statement in while loop
by mpeppler (Vicar) on Dec 03, 2002 at 21:27 UTC
    Off hand I think this calls for placeholders - something like:
    my $sql2 = qq/ INSERT INTO transaction_details (quantity, gross_sales) VALUES (?, ?) /; my $sth = $dbh->prepare($sql2); while (<>) { my $quantity = param("quantity"); my $gross_sales = param("gross_sales"); my $rv = $sth->execute($quantity, $gross_sales); # Handle error if $rv is false... }
    This should handle all quoting (if any is necessary) and should also handle the conversion to "money" (which makes me think that this is a Sybase or MS-SQL database server...)

    Michael

      Thanks Michael. But in using FreeTDS and DBI Sybase (thank you) I haven't been able to use placeholders. My apologies for not listing the setup: Apache/Linux/Perl -> Win2K/SQL Server 7.0
        Ah. Well in that case you'll have to move the prepare() into the loop (or use do()) and use interpolation (with the usual caveats about NULLs, etc.)

        The naive version would look like this:

        while (<>) { my $quantity = param("quantity"); my $gross_sales = param("gross_sales"); my $sql = qq/ insert into transaction_details(quantity, gross_sales) values($quantity, $gross_sales) /; my $rv = $dbh->do($sql); # check return value, etc. }
        You should of course check that the two input parameters are valid before submitting the SQL request.

        Michael

Re: DBI SQL statement in while loop
by rbc (Curate) on Dec 03, 2002 at 21:37 UTC
    Your code snippet looks a little odd to me.
    Maybe its a typo but wouldn't you want this:
    while (<>) { my $quantity = param("quantity"); $quantity = $dbh->quote( $quantity ); my $gross_sales = param("gross_sales"); $gross_sales = $dbh->quote( $gross_sales ); my $sql2 = qq/ INSERT INTO transaction_details (quantity, gross_sales) VALU +ES ($quantity, convert (money, $gross_sales)) /; my $rv = $dbh->do( $sql2 ); }
Re: DBI SQL statement in while loop
by UnderMine (Friar) on Dec 03, 2002 at 21:42 UTC
    my $sql2 = qq/ INSERT INTO transaction_details (quantity, gross_sales) VALUES ($quantity, convert (money, $gross_sales)) /;
    This will evaluate and replace $vars here before the loop (probably as undefs) Are you using strict? Can we have some more information is this a CGI using CGI.pm?

    If so what I think you are trying to get at is something like :-

    my $sql2 = qq/ INSERT INTO transaction_details (quantity, gross_sales) VALUES (?, convert (money, ?)) /;# using bind parameters my $sth = $dbh->prepare($sql); # Assuming that each row in the form has the form # {fieldname}_{rownnum} my %param = $cgi->Vars(); while my $key (grep /quantity_/ keys %param) { my $quantity = $cgi->param($key); $key =~ m/(\d+)$/; my $gross_sales = $cgi->param("gross_sales_$1"); my $rv = $sth->execute( $quantity, $gross_sales ); }
    This is untested code and does not contain error handling but it should give a good idea of the kind things you should be looking at.

    Hope it helps
    UnderMine

Re: DBI SQL statement in while loop
by chromatic (Archbishop) on Dec 03, 2002 at 21:44 UTC

    You're requesting the same form variables on each iteration. Besides that, interpolation occurs where you declare and assign the SQL statement -- not when you "re-assign" to $quantity and $gross_sales. Print $sql2 in the loop to see.

    Placeholders are definitely the way to go -- though I've never used FreeTDS, I know that DBI emulates placeholders for databases that don't support them natively.

    As for iterating over form variables, you're looping over the contents of the files named in @ARGV, or STDIN, which isn't what you want. I bet your loop contents aren't being executed once.

    You need to figure out how to iterate over your form variables. If they have programmatic names, that's one way. If they have the same name, you'll have to read their contents into arrays. It's hard to say without seeing the HTML form.

      though I've never used FreeTDS, I know that DBI emulates placeholders for databases that don't support them natively
      Unfortunately DBI doesn't - however some drivers emulate placeholders (for example DBD::MySQL).

      In the case of DBD::Sybase it is written with the Sybase libraries in mind, and in that situation will handle placeholders just fine. The problem is that FreeTDS isn't (yet) a complete implementation of the TDS protocol, and the reverse engineering problem is compounded by the fact that Sybase and MS have implemented placeholder handling differently.

      However, there is hope - I understand that the next release of FreeTDS may support placeholders correctly.

      Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://217344]
Approved by krisahoch
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (13)
As of 2014-09-17 13:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (80 votes), past polls