By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Perl Database SQL query variable passing

P: 4
Hi,
I have the following perl script working for me.I am accesing the database from my perl script using use Net::Telnet(); package.
I am not using DBI package.as I stated earlier the following program is printing the output in a nice form.However I want to pass
a variable in the
Expand|Select|Wrap|Line Numbers
  1.  $t->cmd("SELECT * FROM TABLE_NAME WHERE INSTANCE_NAME LIKE '%hostname%' ;");
Here I want hostname to be replaced by a variable name like
Expand|Select|Wrap|Line Numbers
  1. $t->cmd("SELECT * FROM TABLE_NAME WHERE INSTANCE_NAME LIKE '%VARIABLE_NAME%' ;");.
I want just pass a variable in the above sql query so that I can change the VARIABLE_NAME.Please help me out with it.
Thank You
Vivek


Expand|Select|Wrap|Line Numbers
  1. $IPAddress = " ";
  2. $Login = " ";
  3. $password = " ";
  4. $Node = "hostname";
  5.  
  6. use Net::Telnet();
  7. $t= new Net::Telnet (Timeout => 3000 , Prompt => '/[%#\$>?:] $/' );
  8. $t->open("$IPAddress");
  9. print "\nConected!";
  10. $t->waitfor('/login: $/i');
  11. $t->print($Login);
  12. print "\nEntered the Username\n";
  13. $t->waitfor('/assword: $/i');
  14. $t->print($Password);
  15. print "\nEntered the Password!\n";
  16. @output=$t->cmd("export ORACLE_SID=$Node\n");
  17. print @output;
  18. print "\n Done with logging into the database\n";
  19. @output= $t->cmd("bash\n");
  20. print FILE @output;
  21. print @output;
  22. @output = $t->cmd("sqlplus username/pass\n");
  23. print @output;
  24. @output = $t->cmd("conn cusername/pass\n");
  25. print @output;
  26. @output = $t->cmd("SELECT * FROM TABLE_NAME WHERE INSTANCE_NAME LIKE '%hostname%' AND PARAM_KEY_NAME LIKE '%host';");
  27. print FILE @output;
  28. @output =  $t->cmd("exit\n");
  29. print @output;
  30. @output =  $t->cmd("exit\n");
  31. print @output;
Jan 27 '09 #1
Share this Question
Share on Google+
4 Replies


numberwhun
Expert Mod 2.5K+
P: 3,503
@sonu2die4

Why are you using % to specify a variable name? In Perl, a $ is for a scalar variable and a % is for a hash. If you have a $variable that you want to use, then use it in place of %hostname% as that is not correct. This is not dos scripting or any other like it.

So, you can write your statement like so:

Expand|Select|Wrap|Line Numbers
  1. @output = $t->cmd("SELECT * FROM TABLE_NAME WHERE INSTANCE_NAME LIKE '$hostname' AND PARAM_KEY_NAME LIKE '$host';");
  2.  
Also, please take note of the following:

1. Code tags are required around ANY code you post in the forums.
2. Any personal or private information (ie: usernames, passwords, IP addresses, url's, email addresses) are strictly forbidden in the general technical forums. This is for your safety and security and we expect you to please abide by this.

Regards,

Jeff
Jan 27 '09 #2

eWish
Expert 100+
P: 971
The % is a wildcard for MySQL. It can be used like he is using it.

--Kevin
Jan 27 '09 #3

numberwhun
Expert Mod 2.5K+
P: 3,503
@eWish
Ok, but if he is trying to pass a variable from a Perl script, he should pass the variable itself, not using the syntax he is using. I have seen plenty of code where I work (and written some) to use a variable in place of a parameter in the SQL line of the code, but have never used the % in the Perl code.
Jan 28 '09 #4

eWish
Expert 100+
P: 971
Here is some code I wrote a while ago to illustrate how to use a placeholder in the LIKE clause, which could be practiced here as well.
Expand|Select|Wrap|Line Numbers
  1. my $var = 'Car';
  2. my $like_var = '%' . $var . '%';
  3.  
  4. my $test = $dbh->prepare(qq|SELECT name FROM categories WHERE name LIKE ?|);
  5.    $test->execute($like_var);
  6.  
  7.    while (my @rows = $test->fetchrow_array()){
  8.        foreach (@rows) {
  9.           print $_, '<br>' ;
  10.  
  11.        }
  12.    }
  13. $test->finish();
  14.  
  15. my $regex_test = qq|^Car|;
  16.  
  17. my $another_test = $dbh->prepare(qq|SELECT name FROM categories WHERE name REGEXP ?|);
  18.    $another_test->execute($regex_test);
  19.  
  20.       while (my @rows = $another_test->fetchrow_array()){
  21.        foreach (@rows) {
  22.           print $_, '<br>';
  23.        }
  24.    }
  25. $another_test->finish(); 
This way you don't lose the % wildcard and you make safer and don't have to worry about escaping the data. Helps reduce the sql injections.

--Kevin
Jan 28 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.