One Star

Getting Sybase table schemas for processing

I just discovered Talend about two weeks ago, so my question might include any number of misunderstandings of its concepts Smiley Wink
I am using the latest version (unstable) on Windows Vista and my project is set as a Java project.
I am trying to analyse a database the structure of which might change about once per week. The Database has a bit more then 1000 tables. What I basically need to do is to get all table names, then iterate over them to get their column names (and idealy column data types). Then I need to put some logic in place to search these column names for string patterns (if I have the data types then I would also like to check for specific data types). The result would be a list of table and column names which I might then use to take further actions.
The table names can easyly be aquired by just using a regular tSybaseInput with a statement like "SELECT name FROM sysobjects WHERE type='U' ORDER BY name". what I don't know is how I can get the column names from there. Can it be done with one of the standard components or do I need to use some custom Java? I noticed for example that there is a tSybaseSP. Can I somehow tell it to fire an "sp_help tablename" for all tablenames from my first result?
Any help would be appreciated. Talend looks like a great piece of software to me and I am looking foreward to learning as much about it as I can.

Re: Getting Sybase table schemas for processing

In your repository, you have possibility to create a Database Connection in the Metadata Manager.
This wizard is able to connect to a database and extract the schema tables of your Sybase schema.
To do that :
- Expand Metadata, and Click-right on Db Connections.
- Choose "Create connection", specify a name, then "Next".
- Choose the right Database connectivity "Sybase", fill the following Field (Login, Password, Server, Port & Database).
- Click check to test your connectivity, then "Finish".
- Then Click-right on your newer Db Connection Named by yourself, and choose "Retrieve schema"
- It's open a Select table wizard page in which you choose selected schemas to import in your metadata.
- Wait pending the retrieve.
- Then click "Next", you enter in the Schema description wizard page in which you have some information about Database type, Length...
- Click finish to save all this schema in your Metadata repository.
When you want to use a specific schema in your jobs, switch built-in to repository, choose the right DbConnection and the right Schema, then you design your own SQL statement.
One Star

Re: Getting Sybase table schemas for processing

Hi cantoine,
thank you for your reply. It doesn't answer my question, though. I already did what you said I should do for some other jobs I have created. For my current job which I have described in my last post I do not want to manually tell Talend to read the schema and I also don't want to save it in a repository. The situation is that I have a database structure with constantly changing schema (more then 1000 tables). I need to automatically read the schema each time the job is started into a datastructure like this:
tablename STRING(20)
columnname STRING(20)
columntype STRING(15)
How can I best fill this structure with my schema info? As I said, I can easyly get the table names, only I don't know how I might iterate over them in order to get the column names. Or does talend allow me to automatically read the schema information in an accessible way by some other method?