how do I... pseudo dynamic sql (like Oracle)?

[theshowmecanuck]

I am trying to write some pseudo dynamic sql to generate a script. It is something I have done much of in Oracle and SQL Server, but I can't figure out the data dictionary for MySQL

Example (what I would do in Oracle):

select "select " || column_name || " from " || table_name || ";"
from user_tab_columns
where column_name like 'col%'
and table_name like 'tab%';

This would generate output something like this:

select col1 from tab1;
select col2 from tab1;
select col3 from tab2;
select col4 from tab3;
...

So, what would be the equivilent of 'user_tab_columns' and the other data dictionary views? I sure hope there is one.

Thanks in advance.

 

[swampBoogie]

Mysql does not have any dictionary views. It supports commands such as SHOW TABLE for getting this type of information.

As far as I know it is not possible using a select statemet to get metadata in Mysql.

 

[theshowmecanuck]

How horrible. Ah well. I will look to Postgres (since they now have a full Windows port)... if it doesn't have it, back to MySQL. At least it is free as in beer.

Thanks for the info! Cheers.

 

[ingresman]

You can get the info you want just not from the system tables.
Look at mysql_list_fields();

 

[ericbrunson]

Since you're writing a script to output another script your first script doesn't need to be SQL.

Try something like this:

ns1(~)$ echo 'show tables;' | mysql -u user -ppassword db | sed 1d | grep '^tab' | while read table ; do echo "describe $table;" | mysql -u user -ppassword db | sed 1d | cut -f1 | grep '^col' | while read column ; do echo "select $column from $table;" ; done ; done
select column1 from table1;
select column2 from table1;
select collars from tablet;
select colleen from tablet;
select collard from tabtabtab;

 

[theshowmecanuck]

hey e.b. yeah, I am pretty sure that will work too, but I was hoping for something a little more elegant and less clunky. That is a very very rough and tough way to do something that is very very simple to do using SQL (and is used quite extensively) in other rdbms systems. If I keep using MySQL, I suspect I will need to use this method, or use the mysql_list_fields() method. This is probably due to the fact that MySQL doesn't support views yet.

Thanks guys!

 

[ericbrunson]

Yeah, I don't approve of MySQL's failure to maintain it's structure within the database, it is a direct violation of Codd's 4th rule of relational databases.

As far as clunky, I don't consider it to be any less clunky than writing SQL to generate SQL. I could have written it much more cleanly in a different language (I'm extrememly partial to Python), but I was purposely using lowest common denominator scripting tools.

Besides, I don't know of any standardization of data dictionaries between RDBMS's, they all name their DD tables differently, so this is probably just as portable to another RDBMS as any other method.

 

[swampBoogie]

There exists a standard for data dictionary views, the information_schema. Implemented in some DBMS, e.g. SQL Server, Mimer and Postgres.

 

[Bastien]

here is a little ASP / MYSQL example of the show tables / columns method

sql = "SHOW TABLES"
  oTables.Open sql, PortalStDB, adOpenStatic, adLockReadOnly

  do until oTables.EOF
    
       sql = "SHOW COLUMNS FROM " & oTables(0) & ";"
       oCols.open sql, PortalStDB, adOpenStatic, adLockReadOnly
      
       If not oCols.EOF Then
          do while not oCols.eof
            'do some stuff

            oCols.MoveNext
         loop  
       end if 
       oCols.Close

       oTables.MoveNext
    end if           
  Loop

Bastien

Cat, the other other white meat