Metadata aren't correct
Posted by: yong.wang
Date: May 06, 2005 08:11AM

Hi, I am having problem with mysql 3.0 connectors (w/ mysql 5.0). They return String type for numeric fields in the metadata. In the following code, "RESERVE" is defined as numeric(10,2) like many others in the list, but metadata returns type "12" which is a string. Can someone tell me how to get around with this problem? Thanks!


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class JDBCMetaTest {

static final private String QUERY = "SELECT lot.LOT_CODE, contract.CONTRACT_NUMBER, concat_ws(', ', buyer.last_name, buyer.first_name) as BUYER, rb.HOME_PHONE, contract.SOLD_DATE, Acct.SERVICE_FEE_RECEIVED, W.SERVICE_FEE_PCT, W.RESERVE_PERCENT, W.RESERVE, Acct.DISCOUNT_TAKEN, W.DISCOUNT, Acct.ADVANCE_TAKEN, W.ADVANCE, Acct.LAST_PAID, L.PPD_FINANCE_CHARGE, Acct.total_paid, L.AMOUNT_FINANCED, Acct.balance, vendor.COMPANY_NAME, vendor.phone,ifnull(contract.GROUP_CODE,'') as GROUP_CODE,if(buyer.buyer_type_id = 'R',ifnull(buyer.GROUP_CODE,' '), ' ') as RETAIL_BUYER_GROUP_CODE, (case 1 when contract.status='A' then 'Active' when contract.status='Q' then 'Quote' when contract.status='C' then 'Cancelled' end) as status FROM buyer, loan L, washout W, contract, lot, retail_buyer rb, vendor, account Acct WHERE L.IS_OUTSIDE_FINANCE_COMPANY='1' AND contract.status = 'A' AND Acct.balance > 0 AND contract.LOT_OID = lot.OID AND L.CONTRACT_OID = contract.OID AND W.CONTRACT_OID = contract.OID and Acct.contract_oid=contract.OID AND buyer.OID = contract.BUYER_OID AND buyer.OID = rb.OID AND L.FINANCE_COMPANY_OID = vendor.OID";
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://msc_ywang:3306/dms2";
Connection conn = DriverManager.getConnection(url,"test","");
Statement stmt = conn.createStatement();
ResultSet rs;

stmt.setMaxRows(1);
rs = stmt.executeQuery(QUERY);

ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

for (int i = 1; i <= numberOfColumns; i++) {
String name = rsmd.getColumnName(i);
String label = rsmd.getColumnLabel(i);
int type = rsmd.getColumnType(i);
int size = rsmd.getColumnDisplaySize(i);
int scale = rsmd.getScale(i);

System.out.println("name=" + name + " type=" + type + " size=" + size + " scale=" + scale);
}

conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}

}

OUPUT:

name=LOT_CODE type=12 size=60 scale=0
name=CONTRACT_NUMBER type=12 size=60 scale=0
name=BUYER type=12 size=126 scale=0
name=HOME_PHONE type=12 size=60 scale=0
name=SOLD_DATE type=93 size=19 scale=0
name=SERVICE_FEE_RECEIVED type=12 size=10 scale=0
name=SERVICE_FEE_PCT type=12 size=8 scale=0
name=RESERVE_PERCENT type=12 size=8 scale=0
name=RESERVE type=12 size=10 scale=0
name=DISCOUNT_TAKEN type=12 size=10 scale=0
name=DISCOUNT type=12 size=10 scale=0
name=ADVANCE_TAKEN type=12 size=10 scale=0
name=ADVANCE type=12 size=10 scale=0
name=LAST_PAID type=93 size=19 scale=0
name=PPD_FINANCE_CHARGE type=12 size=10 scale=0
name=total_paid type=12 size=10 scale=0
name=AMOUNT_FINANCED type=12 size=10 scale=0
name=balance type=12 size=10 scale=0
name=COMPANY_NAME type=12 size=300 scale=0
name=phone type=12 size=60 scale=0
name=GROUP_CODE type=12 size=0 scale=0
name=RETAIL_BUYER_GROUP_CODE type=12 size=3 scale=0
name=status type=12 size=27 scale=0

Options: ReplyQuote


Subject
Written By
Posted
Metadata aren't correct
May 06, 2005 08:11AM
May 06, 2005 08:29AM
May 06, 2005 08:57AM
May 06, 2005 09:36AM
May 06, 2005 08:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.