mysql - 如何在perl中转义表行中的特殊字符

Question

我正在尝试访问名称中有特殊字符的表，有人可以帮忙吗:(

SELECT 
  q.user_data.chat_event_text, q.enq_time
FROM 
  swapp_owner.aq'$'CHAT_EVENT_QUEUE_table q,
  CHAT_EVENT_QUEUE_table p
where 
      q.expiration_reason = 'TIME_EXPIRATION'
  and q.msg_id=p.msg_Id
  and p.enq_time > (SYSDATE - 50000/(24*60))
order by 
  q.enq_time desc;

错误:

Name "main::CHAT_EVENT_QUEUE_table" used only once: possible typo at ./t21 line 30. Use of uninitialized value $CHAT_EVENT_QUEUE_table in concatenation (.) or string at ./t21 line 30. DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare) [for Statement "SELECT q.user_data.chat_event_text, q.enq_time FROM swapp_owner.aq' q,CHAT_EVENT_QUEUE_table p where q.expiration_reason = 'TIME_EXPIRATION' and q.msg_id=p.msg_Id and p.enq_time > (SYSDATE - 50000/(24*60)) order by q.enq_time desc; "] at ./t21 line 30. DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare) [for Statement "SELECT q.user_data.chat_event_text, q.enq_time FROM swapp_owner.aq' q,CHAT_EVENT_QUEUE_table p where q.expiration_reason = 'TIME_EXPIRATION' and q.msg_id=p.msg_Id and p.enq_time > (SYSDATE - 50000/(24*60)) order by q.enq_time desc; "] at ./t21 line 30.

添加完整脚本:

!/usr/bin/perl -w


BEGIN {   $ENV{ORACLE_HOME}='/u01/app/oracle/product/11.1.0/'; } use strict;

use DBI; use utf8;

my $DB='pre14msv'; my $db_user='SWAPP_OWNER'; my $password=`/usr/bin/pmo view password -u $db_user -t $DB`; chomp($password); my $db = DBI->connect( "dbi:Oracle:pre14msv", $db_user, $password )

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;


my $sth = $db->prepare("SELECT q.user_data.chat_event_text, p.enq_time FROM swapp_owner.aq\$\CHAT_EVENT_QUEUE_table q,CHAT_EVENT_QUEUE_table p where q.expiration_reason = 'TIME_EXPIRATION' and q.msg_id=p.msg_Id and p.enq_time > (SYSDATE - 50000/(24*60)) order by q.enq_time desc; "); $sth->execute();

while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n"; }


print "If you see this, execute phase succeeded without a problem.\n";

END {

    $db->disconnect if defined($db); }

Answer 1

在 Perl 中，变量名称内的 ' 与 :: 相同，因此 $'CHAT_EVENT_QUEUE_table 变为 $::CHAT_EVENT_QUEUE_table 是包 main 中的包变量:$main::CHAT_EVENT_QUEUE_table – 除非您没有这样的变量。

如果您希望文字字符串 $'CHAT_EVENT_QUEUE_table 存在，请将 SQL 放入不插入变量的单引号字符串中，例如

my $sth = $db->prepare(q(... SQL here no $'variables ...));

如果由于某种原因您确实需要转义码，请转义 $:\$。