perl DBD::Oracle not using place holders?

From: Amonotod (amonotod_at_charter.net)
Date: 06/25/04 

To: <dbi-users@perl.org>
Date: Fri, 25 Jun 2004 19:42:57 +0000

  I'm using "$DBD::Oracle::VERSION = '1.15' with ActiveState Perl v5.8.0 build 802.

  With the code listed below, I am doing inserts to a database.

# Build my insert statement...
my $insert_start = "insert into $tablemap (";
my $insert_end = ") values (";
my $colcount = 1;
foreach (@cols) {
  $insert_start .= $_;
  $insert_end .= "?";
  if ($colcount < scalar(@cols)) { $insert_start .= ", "; $insert_end .= ", ";}
  $colcount++;
}
$insert_end .= ");";
my $insert_statement = $insert_start . $insert_end;
print "$insert_statement\n";

# Prepare the insert statement, and start inserting the data...
my $DataInsert = $dbh->prepare(Format_SQL($insert_statement));
while (my (@rets) = $Text_sth->fetchrow_array) {
   my @valuelist;
   $colcount = 0;
   foreach my $col (@cols) {
     my $ret = $rets[$colcount];
     if ($ret eq "") { $valuelist[$colcount] = qw /NULL/; }
     else { $valuelist[$colcount] = $ret; }
     $colcount++;
   }
  unless ($DataInsert->execute(@valuelist) ) {
    print LOGFILE "errors: $dbh->errstr \n";
    print LOGFILE "\nErrors were encountered during data load...\n";
  }
}
$DataInsert->finish;

  This all works fine with MSSQL (using DBD::ODBC), and with Sybase (using DBD::Sybase). However, with Oracle it dies on the first insert...

insert into ALERT (Alert_ID, Name, ItemID, SGML_ID, CDM_Type) values (?, ?, ?, ?, ?);
DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 94 in 'insert into Table (ID_Col, Name, ItemID, Alt_ID, C_Type) values (:p1, :p2, :p3, :p4, :p5)<*>;') [for Statement "insert into Table (ID_Col, Name, ItemID, Alt_ID, C_Type) values (?, ?, ?, ?, ?);" with ParamValues: :p5='W', :p3='NULL', :p1='600', :p4='Y48840', :p2='Notice Notice'] at D:\development\dbd_db.pl line 248.

  Hints, tips and suggestions welcome...

Thank you,
amonotod 

-- 
    `\|||/         amonotod@    | subject line: 
      (@@)         charter.net  | no perl, no read...
  ooO_(_)_Ooo________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|

Relevant Pages

  • Re: deleting row from dataset
    ... For example, if I deleted row 10, would row 11 now be pointing at the old row 12? ... BUT only after a resychronisation, NOT immediatly: If it was to simply delete it, from your side, BEFORE making any synchronization with the database, how would ADO Net be aware of that it has to delete it! ... Furthermore, if ever you re-execute again your loop after a first "delete" pass, BEFORE you resynchronize it with the database, you will likely get an error trying to read a field from a row marked as deleted! ... Sure, standard foreach do not allow standard delete, so, maybe it is more "by design" for uniformity, than for real need. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: deleting row from dataset
    ... For example, if I deleted row 10, would row 11 now be pointing at the old ... No, not even for standard list: when you start at index N, toward 1, so ... with the database, how would ADO Net be aware of that it has to delete it! ... I am clueless about why foreach does not allow the delete, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: form post to database best practice?
    ... | info from database then using php doto create elements in form. ... text-align: right; ... foreach ... FROM roLaborCodes ...
    (comp.lang.php)
  • Re: Critique of Robert C. Martins "Agile Principles, Patterns, and Practices"
    ... foreach e in retireableEmployees ... Why ware we sending this particular set of employees the retirement options letter? ... Because we can run the tests on our local laptops without a database. ... I agree that relations are the only needed data structures. ...
    (comp.object)
  • RE: Reorganising Data
    ... A database that has one product per row with information on the product ... I have a spreadsheet with information about each product in a single row. ... fill down with the info in Cols A & B. ... Then cut information from Cols G:J and put that in the second row for that ...
    (microsoft.public.excel.programming)