getting table list from jBASE via JDBC

  • Kim
  • Topic Author
  • Visitor
  • Visitor
13 years 2 months ago #11240 by Kim
Hello all,
I connected to jBASE via JDBC and created a table.
I failed to get table list and column list, but I succeeded select query.

here are code examples:

1. get table list
DatabaseMetaData dataBaseMeta = conn.getMetaData();
ResultSetMetaData meta = null;
ResultSet rs = dataBaseMeta.getTables(null, null, null, tableTypes);
meta = rs.getMetaData();
System.out.println("==============================table list================================");
while(rs.next()) {
for(int i=0;i<meta.getColumnCount();i++)
{ System.out.print(rs.getString(i+1)+"\t");}
System.out.println();
}

2. get column list
ResultSet rs2 = dataBaseMeta.getColumns(null, null, "emp", null);
meta = rs2.getMetaData();
System.out.println("============================= emp column list ===========================");
while(rs2.next()) {
for(int i=0;i<meta.getColumnCount();i++)
{ System.out.print(rs2.getString(i+1)+"\t"); }
System.out.println();
}

3. run select
ResultSet rs3 = stmt.executeQuery("SELECT id,name from emp");
ResultSetMetaData meta2 = rs3.getMetaData();
System.out.println("==== emp columns ====");
for(int i=0;i<meta2.getColumnCount();i++)
{ System.out.print(meta2.getColumnLabel(i+1)+"\t"); }
System.out.println();
System.out.println("=====================");
while(rs3.next()) {
for(int i=0;i<meta2.getColumnCount();i++)
{ System.out.print(rs3.getString(i+1)+"\t");}
System.out.println();
}


I got no result and no error from 1 and 2, but got correct result from 3
What's wrong with 1 and 2?

pleas advise me.

Please Log in or Create an account to join the conversation.

More
13 years 2 months ago #11253 by ambdev
Replied by ambdev on topic Re: getting table list from jBASE via JDBC
Try this, but make sure you have a properly configured environment at the jBASE/T24 server before to start jbase agent service.
Specifically you need to include your table in VOC or the table pointed by JEDIFILENAME_MD environment variable.
public void getMetaData(Connection conn, String sTableName) {
        ResultSet rsTables = null;
        ResultSet rsColumns = null;
        try {
            DatabaseMetaData md = conn.getMetaData();
            rsTables = md.getTables(null, null, sTableName, null);
            while (rsTables.next()) {
                String sNextTable = rsTables.getString(3);
                rsColumns = md.getColumns(null, null, sNextTable, "%");
                while (rsColumns.next()) {
                    String sNextColumn = rsColumns.getString(4);
                    System.out.println(sNextTable + " - " + sNextColumn);
                }
            }
        } catch (SQLException ex) {
            log.error("SQLException: " + ex.getMessage());
        } catch (ArrayIndexOutOfBoundsException e) {
            log.error("ArrayIndexOutOfBoundsException: " + e.getMessage());
        } finally {
            try {
                if (rsTables != null) {
                    rsTables.close();
                }
                if (rsColumns != null) {
                    rsColumns.close();
                }
            } catch (SQLException e) {
                log.error("SQLException: " + e.getMessage());
            }
        }
    }


CALL:
testJdbcConn.getMetaData(jdbcConn, "%SECTOR%");

OUTPUT:
FBNK_SECTOR - SECTOR_CODE
FBNK_SECTOR - DESCRIPTION
FBNK_SECTOR - SHORT_NAME
.......
The following user(s) said Thank You: Kim

Please Log in or Create an account to join the conversation.

  • Kim
  • Topic Author
  • Visitor
  • Visitor
13 years 2 months ago - 13 years 2 months ago #11272 by Kim
Dear ambdev,
I really appreciate your help.
I just have fixed JEDIFILENAME_MD, and now it's working.
best regards,
Last edit: 13 years 2 months ago by Kim.

Please Log in or Create an account to join the conversation.

  • Kim
  • Topic Author
  • Visitor
  • Visitor
13 years 2 months ago #11284 by Kim
would you please see my new topic "jBASE field type and JDBC sql data type" and advise me?.

Please Log in or Create an account to join the conversation.

More
13 years 2 months ago - 13 years 2 months ago #11289 by saahmad
Replied by saahmad on topic Re: getting table list from jBASE via JDBC
Dear ambdev

I am trying to connect using your code snippet but i get a error null pointer exception am i connecting wrong

the agent is running also following is the code snippet

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jbasejava;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;

/**
 *
 * @author sahmad02
 */
public class JbaseJava {

    /**
     * @param args the command line arguments
     * 
     * 
     * 
     * 
     */
    
    
    
