How to escape single quotes inside fields but not the ones aroundfields?

Discussion in 'Perl Misc' started by Henry J., Oct 6, 2008.

  1. Henry J.

    Henry J. Guest

    Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    sending to DB as part of insert SQLs.

    Example 1):

    it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
    it''s yours, 12, 42, 2008/10/06

    Example 2):

    'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
    mine', 'it''s yours', 12, 42, '2008/10/06'

    The tricky part is that the data file may or may not have the string
    fields wrapped in single quotes. In Example 1), it is not and single
    quotes around fields will be added by another script before sending to
    DB, in Example 2), its fields are already enclosed in single quotes
    and will be sent to DB as is.

    Does anybody have handy perl one-liner or script that tackles this?
    Thanks!
    Henry J., Oct 6, 2008
    #1
    1. Advertising

  2. Henry J. wrote:
    > Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    > sending to DB as part of insert SQLs.
    >
    > Example 1):
    >
    > it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
    > it''s yours, 12, 42, 2008/10/06
    >
    > Example 2):
    >
    > 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
    > mine', 'it''s yours', 12, 42, '2008/10/06'
    >
    > The tricky part is that the data file may or may not have the string
    > fields wrapped in single quotes. In Example 1), it is not and single
    > quotes around fields will be added by another script before sending to
    > DB, in Example 2), its fields are already enclosed in single quotes
    > and will be sent to DB as is.
    >
    > Does anybody have handy perl one-liner or script that tackles this?
    > Thanks!
    >


    $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?")->execute($baz);



    --
    RGB
    RedGrittyBrick, Oct 6, 2008
    #2
    1. Advertising

  3. Henry J.

    Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:

    >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    >sending to DB as part of insert SQLs.
    >
    > Example 1):
    >
    > it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
    >it''s yours, 12, 42, 2008/10/06
    >
    > Example 2):
    >
    > 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
    >mine', 'it''s yours', 12, 42, '2008/10/06'
    >
    >The tricky part is that the data file may or may not have the string
    >fields wrapped in single quotes. In Example 1), it is not and single
    >quotes around fields will be added by another script before sending to
    >DB, in Example 2), its fields are already enclosed in single quotes
    >and will be sent to DB as is.
    >
    >Does anybody have handy perl one-liner or script that tackles this?
    >Thanks!



    I don't think this can be a one liner, maybe.
    In esscence this does it all:

    sln

    #############
    # Delim Regex
    #############

    use strict;
    use warnings;

    my $str = "
    it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
    it's wants it's gets ";


    while ($str =~ /([^,\n]+)/g)
    {
    my $val = $1;
    $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
    $val =~ s/'/''/g;
    $val = "'$val'";
    print "val = $val\n";
    }


    __END__

    output:

    val_a = 'it''s mine'
    val_a = 'it''s yours'
    val_a = '12'
    val_a = '42'
    val_a = '2008/10/06'
    val_a = 'it''s mine'
    val_a = 'it''s yours'
    val_a = '12'
    val_a = '42'
    val_a = '2008/10/06'
    val_a = 'it''s wants it''s gets'
    , Oct 6, 2008
    #3
  4. Henry J.

    Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    On Mon, 06 Oct 2008 18:25:07 GMT, wrote:

    >On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:
    >
    >>Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    >>sending to DB as part of insert SQLs.
    >>
    >> Example 1):
    >>
    >> it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
    >>it''s yours, 12, 42, 2008/10/06
    >>
    >> Example 2):
    >>
    >> 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
    >>mine', 'it''s yours', 12, 42, '2008/10/06'
    >>
    >>The tricky part is that the data file may or may not have the string
    >>fields wrapped in single quotes. In Example 1), it is not and single
    >>quotes around fields will be added by another script before sending to
    >>DB, in Example 2), its fields are already enclosed in single quotes
    >>and will be sent to DB as is.
    >>
    >>Does anybody have handy perl one-liner or script that tackles this?
    >>Thanks!

    >
    >
    >I don't think this can be a one liner, maybe.
    >In esscence this does it all:
    >
    >sln
    >
    >#############
    ># Delim Regex
    >#############
    >
    >use strict;
    >use warnings;
    >
    >my $str = "
    >it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
    >it's wants it's gets ";
    >
    >
    >while ($str =~ /([^,\n]+)/g)
    >{
    > my $val = $1;
    > $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;


    # just for grins, reconstitute partial fixes.
    # there goes that one-liner possibility

    $val =~ s/'+/'/g;
    #

    > $val =~ s/'/''/g;
    > $val = "'$val'";
    > print "val = $val\n";
    >}
    >
    >
    >__END__
    >
    >output:
    >
    >val_a = 'it''s mine'
    >val_a = 'it''s yours'
    >val_a = '12'
    >val_a = '42'
    >val_a = '2008/10/06'
    >val_a = 'it''s mine'
    >val_a = 'it''s yours'
    >val_a = '12'
    >val_a = '42'
    >val_a = '2008/10/06'
    >val_a = 'it''s wants it''s gets'
    >
    >
    , Oct 6, 2008
    #4
  5. Re: How to escape single quotes inside fields but not the ones around fields?

    >>>>> "HJ" == Henry J <> writes:

    HJ> Need to escape single quotes ( i.e., ' -> '' ) in a data file
    HJ> before sending to DB as part of insert SQLs.

    Use placeholders in your SQL statement, and let the database driver
    handle escaping as necessary. perldoc DBI

    Charlton



    --
    Charlton Wilbur
    Charlton Wilbur, Oct 6, 2008
    #5
  6. Henry J.

    Henry J. Guest

    On Oct 6, 2:25 pm, wrote:
    > On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:
    > >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    > >sending to DB as part of insert SQLs.

    >
    > >       Example 1):

    >
    > >           it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
    > >it''s yours, 12, 42, 2008/10/06

    >
    > >       Example 2):

    >
    > >           'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
    > >mine', 'it''s yours', 12, 42, '2008/10/06'

    >
    > >The tricky part is that the data file may or may not have the string
    > >fields wrapped in single quotes.  In Example 1), it is not and single
    > >quotes around fields will be added by another script before sending to
    > >DB, in Example 2), its fields are already enclosed in single quotes
    > >and will be sent to DB as is.

    >
    > >Does anybody have handy perl one-liner or script that tackles this?
    > >Thanks!

    >
    > I don't think this can be a one liner, maybe.
    > In esscence this does it all:
    >
    > sln
    >
    > #############
    > # Delim Regex
    > #############
    >
    > use strict;
    > use warnings;
    >
    > my $str = "
    > it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12,42, '2008/10/06',
    > it's wants it's gets ";
    >
    > while ($str =~ /([^,\n]+)/g)
    > {
    >         my $val = $1;
    >         $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
    >         $val =~ s/'/''/g;
    >         $val = "'$val'";
    >         print "val = $val\n";
    >
    > }
    >
    > __END__
    >
    > output:
    >
    > val_a = 'it''s mine'
    > val_a = 'it''s yours'
    > val_a = '12'
    > val_a = '42'
    > val_a = '2008/10/06'
    > val_a = 'it''s mine'
    > val_a = 'it''s yours'
    > val_a = '12'
    > val_a = '42'
    > val_a = '2008/10/06'
    > val_a = 'it''s wants it''s gets'- Hide quoted text -
    >
    > - Show quoted text -


    Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
    cannot add or remove enclosing single quotes).
    Henry J., Oct 6, 2008
    #6
  7. Henry J.

    Dr.Ruud Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    Henry J. schreef:

    > Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    > sending to DB as part of insert SQLs.


    No, you don't.

    --
    Affijn, Ruud

    "Gewoon is een tijger."
    Dr.Ruud, Oct 6, 2008
    #7
  8. Henry J.

    Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <> wrote:

    >On Oct 6, 2:25 pm, wrote:
    >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:
    >> >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    >> >sending to DB as part of insert SQLs.

    >>
    >> >       Example 1):

    >>
    >> >           it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
    >> >it''s yours, 12, 42, 2008/10/06

    >>
    >> >       Example 2):

    >>
    >> >           'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
    >> >mine', 'it''s yours', 12, 42, '2008/10/06'

    >>
    >> >The tricky part is that the data file may or may not have the string
    >> >fields wrapped in single quotes.  In Example 1), it is not and single
    >> >quotes around fields will be added by another script before sending to
    >> >DB, in Example 2), its fields are already enclosed in single quotes
    >> >and will be sent to DB as is.

    >>
    >> >Does anybody have handy perl one-liner or script that tackles this?
    >> >Thanks!

    >>
    >> I don't think this can be a one liner, maybe.
    >> In esscence this does it all:
    >>
    >> sln
    >>
    >> #############
    >> # Delim Regex
    >> #############
    >>
    >> use strict;
    >> use warnings;
    >>
    >> my $str = "
    >> it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
    >> it's wants it's gets ";
    >>
    >> while ($str =~ /([^,\n]+)/g)
    >> {
    >>         my $val = $1;
    >>         $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
    >>         $val =~ s/'/''/g;
    >>         $val = "'$val'";
    >>         print "val = $val\n";
    >>
    >> }
    >>
    >> __END__
    >>
    >> output:
    >>
    >> val_a = 'it''s mine'
    >> val_a = 'it''s yours'
    >> val_a = '12'
    >> val_a = '42'
    >> val_a = '2008/10/06'
    >> val_a = 'it''s mine'
    >> val_a = 'it''s yours'
    >> val_a = '12'
    >> val_a = '42'
    >> val_a = '2008/10/06'
    >> val_a = 'it''s wants it''s gets'- Hide quoted text -
    >>
    >> - Show quoted text -

    >
    >Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
    >cannot add or remove enclosing single quotes).


    Without at least removeing enclosing quotes, the inner quote would be hard to
    substitute. Not impossible, but harder. Its easy to test if there is enclosing
    quote (then add it back later), but it still has to be removed before doing the
    inner quote substitution.

    Either way it can be done. How did you do it?

    Thanks!

    sln
    , Oct 6, 2008
    #8
  9. Henry J.

    Henry J. Guest

    On Oct 6, 3:48 pm, wrote:
    > On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <> wrote:
    > >On Oct 6, 2:25 pm, wrote:
    > >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:
    > >> >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
    > >> >sending to DB as part of insert SQLs.

    >
    > >> >       Example 1):

    >
    > >> >           it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
    > >> >it''s yours, 12, 42, 2008/10/06

    >
    > >> >       Example 2):

    >
    > >> >           'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
    > >> >mine', 'it''s yours', 12, 42, '2008/10/06'

    >
    > >> >The tricky part is that the data file may or may not have the string
    > >> >fields wrapped in single quotes.  In Example 1), it is not and single
    > >> >quotes around fields will be added by another script before sending to
    > >> >DB, in Example 2), its fields are already enclosed in single quotes
    > >> >and will be sent to DB as is.

    >
    > >> >Does anybody have handy perl one-liner or script that tackles this?
    > >> >Thanks!

    >
    > >> I don't think this can be a one liner, maybe.
    > >> In esscence this does it all:

    >
    > >> sln

    >
    > >> #############
    > >> # Delim Regex
    > >> #############

    >
    > >> use strict;
    > >> use warnings;

    >
    > >> my $str = "
    > >> it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
    > >> it's wants it's gets ";

    >
    > >> while ($str =~ /([^,\n]+)/g)
    > >> {
    > >>         my $val = $1;
    > >>         $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
    > >>         $val =~ s/'/''/g;
    > >>         $val = "'$val'";
    > >>         print "val = $val\n";

    >
    > >> }

    >
    > >> __END__

    >
    > >> output:

    >
    > >> val_a = 'it''s mine'
    > >> val_a = 'it''s yours'
    > >> val_a = '12'
    > >> val_a = '42'
    > >> val_a = '2008/10/06'
    > >> val_a = 'it''s mine'
    > >> val_a = 'it''s yours'
    > >> val_a = '12'
    > >> val_a = '42'
    > >> val_a = '2008/10/06'
    > >> val_a = 'it''s wants it''s gets'- Hide quoted text -

    >
    > >> - Show quoted text -

    >
    > >Thanks a lot!  I'm able to adapt it a bit to solve my problem (e.g., I
    > >cannot add or remove enclosing single quotes).

    >
    > Without at least removeing enclosing quotes, the inner quote would be hard to
    > substitute. Not impossible, but harder. Its easy to test if there is enclosing
    > quote (then add it back later), but it still has to be removed before doing the
    > inner quote substitution.
    >
    > Either way it can be done. How did you do it?
    >
    > Thanks!
    >
    > sln- Hide quoted text -
    >
    > - Show quoted text -


    I replace it with a string and then replace it back.

    BTW, i cannot use the placeholders to handle strings because this is a
    generic function in a perl lib that works like bcp in sybase (i.e.,
    loading a given data file into any given table in DB).
    Henry J., Oct 6, 2008
    #9
  10. Henry J.

    Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    On Mon, 6 Oct 2008 12:58:14 -0700 (PDT), "Henry J." <> wrote:

    >On Oct 6, 3:48 pm, wrote:
    >> On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <> wrote:
    >> >On Oct 6, 2:25 pm, wrote:
    >> >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:

    >
    >I replace it with a string and then replace it back.
    >
    >BTW, i cannot use the placeholders to handle strings because this is a
    >generic function in a perl lib that works like bcp in sybase (i.e.,
    >loading a given data file into any given table in DB).


    Ok, workarounds are acceptable.. good luck.
    Btw, I used to do alot of ADO back in the day. In need of a job if you know of any.

    sln
    , Oct 6, 2008
    #10
  11. Henry J.

    Henry J. Guest

    On Oct 6, 4:06 pm, wrote:
    > On Mon, 6 Oct 2008 12:58:14 -0700 (PDT), "Henry J." <> wrote:
    > >On Oct 6, 3:48 pm, wrote:
    > >> On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <> wrote:
    > >> >On Oct 6, 2:25 pm, wrote:
    > >> >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:

    >
    > >I replace it with a string and then replace it back.

    >
    > >BTW, i cannot use the placeholders to handle strings because this is a
    > >generic function in a perl lib that works like bcp in sybase (i.e.,
    > >loading a given data file into any given table in DB).

    >
    > Ok, workarounds are acceptable.. good luck.
    > Btw, I used to do alot of ADO back in the day. In need of a job if you know of any.
    >
    > sln


    Sorry I'm in NYC and you know how rough it is around here. Good luck.
    Henry J., Oct 7, 2008
    #11
  12. Henry J.

    Peter Scott Guest

    Re: How to escape single quotes inside fields but not the ones around fields?

    On Mon, 06 Oct 2008 12:58:14 -0700, Henry J. wrote:
    > BTW, i cannot use the placeholders to handle strings because this is a
    > generic function in a perl lib that works like bcp in sybase (i.e.,
    > loading a given data file into any given table in DB).


    I'm unconvinced. Sure, you can't use placeholders for table names or
    column names, but you don't typically get apostrophes in those. At some
    point you are generating an INSERT statement with a set of values and I
    do not see why you can't build something that uses placeholders, unless
    this can't use DBI at all.

    --
    Peter Scott
    http://www.perlmedic.com/
    http://www.perldebugged.com/
    Peter Scott, Oct 7, 2008
    #12
  13. Henry J.

    Henry J. Guest

    On Oct 7, 7:19 am, Peter Scott <> wrote:
    > On Mon, 06 Oct 2008 12:58:14 -0700, Henry J. wrote:
    > > BTW, i cannot use the placeholders to handle strings because this is a
    > > generic function in a perl lib that works like bcp in sybase (i.e.,
    > > loading a given data file into any given table in DB).

    >
    > I'm unconvinced.  Sure, you can't use placeholders for table names or
    > column names, but you don't typically get apostrophes in those.  At some
    > point you are generating an INSERT statement with a set of values and I
    > do not see why you can't build something that uses placeholders, unless
    > this can't use DBI at all.
    >
    > --
    > Peter Scotthttp://www.perlmedic.com/http://www.perldebugged.com/


    What I'm trying here is to fix a problem in the legacy code. Perhaps
    I should consider writing a new function to do what you describe and
    ask everybody to use it going forward. Thanks.
    Henry J., Oct 7, 2008
    #13
  14. Henry J.

    Henry J. Guest

    On Oct 7, 9:26 am, "Henry J." <> wrote:
    > On Oct 7, 7:19 am, Peter Scott <> wrote:
    >
    > > On Mon, 06 Oct 2008 12:58:14 -0700, Henry J. wrote:
    > > > BTW, i cannot use the placeholders to handle strings because this is a
    > > > generic function in a perl lib that works like bcp in sybase (i.e.,
    > > > loading a given data file into any given table in DB).

    >
    > > I'm unconvinced.  Sure, you can't use placeholders for table names or
    > > column names, but you don't typically get apostrophes in those.  At some
    > > point you are generating an INSERT statement with a set of values and I
    > > do not see why you can't build something that uses placeholders, unless
    > > this can't use DBI at all.

    >
    > > --
    > > Peter Scotthttp://www.perlmedic.com/http://www.perldebugged.com/

    >
    > What I'm trying here is to fix a problem in the legacy code.  Perhaps
    > I should consider writing a new function to do what you describe and
    > ask everybody to use it going forward.  Thanks.


    OK, one problem pops up trying to use placeholders. We are using DB2
    which supports insert statements with multiple value tuples, like
    this:

    insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7, v8,
    v9), ....

    In our lib, such an intert statement is being built on the fly based
    on a parameter specifiying the number of value tuples (usually
    1,000). The multiple values speed up the inserts a lot.

    Now in order to use placeholders, I need to build SQL like


    insert into myTable values(?, ?, ?), (?, ?, ?), (?, ?, ?), ....
    (up to 1,000 tuples)

    and then bind the values into the 3,000 placeholders. Not sure about
    the performance impact. I may run some tests. But sharing of any
    experience on this would be appreciated.
    Henry J., Oct 7, 2008
    #14
  15. Henry J.

    Ben Morrow Guest

    Quoth "Henry J." <>:
    >
    > OK, one problem pops up trying to use placeholders. We are using DB2
    > which supports insert statements with multiple value tuples, like
    > this:
    >
    > insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7, v8,
    > v9), ....
    >
    > In our lib, such an intert statement is being built on the fly based
    > on a parameter specifiying the number of value tuples (usually
    > 1,000). The multiple values speed up the inserts a lot.
    >
    > Now in order to use placeholders, I need to build SQL like
    >
    >
    > insert into myTable values(?, ?, ?), (?, ?, ?), (?, ?, ?), ....
    > (up to 1,000 tuples)
    >
    > and then bind the values into the 3,000 placeholders. Not sure about
    > the performance impact. I may run some tests. But sharing of any
    > experience on this would be appreciated.


    You may find that using placeholders makes the performance problem go
    away. You only need to prepare the statement once (with the placeholders
    in), and that is usually what takes the time. Then you can execute it
    1,000 times with different bind values, which should be relatively fast.

    Ben

    --
    #!/bin/sh
    quine="echo 'eval \$quine' >> \$0; echo quined"
    eval $quine
    # []
    Ben Morrow, Oct 7, 2008
    #15
  16. Re: How to escape single quotes inside fields but not the ones around fields?

    >>>>> "HJ" == Henry J <> writes:

    HJ> OK, one problem pops up trying to use placeholders. We are
    HJ> using DB2 which supports insert statements with multiple value
    HJ> tuples, like this:

    HJ> insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7,
    HJ> v8, v9), ....

    HJ> In our lib, such an intert statement is being built on the fly
    HJ> based on a parameter specifiying the number of value tuples
    HJ> (usually 1,000). The multiple values speed up the inserts a
    HJ> lot.

    I wouldn't dismiss placeholders before running a benchmark. Much of the
    delay in your approach may come from parsing the SQL and preparing the
    execution plan; if you prepare a statement and execute it multiple times
    with different bound values, the statement will only need to be parsed
    and prepared once, and this will eliminate a lot of the overhead.

    Of course, it may be a quirk of DB2, which I've never used; but it holds
    true for Oracle, Informix, MySQL, and Postgres, the databases I have used.

    Charlton





    --
    Charlton Wilbur
    Charlton Wilbur, Oct 7, 2008
    #16
  17. Re: How to escape single quotes inside fields but not the onesaround fields?

    On 2008-10-07 15:08, Ben Morrow <> wrote:
    >
    > Quoth "Henry J." <>:
    >>
    >> OK, one problem pops up trying to use placeholders. We are using DB2
    >> which supports insert statements with multiple value tuples, like
    >> this:
    >>
    >> insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7, v8,
    >> v9), ....
    >>
    >> In our lib, such an intert statement is being built on the fly based
    >> on a parameter specifiying the number of value tuples (usually
    >> 1,000). The multiple values speed up the inserts a lot.
    >>
    >> Now in order to use placeholders, I need to build SQL like
    >>
    >>
    >> insert into myTable values(?, ?, ?), (?, ?, ?), (?, ?, ?), ....
    >> (up to 1,000 tuples)
    >>
    >> and then bind the values into the 3,000 placeholders. Not sure about
    >> the performance impact. I may run some tests. But sharing of any
    >> experience on this would be appreciated.

    >
    > You may find that using placeholders makes the performance problem go
    > away. You only need to prepare the statement once (with the placeholders
    > in), and that is usually what takes the time. Then you can execute it
    > 1,000 times with different bind values, which should be relatively fast.


    If DBD::DB2 has proper support for execute_array, you don't even have to
    execute it 1000 times - you can execute it once with the data for all
    thousand rows. At least for Oracle this is a lot faster than doing lots
    of single row inserts.

    hp
    Peter J. Holzer, Oct 8, 2008
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris
    Replies:
    1
    Views:
    13,628
    Oisin
    Mar 24, 2006
  2. Leif B. Kristensen

    escape single and double quotes

    Leif B. Kristensen, Mar 24, 2005, in forum: Python
    Replies:
    7
    Views:
    36,255
    Kent Johnson
    Mar 24, 2005
  3. Mausam
    Replies:
    4
    Views:
    863
  4. Replies:
    10
    Views:
    1,195
    Anno Siegel
    Apr 17, 2006
  5. Replies:
    2
    Views:
    124
    slebetman
    Jul 7, 2008
Loading...

Share This Page