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

Perl Database SQL query variable passing

P: 4
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 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
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

Expand|Select|Wrap|Line Numbers
  1. $IPAddress = " ";
  2. $Login = " ";
  3. $password = " ";
  4. $Node = "hostname";
  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

Expert Mod 2.5K+
P: 3,503

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';");
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.


Jan 27 '09 #2

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

Jan 27 '09 #3

Expert Mod 2.5K+
P: 3,503
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

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 . '%';
  4. my $test = $dbh->prepare(qq|SELECT name FROM categories WHERE name LIKE ?|);
  5.    $test->execute($like_var);
  7.    while (my @rows = $test->fetchrow_array()){
  8.        foreach (@rows) {
  9.           print $_, '<br>' ;
  11.        }
  12.    }
  13. $test->finish();
  15. my $regex_test = qq|^Car|;
  17. my $another_test = $dbh->prepare(qq|SELECT name FROM categories WHERE name REGEXP ?|);
  18.    $another_test->execute($regex_test);
  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.

Jan 28 '09 #5

Post your reply

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