CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
DBI Interface Problem

 



pdolinski
Novice

Sep 14, 2015, 9:41 AM

Post #1 of 8 (1095 views)
DBI Interface Problem Can't Post

I am trying to bind a variable in what I thought would be a very simple piece of code, but after spending all morning with it, I'm getting nowhere.

my $sth = $dbh->prepare ("grant dba to ?"); ## also tried: my $sth = $dbh->prepare ("grant dba to :1");
$sth->bind_param (1, $usr_name);
$sth->execute ();

Error:
ORA-00987: missing or invalid username(s) (DBD ERROR: error possibly near <*> indicator at char 13 in 'grant dba to <*>:p1')

When I do this:
my $sth = $dbh->prepare ("grant dba to $usr_name");
$sth->execute ();

it works fine.
This is Perl 5.83.

Can you help. Thanks.
Pete


FishMonger
Veteran / Moderator

Sep 14, 2015, 10:27 AM

Post #2 of 8 (1092 views)
Re: [pdolinski] DBI Interface Problem [In reply to] Can't Post

Do you have the latest versions of DBI and DBD::Oracle installed?

Do you have RaiseError enabled?

Is it failing on the prepare or execute statement?

Does it work if you pass $usr_name in the execute statement?

Code
my $sth = $dbh->prepare("grant dba to ?"); 
$sth->execute($usr_name);


Add error handling to both of those statements if you don't have RaiseError enabled?


(This post was edited by FishMonger on Sep 14, 2015, 10:28 AM)


pdolinski
Novice

Sep 14, 2015, 11:24 AM

Post #3 of 8 (1087 views)
Re: [pdolinski] DBI Interface Problem [In reply to] Can't Post

I don't have control over the versions of modules I am stuck using. They are likely old.

perl -MDBD::Oracle -le "print $DBD::Oracle::VERSION"
1.15

perl -MDBI -le "print $DBI::VERSION"
1.42

I enabled RaiseError, but what does that change? The error is still the same.

Uncaught exception from user code:
DBD::Oracle::st execute failed: ORA-00987: missing or invalid username(s) (DBD ERROR: error possibly near <*> indicator at char 13 in 'grant dba to <*>:p1') [for Statement "grant dba to ?" with ParamValues: :p1='toms'] at db_util.pl line 1874.
#... function call chain
Issuing rollback() for database handle being DESTROY'd without explicit disconnect() at db_util.pl line 1874.


>Does it work if you pass $usr_name in the execute statement?
No. Same error.


When comment out $sth->execute (), I get this:

Import: Release 11.2.0.1.0 - Production on Mon Sep 14 14:17:38 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and OLAP options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "TOMS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TOMS"."SYS_SQL_FILE_FULL_01": #...commented out
Job "TOMS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:17:42

Abnormal program termination.


(This post was edited by pdolinski on Sep 14, 2015, 11:26 AM)


Laurent_R
Veteran / Moderator

Sep 15, 2015, 1:36 AM

Post #4 of 8 (1072 views)
Re: [pdolinski] DBI Interface Problem [In reply to] Can't Post

Perhaps we need to step back a bit to see if you can really access the Oracle DB.

Can you run a simple DBI select * from ... where ... command, first with a hard-coded where clause, and then again with a placeholder in that clause?


pdolinski
Novice

Sep 15, 2015, 7:03 AM

Post #5 of 8 (1067 views)
Re: [Laurent_R] DBI Interface Problem [In reply to] Can't Post

Here is how I tested a simple SELECT:

my $query = "select table_name from user_tables where table_name like ?";
my $sth = $dbh->prepare ($query);
$sth->bind_param (1, "MAP%");
$sth->execute () or print ("\nFATAL ERROR: Cannot fetch data. Oracle says:\n" . DBI->errstr) and $dbh-> disconnect () and exit (1);

my $ref_tbls = $sth->fetchall_arrayref ();
push (my @tables, @{$ref_tbls});

foreach my $r (@tables)
{
print ("[@{$r}[0]]");
}

Output:

[MAP_COMPLIST$]
[MAP_ELEMENT$]
[MAP_EXTELEMENT$]
[MAP_FILE$]
[MAP_FILE_EXTENT$]
[MAP_OBJECT]
[MAP_SUBELEMENT$]

So binding works perfectly OK with SELECT.

Which leads me to believe, perhaps this whole binding scheme is not supported for admin queries like GRANT, which is what I am trying to accomplish. Could someone run a test and confirm this (keeping in mind the vintage of my DBI modules)?

Pete


FishMonger
Veteran / Moderator

Sep 15, 2015, 7:57 AM

Post #6 of 8 (1061 views)
Re: [pdolinski] DBI Interface Problem [In reply to] Can't Post

I don't have access to an Oracle db to test this, but I can test it on mysql.

Here's my test:

Code
my $user = 'jeffery'; 
my $sql = "GRANT ALL ON test.* TO ?";

print "preparing sql statement: $sql\n";
my $sth = $dbh->prepare($sql);

print "executing sql statement\n";
$sth->execute($user);

print "disconnecting from mysql\n";
$dbh->disconnect;

print "all good\n"


Output:

Quote
[root@099-91-RKB-2 ~]# ./db_test.pl
Connecting to mysql
preparing sql statement: GRANT ALL ON test.* TO ?
executing sql statement
disconnecting from mysql
all good


I also tested it using bind_pram() and that was also successful.


pdolinski
Novice

Sep 15, 2015, 8:07 AM

Post #7 of 8 (1058 views)
Re: [FishMonger] DBI Interface Problem [In reply to] Can't Post

Thanks for that. But it doesn't prove or disprove anything. We need a test which exercises the Oracle API.


FishMonger
Veteran / Moderator

Sep 15, 2015, 8:25 AM

Post #8 of 8 (1056 views)
Re: [pdolinski] DBI Interface Problem [In reply to] Can't Post

Hopefully someone else has access to Oracle to run a test.

My first recommendation would be that you have your sys admins upgrade the modules. They should also upgrade perl as well. If they won't do any upgrades, then you might need to not use placeholders in these types of statements.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives