cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tony Edwards <anthonybedwa...@optusnet.com.au>
Subject Re: Get Metadata from database
Date Mon, 01 Sep 2008 23:57:31 GMT
I don't know if this helps but I've obtained metadata info from a 
database (oracle, sql server and Informix) using a little bit of flow.
The code snippet below displays the details about a given table and 
column but it can be expanded to return information about all tables and 
columns (I've got code for that sitting around somewhere).
I suppose you're limited to what the jdbc driver returns but it seems 
pretty comprehensive for most uses.
See what you think
Regards,
Tony


importClass(java.sql.ResultSetMetaData);
importClass(java.sql.ResultSet);

function getSchemaDetails(psTableName, psColumn){
    if((gDbConn == null) || (gDbConn.isClosed())){   
        gDbConn = getDbConnectionObject(user.userLogin.username, 
user.userLogin.password, user.userLogin.database);           
    }
   
    var table = psTableName;
    var bFound = false;

    if(table != null){
       
        print("Schema object = " + table);
        print("psColumn = " + psColumn);
        var WSps = null;
       
        WSps = gDbConn.prepareStatement("SELECT * from " + table);
        var resultRs = WSps.executeQuery();
       
        try{
            var WSrsmd = resultRs.getMetaData();
            var numcols = WSrsmd.getColumnCount();
           
            print("Number of columns in table '" + table + "' = " + 
numcols);
            var colName = new String();
            for(var n=1; n < numcols + 1; n++){
                colName = WSrsmd.getColumnName(n);
                print("Column name " + n + " = " + colName);
                if(colName.toLowerCase() == psColumn.toLowerCase()){
                    bFound = true;
                }               
            }
            resultRs.close();
            WSps.close();
            WSps = null;
            resultRs = null;
            return bFound;       
           
        } catch (error){
            message="Error obtaining metadata: " + error;
            cocoon.log.error(error);           
            return false;
        }
    }           
}

getDbConnectionObject just opens a connection after obtaining the 
required information from an xml file and basically looks like this:
    //java.lang.Class.forName(className);
    try{
        var jdbc = java.sql.DriverManager.getConnection(dbUrl, 
dbUserName, dbPassWord)
    } catch (error) {
        setMessage("Error conecting to database! " + error);
        print("Error conecting to database! " + error);
        return null;
    }

Johannes Hoechstaedter wrote:
> I know that there is a mechanism in JDBC. But it seems that there is 
> no mechanism in cocoon. For me it is important to have a more db 
> independent way. I will try to write my own database action for 
> metadatas.
>
> Derek Hohls schrieb:
>> Johannes
>>
>> I think this is database dependant - in MySQL (ver 5+) you can read this
>> type of information from the INFORMATION_SCHEMA table.
>> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
>> Derek
>>
>>  
>>>>> On 2008/09/01 at 09:27, in message <48BB9951.4050700@memocomp.de>,

>>>>> Johannes Hoechstaedter <j.hoechstaedter@memocomp.de> wrote:
>>>>>         
>> Is it possible to fetch the metadata from a database, such as table 
>> names, column names, column types and so on?
>>
>> cheers
>> Johannes
>>
>>
>>
>>
>>
>>   
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>

-- 
Tony Edwards
Mob: 0411 636 761
Skype: anthony.b.edwards
email: anthonybedwards@optusnet.com.au
http://vomoir.blogspot.com/


Mime
View raw message