Perl Database functions

[dlabdo]

I'm writing a script that will generate insert statements for database tables. The only thing holding me back from making this work is finding out what types the columns are. If i know what type they are i can correctly format the values in my insert statement. is there a way to get the type of a specific column name?

ex:
#column's actual value
$val = getValue("AGE_COL");

#getType() is what im looking for
$colType = getType("AGE_COL");

if (isNumeric($colType)) #isNumeric is just a hunch
{
$valuesClause = $valuesClause + ", " + $val;
}
elsif (isText($colType)) #isText is just a hunch
{
$valuesClause = $valuesClause + ", \'" + $val + "\'";
}
...etc...

 

[Rieekan]

Maybe this could help you.

column_info

$sth = $dbh->column_info( $catalog, $schema, $table, $column );

Returns an active statement handle that can be used to fetch information about columns in specified tables.

The arguments $schema, $table and $column may accept search patterns according to the database/driver, for example: $table = '%FOO%';

Note: The support for the selection criteria is driver specific. If the driver doesn't support one or more of them then you may get back more than you asked for and can do the filtering yourself.

The statement handle returned has at least the following fields in the order shown below. Other fields, after these, may also be present.

TABLE_CAT: The catalog identifier. This field is NULL (undef) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table.

TABLE_SCHEM: The schema identifier. This field is NULL (undef) if not applicable to the data source, and empty if not applicable to the table.

TABLE_NAME: The table identifier. Note: A driver may provide column metadata not only for base tables, but also for derived objects like SYNONYMS etc.

COLUMN_NAME: The column identifier.

DATA_TYPE: The concise data type code.

TYPE_NAME: A data source dependent data type name.

COLUMN_SIZE: The column size. This is the maximum length in characters for character data types, the number of digits or bits for numeric data types or the length in the representation of temporal types. See the relevant specifications for detailed information.

BUFFER_LENGTH: The length in bytes of transferred data.

DECIMAL_DIGITS: The total number of significant digits to the right of the decimal point.

NUM_PREC_RADIX: The radix for numeric precision. The value is 10 or 2 for numeric data types and NULL (undef) if not applicable.

NULLABLE: Indicates if a column can accept NULLs. The following values are defined:

SQL_NO_NULLS 0
SQL_NULLABLE 1
SQL_NULLABLE_UNKNOWN 2

REMARKS: A description of the column.

COLUMN_DEF: The default value of the column.

SQL_DATA_TYPE: The SQL data type.

SQL_DATETIME_SUB: The subtype code for datetime and interval data types.

CHAR_OCTET_LENGTH: The maximum length in bytes of a character or binary data type column.

ORDINAL_POSITION: The column sequence number (starting with 1).

IS_NULLABLE: Indicates if the column can accept NULLs. Possible values are: 'NO', 'YES' and ''.

SQL/CLI defines the following additional columns:

CHAR_SET_CAT
CHAR_SET_SCHEM
CHAR_SET_NAME
COLLATION_CAT
COLLATION_SCHEM
COLLATION_NAME
UDT_CAT
UDT_SCHEM
UDT_NAME
DOMAIN_CAT
DOMAIN_SCHEM
DOMAIN_NAME
SCOPE_CAT
SCOPE_SCHEM
SCOPE_NAME
MAX_CARDINALITY
DTD_IDENTIFIER
IS_SELF_REF

Drivers capable of supplying any of those values should do so in the corresponding column and supply undef values for the others.

Drivers wishing to provide extra database/driver specific information should do so in extra columns beyond all those listed above, and use lowercase field names with the driver-specific prefix (i.e., 'ora_...'). Applications accessing such fields should do so by name and not by column number.

The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.

Note: There is some overlap with statement attributes (in perl) and SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.

See also Catalog Methods and Standards Reference Information.

- Rieekan

 

[dlabdo]

that definitely works but im having trouble understanding the sth object and its available methods. is there an online resource i can use for these questions i have about sth???

 

[Rieekan]

Sure, from a unix prompt, you can just type 'perlman dbi' to get the Perl DBI documentation.

- Rieekan

 

[dlabdo]

One more question,

How can i go about asking for a specific table and getting back a list of all of its columns in perl?

thanks for all your help.

 

[stevexff]

Depending on your DBMS, it may support a set of views called INFORMATION_SCHEMA.*

These are supposed to be an SQL standard way of getting schema metadata out of databases, e.g.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'whatever';

 

[dlabdo]

thanks stevexff,

I also found that i could use the same column_info call and leave the $column argument as undef. if you do that you get a hash of all the columns and their attributes.

 

[fishiface]

STOP!

If the only reason you want to find out the column type is to correctly format an insert, then you can relax. If you use placeholders in your insert statement (all grown-up databases support this) then DBI will take care of all of this for you.

my $q_ins = $dbh->prepare( q{
 insert into mytable ( eek, ank, ork )
 values ( ?, ?, ? )
} ) or die $DBI::errstr;
foreach my $record( @whatever ) {
 my( $eek, $ank, $ork ) = @$_;
 $q_ins->execute( $eek, $ank, $ork ) == 1 or warn "Oops"
}

Yours,

fish

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur

 

[dlabdo]

fish,

thank you for the help but i guess i didnt explain enough. I am using an application that leaves a log file behind with all of its sql statements. The insert statements in this log are not formatted in correct sql syntax. basically i want my program to do this...

1. i give the perl script the log file.
2. it graps all of the inserts.
3. goes through each insert and rebuilds it correctly.

I have done 1 and 2. but to format each column correctly within the VALUES section of the statement i need to find out what each column actually is. here is my method of doing this...

1. i have an array of tables that im looking for inserts for in the beginning. i will run a column_info call on each and get the table_name, column_name, and data_type from them all.
2. store all of these in an array/hash
3. when i rebuild my inserts i will tokenize and go through each column name, look it up on my array/hash and format it correctly for the values section of the insert.

phew....does that sound like a good plan???

 

[MikeLacey]

Yes, it does actually.

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[fishiface]

I think that you could still benefit from using placeholders. For example, if your log contained

INSERT INTO sometable ( id, name, tel, )
VALUES ( 1001, Smith, 01217806200 );

then you could parse and re-execute it like this

sub parse_and_execute {
   my $sql = $_[0];
   my( $pre, $vals, $post ) = $sql =~ /
            (^                  # Capture from start..
               .*VALUES \s* \(  # ..up to & inc 'values (' ..
            )                   # ..to $1
            (                   # Capture..
               [^\)]+           # ..up to a ')' ..
            )                   # ..to $2
            ( .* ) $            # Everything else to $3
      /isx; # ignore case, '.' matches newlines, comments
   my @vals = split( /[\s,]+/ $vals;
   my $placeholders = join( ', ', '?' x $#vals ); # enough '?'s
   my $newsql = $pre . $placeholders . $post;
   my $q = $dbh->prepare_cached( $newsql ) or die $DBI::errstr;
   $q->execute( @vals ) or die $DBI::errstr;

Without seeing your logfile I don't know whether this is practical or not - your mileage may vary.

Note that, if you don't absolutely control the contents of the logs, a non-placeholder solution is possibly subject to SQL injection attacks.

Yours,

f

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur