Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Replacing a ' in a string

by SamueD2 (Novice)
on Feb 06, 2003 at 16:05 UTC ( [id://233167] : perlquestion . print w/replies, xml ) Need Help??

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

This node falls below the community's threshold of quality. You may see it by logging in.

Replies are listed 'Best First'.
•Re: Replacing a ' in a string
by merlyn (Sage) on Feb 06, 2003 at 16:22 UTC
    I almost don't even want to comment on this code because it soooo hurts my eyes. Here's the first few bits of what I can contribute:
    • Your quoting problem will disappear when you use placeholders. See the DBI manpage for details.
    • Please, go a little easier on the shiftlock key.
    • Variables that include numbers as part of the names are almost always a sign of misdesign.
    • How much code will you have to change when you add the 63rd column to your spreadsheet? Isn't there any way to get column headings from the sheet, so you can do a real dynamic mapping of the needed columns?
    • Short of that, how about not copying every variable all over creation, but simply creating a map (hash) from database name to positional index, and using that map both to create the columns to insert and to select the proper value from an array for the placeholder. There's other code here in the catacombs to show how to do that.
    Seriously, that code would definitely flunk any code review I gave it.

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

Re: Replacing a ' in a string
by blokhead (Monsignor) on Feb 06, 2003 at 16:28 UTC
    Please use placeholders! They will automatically escape the special characters (like the apostrophe) in your data. If you're not familiar with what these are, check out What are placeholders in DBI, and why would I want to use them?.

    Also, I'd recommend not using temporary variables inside your while loop for all the data. With placeholders, you can just pass the entire @data array as arguments for the SQL statement, and also reuse the SQL statement for each insert statement. Here's an example of how you can avoid using the temporary variables (untested):

    my @fields = qw/CLIENT RECORD CONTROL CUSIP .../; ... # open file and run sanity checks my $columns = join ',', @fields; # create the correct number of placeholders my $placeholders = join ',', ('?') x @fields; my $sql = "INSERT INTO table ($columns) values ($placeholders)"; my $sth = $dbh->prepare($sql); while ( defined $line = <INFILE> ) { chomp $line; my @data = split /\t/, $line; ... # probably should check to see if all the fields are defined. # then insert using our statement, passing the values of @data to # replace the placeholders in the SQL statement $sth->execute(@data); }
    Notice how much easier this code would be to update if your flatfile format were changed, as merlyn suggests above. I think once you get the hang of placeholders, you will really be glad you did -- they make inserting data a breeze (among their other good qualities). If you have any questions about these suggestions, feel free to ask. Also, I'd recommend reading the DBI documentation sections about placeholders, and using Super Search here at the monestary to learn more.

    Hope this helped.


Re: Replacing a ' in a string
by bart (Canon) on Feb 06, 2003 at 16:30 UTC
    Please use placeholders. Those are like anonymous variables in your SQL, the syntax is a question mark. That way, DBI takes care of all the quoting for you, including the problem you're having now. (You don't put quotes around the question mark.)

    As an added benefit, you have to prepare your SQL statement only once, because it no longer contains any variable data. You just execute() the prepared statement with the new data for the placeholders.

    The code could look a bit like this — obviously, you must merge it in with what you've got, at the appropriate places:

    my $sql = "INSERT INTO mf_forms (CLIENT, RECORD, CONTROL, CUSIP, FROMA +CCT, F_IND, TOACCT, T_IND) VALUES (?,?,?,?,?,?,?)"; my $sth = $dbh->prepare($sql);
    and foreach record:
    $sth->execute($CLIENT, $RECORD, $CONTROL, $CUSIP, $FROMACCT, $F_IND, $ +TOACCT, $T_IND);
    p.s. You may just as well call execute() with an array containing the data.
      If this is a CGI application, you're going to want to check out prepare_cached(). If you're going to do SELECT statements, see also bind_columns() and fetch().

      We are the carpenters and bricklayers of the Information Age.

      Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Re: Replacing a ' in a string
by dreadpiratepeter (Priest) on Feb 06, 2003 at 16:48 UTC
    Did you add the use strict line to your text before posting so you wouldn't get yelled at? Because there is no way that this code will run under use strict with 40 odd undeclared variables.
    UPDATE: In fact this is the same code you posted last week without the use strict line. Aristotle nicely replied with a well written revision that compiles under strict and fixes a lot of the glaring propblems, which you have apparently ignored.
    Now you post the same bad code, with a use strict thrown in just for our benefit and expect us to fix it again. I would suggest that you go back and apply the changes that Aristotle and others took time out to show you instead of wasting your own and everybody elses time.

    "Worry is like a rocking chair. It gives you something to do, but it doesn't get you anywhere."
Re: Replacing a ' in a string
by Aristotle (Chancellor) on Feb 07, 2003 at 13:55 UTC
    I replied to you the first time you asked, with a pretty thorough cleanup. Did you have any problems with that code, and if so what where they?

    Makeshifts last the longest.