Hyphens causing SQL problems in DBI

Question

I have an SQL query with a WHERE clause that usually has values, including dashes stored in the database, like CHAR (10). When I explicitly call it this way:

$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = 'A-50C'");

It works and returns my 1 row correctly; however, if I do the following:

my $code = 'A-50C';
$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = ?");
$sth->execute($code);

or me:

my $code = 'A-50C';
$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = ?");
$sth->bind_param(1, $code);
$sth->execute();

The request is complete, but I am not getting any results. I suspect this is due to a misinterpretation of the dash, but I cannot relate it to the Perl problem since I printed my $ code variable using print "My Content: $code\n";to confirm that it was not weirdly converted. I also tried to include the third value for bind_param, and if I specify something like ORA_VARCHAR2, SQL_VARCHAR (tried all the possibilities), I still do not get any results. If I change it to a long form, that is {TYPE => SQL_VARCHAR}, this will give me an error

DBI :: st = HASH <0x232a210> → bind_param (...): parameter attribute "SQL_VARCHAR" is not a ref hash

, -, , , 1 , 0. ? . .

:

my $dbh = DBI->connect($dsn, $user, $pw, {PrintError => 0, RaiseError => 0})
  or die "$DBI::errstr\n";

# my $dbh = DBI->connect(); # connect

my $code = 'A-50C';
print "My Content: $code\n";
$sth = $dbh->prepare( "SELECT COUNT(*) FROM MyTable WHERE CODE = ?" )
  or die "Can't prepare SQL statement: $DBI::errstr\n";
$sth->bind_param(1, $code);
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";

my $outfile = 'output.txt';
open OUTFILE, '>', $outfile or die "Unable to open $outfile: $!";

while(my @re = $sth->fetchrow_array) {
    print OUTFILE @re,"\n";
}

warn "Data fetching terminated early by error: $DBI::errstr\n"
  if $DBI::err;

close OUTFILE;

$sth->finish();
$dbh->disconnect();

:

-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x22fbcc0)~0x3bcf48 2 'A-50C' HASH(0x22fbac8)) thr#3b66c8
dbd_bind_ph(1): bind :p2 <== 'A-50C' (type 0 (DEFAULT (varchar)), attribs: HASH(0x22fbac8))
dbd_rebind_ph_char() (1): bind :p2 <== 'A-50C' (size 5/16/0, ptype 4(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :p2 <== ''A-50' (size 5/16, otype 1(VARCHAR), indp 0, at_exec 1)
    bind :p2 as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :p2 <== 'A-50C' (in, not-utf8, csid 178->0->178, ftype 1 (VARCHAR), csform 0(0)->0(0), maxlen 16, maxdata_size 0)

Answer 1

, , CHAR VARCHAR.

CHAR ( ), . . , ACC_TYPE, 10 . , , A-50C, 10 , A-50C_____ ( _ ).

, , , Oracle (A-50C → A-50C_____). , , , VARCHAR CHAR, .

:

SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = rpad(?, 10)

CHAR VARCHAR2.

Answer 2

DBI_TRACE, ACC_TYPE CHAR (10), VARCHAR.

CHAR, NCHAR . (, CHAR (10) , ACC_TYPE 10 .) , 'A ' 'A', CHAR, . , VARCHAR, , 'A ' "A", VARCHAR.

sqlplus DBI:

SELECT COUNT(1) FROM DUAL WHERE CAST('A' AS CHAR(2)) = 'A'; -- or CAST AS CHAR(whatever)
SELECT COUNT(1) FROM DUAL WHERE CAST('A' AS CHAR(2)) = CAST('A' AS VARCHAR(1));

(Oracle , ANSI-92 , CHAR , . , , , , - .)

@MickMnemonic , RPAD() , , VARCHAR. CAST(? AS CHAR(10)). TRIM (TRAILING FROM ACC_TYPE) .