July 17th, 2013, 04:37 AM
-
How to see only particular table details by using perlscript.
Hi,
I written the below perl sciprt this will give particular employee
table details.But i want to see each time by specifying different table names as inpuT tparametes.Ex:next time i want to see
only customer table details only.So HOW I CAN GET THIS USING PERL SCRIPT.
Please check the attached script for particular table(employee)
DB:SQLSERVER
script:
use DBI;
# DBD::ODBC
my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host = '100.10.100,1433';(---sampleip)
my $database = 'test';(---sampledb name)
my $db_Metadata = 'test';(---for sample)
my $user = 'xx';
my $auth = 'xxxx';
my %hash = ();
# Connect via DBD::ODBC by specifying the DSN dynamically.
my $dbh1 = DBI->connect("$dsn;Server=$host;Database=$database",
$user,
$auth,
{ RaiseError => 1, AutoCommit => 1}
) || die "Database connection not made: $DBI::errstr";
my $tabsql ="select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_name ='employee'\n";
my $sth = $dbh1->prepare( $tabsql );
my $sth = $dbh1->prepare( $tabsql );
#Execute the statement
$sth->execute();
my($TABLE_SCHEMA,$TABLE_NAME,$COLUMN_NAME,$DATA_TYPE,$NUMERIC_PRECISION,$CHARACTER_MAXIMUM_LENGTH,$I S_NULLABLE);
# Bind the results to the local variables
$sth->bind_columns(undef,\$TABLE_SCHEMA,\$TABLE_NAME,\$COLUMN_NAME,\$DATA_TYPE,\$NUMERIC_PRECISION,\$CHAR ACTER_MAXIMUM_LENGTH,\$IS_NULLABLE );
#Retrieve values from the result set
while( $sth->fetch() ) {
print "$TABLE_SCHEMA,$TABLE_NAME,$COLUMN_NAME,$DATA_TYPE,$NUMERIC_PRECISION,$CHARACTER_MAXIMUM_LENGTH,$IS_ NULLABLE\n";
}
$sth->finish();
$dbh1->disconnect();
July 17th, 2013, 08:59 AM
-
Use Placeholders.
Code:
my $table = shift @ARGV; # in this example, tablename is passed to the script
# lets add some readability to the sql statement
my $tabsql = "select TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
NUMERIC_PRECISION,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where table_name = ?";
my $sth = $dbh1->prepare( $tabsql );
#Execute the statement
$sth->execute($table);
Comments on this post