view release on metacpan or  search on metacpan

t/test.pl  view on Meta::CPAN

print STDERR "Test metadata...\n";
my @tbls = $dbh->tables;
my $tblcnt = ($#tbls > 10) ? 10 : $#tbls;
print "Partial table listing:\n" if ($tblcnt < $#tbls);
print join("\n", @tbls[0..$tblcnt]), "\n";

$sth = $dbh->table_info;
my $names = $sth->{NAME};
$tblcnt = 0;
while ($row = $sth->fetchrow_arrayref) {
	$tblcnt++;
	last if ($tblcnt > 10);
	print $$names[$_], ': ', (defined($$row[$_]) ? $$row[$_] : 'NULL'), "\n"
		foreach (0..$#$row);
}

my $typeinfo = $dbh->type_info_all() || die "Can't get type info: " . $dbh->errstr . "\n";

my $srvname = $dbh->get_info(13) || die "Can't get_info(SQL_SERVER_NAME): " . $dbh->errstr . "\n";
$srvname = $dbh->get_info(17) || die "Can't get_info(SQL_DBMS_NAME): " . $dbh->errstr . "\n";
die "Invalid DBMS name $srvname\n" unless ($srvname eq 'Teradata');

print STDERR (($tblcnt > 0) ? "Metadata OK.\n" : "Metadata failed.\n");
###################################################
#
#	test large response
#
###################################################
if ($dbh->{tdat_versnum} < 6000000) {
	print STDERR "Large response not supported, skipping...\n";
}
else {
	print STDERR "Test large response...\n";
	my $lsth = $dbh->prepare('select * from dbc.columnsx order by databasename, tablename, columnname')
		or die "Can't prepare large response request: " . $dbh->errstr . "\n";
	$lsth->execute
		or die "Can't execute large response request: " . $lsth->errstr . "\n";
	my $rowcnt = 0;
	my $row;
	while ($row = $lsth->fetchrow_arrayref) {
		print "\r Recv'd $rowcnt rows..."
			unless ++$rowcnt % 100;
	}
	print STDERR "\n$rowcnt rows returned.\n";
	print STDERR "Large response OK.\n";
}
###################################################
#
#	test DDL
#
###################################################
print STDERR "Testing DDL...\n";
$dbh->do( 'DROP TABLE alltypetst');
($dbh->err != 3807) ? die $dbh->errstr : print STDERR $dbh->errstr . "\n"
	if $dbh->err;

my $ctsth = $dbh->prepare( 'CREATE TABLE alltypetst, NO FALLBACK (
col1 integer,
col2 smallint,
col3 byteint,
col4 char(20) character set unicode,
col5 varchar(100) character set unicode,
col6 float,
col7 decimal(2,1),
col8 decimal(4,2),
col9 decimal(8,4),
col10 decimal(14,5),
col11 date,
col12 time,
col13 timestamp(0))
unique primary index(col1);'
) || die ($dbh->errstr . "\n");

$rc = $ctsth->execute;
die ($ctsth->errstr . "\n") unless defined($rc);
###################################################
#
#	test result when update to empty table
#
###################################################
print STDERR "Update empty table...\n";
$rc = $dbh->do('UPDATE alltypetst SET col2 = 23 WHERE col1 = 10');
die ($ctsth->errstr . "\n") unless defined($rc);
print STDERR "Update empty table OK\n";

print STDERR "SHOW TABLE...\n";
$sth = $dbh->prepare('SHOW TABLE alltypetst') || die ($dbh->errstr . "\n");
$rc = $sth->execute;
die ($sth->errstr . "\n") unless defined($rc);
$names = $sth->{NAME};

while ($row = $sth->fetchrow_arrayref() ) {
	foreach (0..$#$row) {
		if (defined($$row[$_])) {
			$$row[$_]=~s/\r/\n/g;
			print "$$names[$_]:\n$$row[$_]\n";
		}
		else {
			print "$$names[$_]: NULL\n";
		}
	}
	print "\n";
}
print STDERR "SHOW TABLE OK\n";

print STDERR "HELP TABLE...\n";
$sth = $dbh->prepare('HELP TABLE alltypetst') || die ($dbh->errstr . "\n");
$rc = $sth->execute;
die ($sth->errstr . "\n") unless defined($rc);
$names = $sth->{NAME};

while ($row = $sth->fetchrow_arrayref() ) {
	print $$names[$_], ': ', (defined($$row[$_]) ? $$row[$_] : 'NULL'), "\n"
		foreach (0..$#$row);
	print "\n";
}
print STDERR "HELP TABLE OK\n";

print STDERR "EXPLAIN...\n";
$sth = $dbh->prepare('EXPLAIN select * from alltypetst') || die ($dbh->errstr . "\n");
$sth->execute or die ($sth->errstr . "\n");

while ($row = $sth->fetchrow_arrayref() ) {
	foreach (@$row) {
		print "NULL\n" and next
			unless defined($_);

		$_=~s/\r/\n/g;
		print $_;
	}
	print "\n";
}
print STDERR "EXPLAIN OK\n";
###################################################
#
#	test MACRO execution
#
###################################################
print STDERR "Testing Macro creation...\n";

$rc = $dbh->do( 'DROP MACRO dbitest');
die $dbh->errstr unless
	(defined($rc) || ($dbh->err == 3824));
#print STDERR $dbh->errstr . "\n" if $dbh->err;

my $cmsth = $dbh->prepare(
'CREATE MACRO dbitest(col1 integer,
col2 smallint,
col3 byteint,
col4 char(20) character set unicode,
col5 varchar(100) character set unicode,
col6 float,
col7 decimal(2,1),
col8 decimal(4,2),
col9 decimal(8,4),
col10 decimal(14,5),
col11 DATE,
col12 TIME,
col13 TIMESTAMP(0)) AS (
INSERT INTO alltypetst VALUES(:col1, :col2, :col3, :col4, :col5, :col6,
:col7, :col8, :col9, :col10, :col11, :col12, :col13);
 /* now read it back */
SELECT * FROM alltypetst; );' ) || die ($dbh->errstr . "\n");
$cmsth->execute or die ($cmsth->errstr . "\n");

print STDERR "DROP/CREATE MACRO ok.\n";
#
#	now test all datatypes as bound params
#	and placeholders
#
print STDERR "Testing multiple prepared statements, placeholders, and explicit commit...\n";
$dbh->{AutoCommit} =  0;

my $isth = $dbh->prepare(
'INSERT INTO alltypetst VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?(time(6)), ?(timestamp(0)))')
	|| die ($dbh->errstr . "\n");
my $dsth = $dbh->prepare( 'DELETE FROM alltypetst') || die ($dbh->errstr . "\n");
$ssth = $dbh->prepare('SELECT * FROM alltypetst ORDER BY col1',
	{ChopBlanks => 1}) || die ($dbh->errstr . "\n");
#
#	insert a row
#
print STDERR "Test explicit param binding...\n";
my @invals = (123456, 1234, 12, 'perl is great', 'okey dokey',
12.34567, 1.2, 12.34, 1234.5678, 123456789.01234,
1021121, '11:21:02.034500', '2002-11-21 11:21:02');

$isth->bind_param(1, 123456) || die ($isth->errstr . "\n");
$isth->bind_param(2, 1234) || die ($isth->errstr . "\n");
$isth->bind_param(3, 12) || die ($isth->errstr . "\n");
$isth->bind_param(4, 'perl is great') || die ($isth->errstr . "\n");
$isth->bind_param(5, 'okey dokey') || die ($isth->errstr . "\n");
$isth->bind_param(6, 12.34567) || die ($isth->errstr . "\n");
$isth->bind_param(7, 1.2) || die ($isth->errstr . "\n");
$isth->bind_param(8, 12.34) || die ($isth->errstr . "\n");
$isth->bind_param(9, 1234.5678) || die ($isth->errstr . "\n");
$isth->bind_param(10, 123456789.01234) || die ($isth->errstr . "\n");
$isth->bind_param(11, '2002-11-21') || die ($isth->errstr . "\n");
$isth->bind_param(12, '11:21:02.0345') || die ($isth->errstr . "\n");
$isth->bind_param(13, '2002-11-21 11:21:02') || die ($isth->errstr . "\n");

$isth->execute or die ($isth->errstr . "\n");
#
#	make sure the returned values are the same
#	as we inserted
#
$names = $ssth->{NAME};
$ssth->execute or die ($ssth->errstr . "\n");
while ($row = $ssth->fetchrow_arrayref() ) {
	foreach (0..$#$row) {
		print $$names[$_], ': ', (defined($$row[$_]) ? $$row[$_] : 'NULL'), "\n";