Get content from SYSADMIN.VDBResources

[parrotola]

Hi,

I would like to ask you how can I  get vdb file or every xmi file:

• calling SYS.getXMLSchemas;
• using the table SYSADMIN.VDBResources.

Is there a Way to obtain this files through JDBC connector?

Best regards.

Thanks.

Francesco

[shawkins]

SYS.getXMLSchemas is for schema/.xsd files associated with xml document models. 

SYSADMIN.VDBResources should provide you with access to .xmi files for vdbs built by Designer.  This will provide a read only blob reference.

> Is there a Way to obtain this files through JDBC connector?

Can you elaborate on how you would want to use this from a JDBC connector / translator, or do you just mean from a JDBC client?

[parrotola]

My problem is that I want to obtain from JDBC connection, details about vdb created with the designer. For example Source, type of source and translator. Because from Table of SysAdmin I'm not able to obtain the kind of connection (Flat file, RDBMS...)

Thanks.

Best regards.

Francesco

[shawkins]

> My problem is that I want to obtain from JDBC connection, details about vdb created with the designer. For example Source, type of source and translator. Because from Table of SysAdmin I'm not able to obtain the kind of connection (Flat file, RDBMS...)

The most straight-forward way of doing that would be to retrieve the vdb.xml from SYSADMIN.vdbResources.  Alternatively an admin connection will give you access to the VDB metadata object, which makes it quite easy to determine all of that metadata.

[parrotola]

How Can I obtain vdb.xml from CBlob field of SYSADMIN.vdbResources?

What is the query?

Thanks.

[shawkins]

For a .vdb it would be:

select contents from SYSADMIN.vdbResources where resourcePath = '/META-INF/vdb.xml'

[parrotola]

Thank you very much.

This is  the code:

String url = "jdbc:teiid:vdbname@mm://localhost:31000";

String sql = "select contents from SYSADMIN.vdbResources where resourcePath = '/META-INF/vdb.xml'"; 

Class.forName("org.teiid.jdbc.TeiidDriver");

connection = DriverManager.getConnection(url, "user", "password");

Statement statement = connection.createStatement();

ResultSet results = statement.executeQuery(sql);

while(results.next()) {

  File xmlfile = new File("example.xml");

  FileOutputStream fos = new FileOutputStream(xmlfile);

  byte[] buffer = new byte[1];

  InputStream is = results.getBinaryStream(1);

  while (is.read(buffer) > 0) {

  fos.write(buffer);

  }

  fos.close();

}