6 Replies Latest reply on Apr 18, 2016 6:40 AM by parrotola

    Get  content from SYSADMIN.VDBResources

    parrotola Novice

      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

        • 1. Re: Get  content from SYSADMIN.VDBResources
          Steven Hawkins Master

          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?

          • 2. Re: Get  content from SYSADMIN.VDBResources
            parrotola Novice

            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

            • 3. Re: Get  content from SYSADMIN.VDBResources
              Steven Hawkins Master

              > 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.

              • 4. Re: Get  content from SYSADMIN.VDBResources
                parrotola Novice

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

                What is the query?

                 

                Thanks.

                • 5. Re: Get  content from SYSADMIN.VDBResources
                  Steven Hawkins Master

                  For a .vdb it would be:

                   

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

                  1 of 1 people found this helpful
                  • 6. Re: Get  content from SYSADMIN.VDBResources
                    parrotola Novice

                    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();

                    }