topic Getting Sybase table schemas for processing in Talend Studio

Getting Sybase table schemas for processing

Anonymous — Sat, 16 Nov 2024 14:32:55 GMT

I just discovered Talend about two weeks ago, so my question might include any number of misunderstandings of its concepts
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

Anonymous — Fri, 28 Sep 2007 02:02:45 GMT

Hi,
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.
Regards

Re: Getting Sybase table schemas for processing

Anonymous — Fri, 28 Sep 2007 07:57:56 GMT

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?
Cheers,
colorfool