Mailing-List: contact users-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@cocoon.apache.org
Received-SPF: pass (athena.apache.org: domain of
 anthonybedwards@optusnet.com.au designates 211.29.132.188 as permitted
 sender)
Message-ID: <48BC816B.5030304@optusnet.com.au>
Date: Tue, 02 Sep 2008 09:57:31 +1000
From: Tony Edwards <anthonybedwards@optusnet.com.au>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: users@cocoon.apache.org
Subject: Re: Get Metadata from database
References: <48BB9951.4050700@memocomp.de> <48BBC12B.5CE9.00D4.0@csir.co.za>
 <48BBA807.2070206@memocomp.de>
In-Reply-To: <48BBA807.2070206@memocomp.de>
Content-Type: multipart/mixed;
 boundary="------------030607050206040806060909"

--------------030607050206040806060909
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/


--------------030607050206040806060909
Content-Type: text/x-vcard; charset=utf-8;
 name="anthonybedwards.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="anthonybedwards.vcf"

begin:vcard
fn:Tony Edwards
n:Edwards;Tony
adr:;;7 Freyberg Street;New Lambton;NSW;2305;Australia
email;internet:anthonybedwards@optusnet.com.au
tel;work:0411 636761
url:http://vomoir.blogspot.com/
version:2.1
end:vcard


--------------030607050206040806060909
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
--------------030607050206040806060909--