I am newbie in this, Need to connect to SQL Server

[kadiyalavj]

Hi
I am totally new to this java and Teiid stuff. So can you please help me with a link or with a working document which i can use to pull the data from SQL Server and CSV via Teiid, Join it in and Present it to the VB.NET application.

Please help me
Thanks -- Vijaya Kadiyala
www.DotNetVJ.com

[rareddy]

Vijaya,

Please take look at the Teiid's Quick Start Example at

http://docs.jboss.org/teiid/6.1/quick-start-guide/en-US/html/

it exactly does what you are looking for, except this example integrates the data from Derby database and CSV file. You can easily substitute Derby for SQLServer.

There is a video at the end, which shows how to use Designer. Also, there are lot of documentation if you need. The link to the video is

http://docs.jboss.org/teiid/6.1/quick-start-guide/en-US/html/images/EmbeddedDesignerExampleVideo.swf

The example shows how to access your integrated data from a Java application.

Accessing data from VB.NET application, I am not sure how to accomplish that. If there is way you can connect using the JDBC from VB.NET application you can use that. ODBC support is not available in Teiid at this time.

[kadiyalavj]

Hi RaReddy,

Thanks for your prompt reply. But the example is based out derby and Linux flavour. That too there are different steps even to install Derby and its so much of dependency. As i stated in my question, i dont have any knowledge on Java stuff. I would really appreciate if you any single document with Oracle or SQL Server or Sybase or DB2.

I have one question, can we use Teiid as VDB to genarate reports from Business Objects? Where Teiid is going to pull the data from Sybase and Oracle.

Thanks -- Vijaya Kadiyala

[rareddy]

Vijaya,

You do not need to install Derby, I was referring example as template to create your own VDB using SQLServer. In the example there is nothing that is specific to Linux either. In Java, once you have JDBC driver, accessing Oracle or DB2 is no different than accessing the Derby, just like ADO objects.

To use the Designer to create a VDB, one does not require Java expertise, however working with Java JDBC connections and SQL essential. However you do need to setup the Eclipse which runs using Java JRE 1.6.

To write the access program, that definitely requires a Java experience. I do not know specific question about Business Objects supporting the JDBC access. I know for sure they support ODBC sources. Teiid does not support ODBC yet. Yes, Teiid does support integrating data from both Sybase and Oracle, but the user must supply the corresponding JDBC drivers to Teiid.

Metamatrix, a product that RedHat supports (Teiid is open source re-incarnation of MetaMatrix) does provide ODBC driver, where you may be able to hookup to Business Objects. For more information on this product please see
http://www.jboss.com/products/platforms/dataservices/

[kadiyalavj]

Hi,
Thanks for your quick reply. Finally i started to get hands on Teiid but i am getting one error.
I tried to follow the example which is in Quick Start. Where i am not entirely following the example but my aim is to query the data from a text file using Teiid.

FYI: I am running this on Windows XP and using Latest version of Teiid Designer.

So this is what i did:
Data Preparation:
Created a schema file with the following content: (Schema.txt)
MarketData.PRICE.location = C:\VJ_R_D\SourceFile\sourcedata.txt
MarketData.PRICE.delimiter = ,
MarketData.PRICE.headerLine = 1

Created a sample data file with the following content: (sourcedata.txt)
STOCK,NAME
IBM,I1
RHT,R1
BA,B1
ORCL,O1


1) Created a New Model Project with the Name "portfolio"
2) Created a New Metadata Model with the Name "MarketData"
3) Now i can see MarketData.xml under the Portfolio in Model Explorer
4) On the right hand side, i created a table with the name PRICE.
5) Created two columns STOCK string, NAME string.
6) Created new connector "MarketData" as the Connecter Name and Connector Type "Text File Connector"
7) I set the value C:\VJ_R_D\SourceFile\Schema.txt in Text File Descriptor.
8) Now i can see MarketData in Connectors window.
9) Now i dragged MarketData.xml which is in Model Explorer to the MarketData in Connectors window.
10) Now when i click on the PRICE table and preview data i am getting "Group Does not exists: MarketData.PRICE"

Please let me know how to get around this. I am sure some where i am doing something wrong.

Could you please correct me.

Thanks -- Vijaya Kadiyala

[rareddy]

Vijaya,

I do not see any errors in the steps you have described. One thing I noticed is in your "schema.txt" file, you are missing one line

MarketData.PRICE.skipHeaderLines=1

In my testing, even with out this line, I was failing with error

Error occurred on connector MarkertData Connector<3> - 'SELECT PRICE.symbol, PRICE.name FROM PRICE' cannot be translated by the TextTranslator. Column symbol not found for element

not the group not found exception you are seeing. In Designer if you click the "MessagesLog" tab (right next to preview tab), you should see an exception showing your error. Can post that?

Also, did you save your project before the preview?

[kadiyalavj]

Hi Ramesh,

In fact i got that error also, which you mentioned in your post.

Could you please tell me is there any resolution around with your error?

Thanks -- Vj

[rareddy]

add the following to the your "schema.txt" file?

MarketData.PRICE.skipHeaderLines=1