Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    5

    Unanswered: column_info with JOIN query

    Hello,
    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:
    Code:
    SELECT 
      CT.NAME, 
      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.

    Thanks in advance!

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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/.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •