[Phoenix-pm] PERL DBI

[Brock]

Yes.

What you need to use is "bind values" as described in perldoc DBI.

You can do something like:

  $sql = "INSERT INTO theTable (DLVRB_GID, PRC_REL_GID, PRDCT_GID,
  BIL_PYMT_TYP_CDE, PRJTD_TRX_CNT) VALUES (?, ?, ?, ?, ?)";

  # Execute the statement, binding the '?' values
  $d_dbh->do($sql, undef, $dlvrb_gid, $prc_rel_gid, $prdct_gid,
    $bil_pymt_type_cde, $prjtd_trx_cnt);


and can otherwise buld up the statement dynamically too. You can do
something similar with SELECT statements, passing the parameters (things
to replace the '?' marks) into the $d_sth->execute(...) command.

Let us know if you need a more detailed or specific example.

--Brock

On 2006.03.13.10.03, Loo, Peter # PHX wrote:
|    Hi,
| 
|    Not sure if this question is appropriate for this group, but here it is.
| 
|    I am using PERL DBI to SELECTing all columns from a table on Neteeza
|    database and am INSERTing into an existing table on Oracle.  However, some
|    of the columns are character based so they require ("") quotes around the
|    column data to insert.  I need to dynamically built the INSERT values.  Is
|    there an easy way to quote the column data for the INSERT statement.  Here
|    are some examples what I am trying to do:
| 
|        $d_sth = $d_dbh->prepare("select column_name, data_type
|                                  from   all_tab_columns
|                                  where  table_name like upper('%$d_tblName%')
|                                ") || die "$DBI::errstr\n";
| 
|        $d_sth->execute() || die "$DBI::errstr\n";
|        while ( $d_arrayref = $d_sth->fetchrow_arrayref() ) {
|          if ( @$d_arrayref[1] =~ /CHAR|DATE/ ) {
|            $columnsToAssign{ @$_arrayref[0] } = "0";
|            }
|          }
| 
|        $s_sth = $s_dbh->prepare("select * from $s_tblName") || die
|    "$DBI::errstr\n";
|        $s_sth->execute() || die "$DBI::errstr\n";
|        while ( $s_hashref = $s_sth->fetchrow_hashref ) {
| 
|     Name                                      Null?    Type
|     ----------------------------------------- --------
|    ----------------------------
|     DLVRB_GID                                 NOT NULL NUMBER(10)
|     PRC_REL_GID                               NOT NULL NUMBER(10)
|     PRDCT_GID                                 NOT NULL NUMBER(10)
|     BIL_PYMT_TYP_CDE                          NOT NULL CHAR(1)
|     PRJTD_TRX_CNT                             NOT NULL NUMBER(10)
| 
|    The column above (BIL_PYMT_TYP_CDE) require quotes for the INSERT
|    statement.  I would like to dynamically create the INSERT statements like
|    so:
| 
|            $columnsToAssign = $columnsToAssign . ", \"" . @$d_arrayref[0] .
|    "\"";
|            }
|          else {
|            $columnsToAssign = $columnsToAssign . ", " . @$d_arrayref[0];
|            }
|    Is there an easier way?
| 
|    Thanks.
| 
|    Peter Loo
| 
| 
| 
|    This E-mail message is for the sole use of the intended recipient(s) and
|    may contain confidential and privileged information. Any unauthorized
|    review, use, disclosure or distribution is prohibited. If you are not the
|    intended recipient, please contact the sender by reply E-mail, and destroy
|    all copies of the original message.

| _______________________________________________
| Phoenix-pm mailing list
| Phoenix-pm at pm.org
| http://mail.pm.org/mailman/listinfo/phoenix-pm