getting table list from jBASE via JDBC
- Kim
- Topic Author
- Visitor
-
13 years 2 months ago #11240
by Kim
getting table list from jBASE via JDBC was created 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.
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.
- ambdev
- Offline
- Junior Member
-
Less
More
- Posts: 30
- Thank you received: 5
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.
CALL:
testJdbcConn.getMetaData(jdbcConn, "%SECTOR%");
OUTPUT:
FBNK_SECTOR - SECTOR_CODE
FBNK_SECTOR - DESCRIPTION
FBNK_SECTOR - SHORT_NAME
.......
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
-
13 years 2 months ago - 13 years 2 months ago #11272
by Kim
Replied by Kim on topic Re: getting table list from jBASE via JDBC
Dear ambdev,
I really appreciate your help.
I just have fixed JEDIFILENAME_MD, and now it's working.
best regards,
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
-
13 years 2 months ago #11284
by Kim
Replied by Kim on topic Re: getting table list from jBASE via JDBC
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.
- saahmad
- Offline
- Premium Member
-
Less
More
- Posts: 145
- Thank you received: 9
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
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.
- ambdev
- Offline
- Junior Member
-
Less
More
- Posts: 30
- Thank you received: 5
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
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.
- ambdev
- Offline
- Junior Member
-
Less
More
- Posts: 30
- Thank you received: 5
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
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
-
13 years 1 month ago #11477
by Kim
Replied by Kim on topic Re: getting table list from jBASE via JDBC
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.
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