Of course these kinds of things should all be done using the standard
DBI metadata methods if the driver supports them, or else by fixing
the driver to support them so everyone gains :-)
Tim.
On Wed, Jun 11, 2003 at 09:14:22AM -0500, Brian P. Millett wrote:
> This is a function that works for informix. You mileage will vary.
>
>
> # This function queries the database about a table. Returns three values:
> # 1) The strings that represent how to reconstruct the
> # table. IE: "biblio_id CHAR (7),"
> # 2) The number of columns in the table.
> # 3) The array of column names.
> sub get_columns_defs {
> my ($sth, $table) = @_;
> my @row,$col_name,$type_name,$col_len,@results,$i,@variableArray,$nulls;
> @results = ();
> $i=0;
> #print "Getting table schema for $table\n";
> $sth->execute($table);
> while (@row = $sth->fetchrow_array ) {
> $i++;
> $nulls = ($row[1] > 255) ? " NOT NULL" : "";
> $row[1] -= 256 if ($row[1] > 255);
> $col_name = $row[0];
> $type_name = $typ{$row[1]};
> $col_len = $row[2];
> if ($type_name eq 'CHAR' ||
> $type_name eq 'VARCHAR'||
> $type_name eq 'NCHAR'||
> $type_name eq 'NVARCHAR') {
> push @results, sprintf "\t%-30s\t%-8s(%d)%s"
> , $col_name,$type_name,$col_len,$nulls ;
> } else {
> push @results, sprintf "\t%-30s\t%-8s%s"
> , $col_name,$type_name,$nulls ;
> }
> push @variableArray, $col_name;
> }
> return (join(",\n", @results),$i,@variableArray);
> }
>
>
> my $dbh = DBI->connect("dbi:Informix:$database", $user, $pass);
>
> $dbh->{PrintError}=1;
> $dbh->{RaiseError}=1;
> $dbh->{ChopBlanks} = 1;
>
> # Query the database for column names, types & length of each
> # column in a specific table.
> my $stmnt = $dbh->prepare("
> SELECT colname col_name, coltype type_name, collength col_len
> FROM systables, syscolumns
> WHERE systables.tabid = syscolumns.tabid
> AND tabname = ?
> ");
>
> #This function call returns three arguments:
> # 1) schema = the strings that represent how to reconstruct the
> # table. IE: "biblio_id CHAR (7),"
> # 2) numberOfVariables = the number of columns in the table.
> # 3) variableArray = the array of column names.
> ($schema,$numberOfVariables,@variableArray) = &get_columns_defs($stmnt,
> $table);
>
>
> --
> Brian Millett
> Enterprise Consulting Group "Shifts in paradigms
> (314) 205-9030 often cause nose bleeds."
> [EMAIL PROTECTED] Greg Glenn
>