    public static void getMetaData(Connection conn, String sTableName) {
        ResultSet rsTables = null;
        ResultSet rsColumns = null;
        try {
            DatabaseMetaData md = conn.getMetaData();
            rsTables = md.getTables(null, null, sTableName, null);
            while (rsTables.next()) {
                String sNextTable = rsTables.getString(3);
                rsColumns = md.getColumns(null, null, sNextTable, "%");
                while (rsColumns.next()) {
                    String sNextColumn = rsColumns.getString(4);
                    System.out.println(sNextTable + " - " + sNextColumn);
                }
            }
        } catch (SQLException ex) {
            System.out.println( ex.getMessage());
        } catch (ArrayIndexOutOfBoundsException e) {
            System.out.println("ArrayIndexOutOfBoundsException: " + e.getMessage());
        } finally {
            try {
                if (rsTables != null) {
                    rsTables.close();
                }
                if (rsColumns != null) {
                    rsColumns.close();
                }
            } catch (SQLException e) {
                System.out.println("SQLException: " + e.getMessage());
            }
        }
    }

    
    
    
    
    
    
    
    
    
    public static void main(String[] args) {
        // TODO code application logic here
        //Load jBASE JDBC Driver
        
        try{

Class.forName("com.jbase.jdbc.driver.JBaseJDBCDriver");

 

//Set connection properties and request a new connection

String url = "jdbc:jbase:thin:@10.103.1.32:20002";

Properties cxProps = new Properties();

cxProps.setProperty("user", "t24test");

cxProps.setProperty("password", "t2412test");

//cxProps.setProperty("SSL", "true");                  //Enable SSL

cxProps.setProperty("NaiveTrustManager", "true");

Connection cx = DriverManager.getConnection(url, cxProps);
getMetaData(cx, "%SECTOR%");
/*ResultSet rsTables = null;
ResultSet rsColumns = null;

DatabaseMetaData md = cx.getMetaData();
            rsTables = md.getTables(null, null, "SECTOR", null);
            while (rsTables.next()) {
                String sNextTable = rsTables.getString(3);
                rsColumns = md.getColumns(null, null, sNextTable, "%");
                while (rsColumns.next()) {
                    String sNextColumn = rsColumns.getString(4);
                    System.out.println(sNextTable + " - " + sNextColumn);
                }
            }

Statement stmt = null;
stmt = cx.createStatement();
ResultSet rs3 = stmt.executeQuery("SELECT SHORT_CODE,DESCRIPTION,SECTOR_CODE from FBNK_SECTOR");
ResultSetMetaData meta2 = rs3.getMetaData();
System.out.println("==== emp columns ====");
for(int i=0;i<meta2.getColumnCount();i++)
{ System.out.print(meta2.getColumnLabel(i+1)+"\t"); }
System.out.println();
System.out.println("=====================");
while(rs3.next()) {
for(int i=0;i<meta2.getColumnCount();i++)
{ System.out.print(rs3.getString(i+1)+"\t");}
System.out.println();
}
*/













    }catch(Exception ex){
        System.out.println("Error while connecting to jbase  "+ex.toString());
        ex.printStackTrace();
    }
        
    }      
        
}
Last edit: 13 years 2 months ago by saahmad.

Please Log in or Create an account to join the conversation.

More
13 years 2 months ago #11291 by ambdev
Replied by ambdev on topic Re: getting table list from jBASE via JDBC
This is what I did to reproduce the exception. If you received a different exception message, then you will need to post it.

FBNK_ACCOUNT - ACCOUNT_NUMBER
FBNK_ACCOUNT - CUSTOMER
...
FBNK_ACCOUNT - TRAN_LAST_CR_AUTO
Exception in thread "main" java.lang.NullPointerException
at com.jbase.jdbc.JBaseJDBCDatabaseMetaData.getJavaSqlType(Unknown Source)
at com.jbase.jdbc.JBaseJDBCDatabaseMetaData$9.setRowValues(Unknown Source)
at com.jbase.jdbc.JBaseJDBCDatabaseMetaData$9.next(Unknown Source)
...

Last "good" field is: TRAN_LAST_CR_AUTO
By looking at STANDARD.SELECTION we can see the field that comes after 'TRAN_LAST_CR_AUTO'
In my case the next field and the culprit is 'DATE.LAST.CR.BANK'

Please, execute below two commands with your own data in the jsh shell and post the output.

-- jsh commands
COPY DICT FBNK.ACCOUNT TRAN.LAST.CR.AUTO (TN
COPY DICT FBNK.ACCOUNT DATE.LAST.CR.BANK (TN

Please Log in or Create an account to join the conversation.

More
13 years 1 month ago #11473 by ambdev
Replied by ambdev on topic Re: getting table list from jBASE via JDBC
Instead of write code for reading from a jBASE/T24 Database using SQL you can try a free SQL Client.

I did some tests with SQuirreL, if you want to check the results look here: youtube.com/watch?v=LwMkpQwLuxU

regards

Please Log in or Create an account to join the conversation.

  • Kim
  • Topic Author
  • Visitor
  • Visitor
13 years 1 month ago #11477 by Kim
I have tried SQuirreL as you recommended.
Actually, I have to make my own program, but, SQuirreL will be a great reference.
Thanks for your kindness.

Please Log in or Create an account to join the conversation.

Time to create page: 0.041 seconds