I have a program that executes SQL statements and returns XML to the client where it is converted to a dataset and used afterwards. Written in Object Pascal this is working with no problems over TCP/IP.
Now I have written that in Perl to work over HTTP and have some problems. With Pascal's classes it is very easy to obtain the data type and column size after a SELECT query is executed. For this to work I am using $dbh->column_info() as wriiten in DBI docs.
The problem is that this one needs a column name and a table name.
A simple sql parser is doing fine but when it comes to JOIN queries this gets dirty.
A simple query like:
CN.NAME AS J_COUNTRY_NAME
FROM CITY CT
JOIN COUNTRY CN ON CT.COUNTRY_ID = CN.ID
can be written in numerous ways and there is much more work to be done to determine correct column type and size.
Is there an easy way to use column_info with JOIN queries, or any other way for getting this information? I have searched the net and column_info seems to be very used function. Not to mention easy migration in DB systems.
Your question ranges from vague to incomprehensible.
You seem to think that joins make a query result really complicated. They don't. The result of a query with a join subclause is just a regular SQL table. So getting info on the columns is going to work the same way it does without joins.
If you're asking, "can I ask the DBMS what the schema looks like before I build a dynamic query," the answer is "depends on the DBMS you're using." DBI is, like ODBC, JDBC, ADO and on and on, pretty much a wrapper around SQL. Operations like interrogating the schema of the database are usually handled by proprietary SQL. You might search your DBMS manual for INFORMATION_SCHEMA.
If you're trying to figure out what the "schema" of your result is, then, again, it depends on your DBMS but there are a slew of functions in DBI. Read The Fine Manual on DBI, meaning "man DBI" or go to http://dbi.perl.org/.