Re: Newline inserted?

Paul,

It seems you must have some program or script that converts
the syslog info into a SQL statement. I'd recommend moving this
processing into your DBI script, i.e.

$insert_statement = $dbh->prepare(
q/INSERT INTO message
(FULLDATE,HOSTNAME,FACILITY,PRIORITY,MESSAGE,SHA1)
VALUES(?, ?, ?, ?, ?, ?)/;

while ( ) {
# parse syslog info into variables
# $fulldate, $hostname, $facility, $priority, $message, $sha1
# including necessary data cleaning, sanity checks, etc

$insert_statement->execute($fulldate, $hostname,
$facility,
$priority, $message, $sha1);
}

Then you don't need to worry about embedded quotes and so on.
Plus your DBA will like you better if you use bind variables.
As for your original question, I'd bet Ted Behling has the right answer:
get rid of the trailing semi-colon.

-Chris


On Oct 24, 2005, at 2:22 PM,
wrote:

> This runs as a CRON job loading syslog data into Oracle so SQL*Plus
> is not a
> good answer. Thanks for help though.
>
> -----Original Message-----
> From: Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com]
>
> If your file has a '\n' in it, it cannot possibly be an issue with
> DBI or
> Oracle v anything.
>
> Are you processing individual SQL statements (does your file
> contain SQL
> insert statements)? If so, DBI is not your best bet to do this. I
> would
> rethink this and:
>
> a. Use SQL*Plus to load the file:
> sqlplus -L /nolog > connect uid/pwd@db_name
> @file
> commit;
> exit
> eof
>
> b. Consider getting a file of data (no SQL statemets) and use
> SQL*Loader to
> load the data.
>
> -----Original Message-----
> From: Ward.P.Fontenot@wellsfargo.com
>
> I have the following sql insert statement in a file:
>
> INSERT INTO message(FULLDATE,HOSTNAME,FACILITY,PRIORITY,MESSAGE,SHA1)
> VALUES('2005-Oct-18 11:53:21','pkitest.wellsfargo.com','auth
> ','info','su(pam_unix)[3638]: session opened for user oracle by
> root(uid=0)','H4vgmf+OJsT99kCfQuIpALY7k9k=');
>
> When I attempt to load this file into Oracle 10g using the DBI I
> get the
> following error:
>
> DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD
> ERROR:
> error possibly near <*> indicator at char 238 in 'INSERT INTO
> message(FULLDATE,HOSTNAME,FACILITY,PRIORITY,MESSAGE,SHA1)
> VALUES('2005-Oct-18
> 11:53:21','pkitest.wellsfargo.com','auth','info','su(pam_uni x)[3638]:
> session opened for user oracle by
> root(uid=0)','H4vgmf+OJsT99kCfQuIpALY7k9k=')<*>;')
>
> This is the code that loads this file:
>
> while () {
> chomp;
> $sql = qq{$_};
>
> $sth = $dbh->prepare($sql); # Should these be outside the loop?
> $sth->execute();
> }
>
> Od -cx FILENAME shows a "\n" character at the <*> point in the above
> file. VI -b does not. Is this a known issue with DBI and 10g or am I
> overlooking something?
>
>