• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

I want to get 16 digit number from Oracle but I get error

I am using tomcat/JDBC to access Oracle.  

public int getNumber()
        throws MyException
    {
        Connection connection = null;
        MyException exception = null;
        Statement statement = null;
        ResultSet rs = null;
        long CustNum = 0;
       
        try
        {
            connection = DBConnection.getConnection();
            statement = connection.createStatement();
            String CardNum = "select CustID from evisa where ACCOUNT=208487";
                  rs = statement.executeQuery(CardNum);
            if(rs.next())
            {
               CustNum = rs.getLong("CustID");
                System.out.println(CustNum);
            }
            }
        catch(SQLException sqle)
        {
            exception = new MyException(HBError.get("DatabaseFailure.exCode"), HBError.get("DatabaseFailure.exDescription"), HBError.get("DatabaseFailure.exRecovery"), sqle);
        }
        finally
        {
            DBConnection.freeConnection(connection, statement, null);
            if(exception != null)
                throw exception;
        }
      return CustNum;
    }

I am getting error:
java.sql.SQLException: Numeric Overflow

becuase the CustID  is long number (16 digits) and.  I chnage my test account to be # 10 then my code worked.  I know my problem is the lrage number.
I tried to make int to Long  I get an error:

found   : long
required: int
        return CustNum;
               ^
1 error

how can I solve this problem

thanks
0
salasadi
Asked:
salasadi
  • 28
  • 19
  • 6
  • +2
1 Solution
 
objectsCommented:
>      return CustNum;

try:

     return (int) CustNum;
0
 
salasadiAuthor Commented:
so leave everything as is but change this line or make the variable as long in my decalration?
0
 
CEHJCommented:
Try


BigDecimal CustNum = rs.getBigDecimal("CustID");
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
salasadiAuthor Commented:
now I am getting this:

found   : java.math.BigDecimal
required: int
        return CCustNum;

Thanks
0
 
CEHJCommented:
Well, obviously you'd have to *return* BigInteger instead
0
 
salasadiAuthor Commented:
when I did that I get the following:

found   : java.math.BigDecimal
required: java.math.BigInteger
        return  (BigInteger) CustNum;
                             ^
when I remove the ()

I get the following:

C:\com\test\getNumber.java:51: ';' expected
        return  BigInteger CardAcctNum;

0
 
salasadiAuthor Commented:
woops
it retruns  return  BigInteger CustNum; sorry for the typo
0
 
objectsCommented:
> public int getNumber()

change that to

public BigInteger getNumber()
0
 
salasadiAuthor Commented:
I did that but still
0
 
salasadiAuthor Commented:
now I am getting this error:

C:\com\test\getNumber.java:52: inconvertible types
found   : java.math.BigDecimal
required: java.math.BigInteger
        return  (BigInteger) CustNum;
                             ^
0
 
CEHJCommented:
Don't forget

BigInteger CustNum = null;

as well
0
 
salasadiAuthor Commented:
some it compliants about coverting the variable! even after I intil the BigInteger CustNum = null;
C:\com\test\getNumber.java:52: inconvertible types

Thanks
0
 
CEHJCommented:
There should be no conversion. Get rid of int altogether
0
 
Giant2Commented:
Try to cast to int:
public int getNumber(){
...
return CustNum.intValue();
}

Bye, Giant.
0
 
Giant2Commented:
if you need you can even use:
CustNum.floatValue()
or
CustNum.longValue()
...
See http://java.sun.com/j2se/1.5.0/docs/api/java/math/BigDecimal.html
0
 
salasadiAuthor Commented:
Giant,
Thanks the casting did it.  I still have 1 problem.  some time CustNum retrun more then one value, and I need to pass the values to JSP page:

 Myclass test  = new myclass();

String CustNum = String.valueOf(test.getCustNumber());

husband and wife can both have the same account but diffrent Custnum, I need to get both custnum and give them the option to select which account to view.

Thanks again
0
 
CEHJCommented:
>>Thanks the casting did it.

How are you casting? int cannot hold the maximum 16 digit number

int i = 9999999999999999; // Won't compile
0
 
salasadiAuthor Commented:
I left the BigDecimal when I get the rs:

CustNum = rs.getBigDecimal("CustNum");

I cheange the retrun value to :

return  CustNum.intValue();


the output:
5194820009999090
0
 
Giant2Commented:
You must change this piece of code:
  if(rs.next())
            {
               CustNum = rs.getLong("CustID");
                System.out.println(CustNum);
            }
            }

make a while cycle. In this manner you read all the record from your ResultSet.
After this the return variable of your method must be an array or a collection (Vector).
0
 
salasadiAuthor Commented:
can you give anexample please.  I still have a hard time to understand OO
0
 
Giant2Commented:
Example:
public Vector getNumber(){
Vector ret=new Vector();
...
while (rs.next()){
CustNum=rs.getBigDecimal("CustNum");
System.out.println(CustNum);
ret.addElement(CustNum);
}
return ret;
}

Be sure that when you call this method you must cast correctly the element of the Vector.
Example:
Vector numbers=getNumber();
int value0=((BigDecimal)numbers.elementAt(0)).intValue;
0
 
CEHJCommented:
>>
I cheange the retrun value to :

return  CustNum.intValue();
>>


But you can't do that - that number will not fit in an int. See the following:

                  BigInteger bi = new BigInteger("5194820009999090");
                  System.out.println(bi.intValue());
                  System.out.println(bi);
0
 
Giant2Commented:
Or, example 2:
public int[] getNumber(){
Vector ret=new Vector();
..
while (rs.next()){
CustNum=rs.getBigDecimal("CustNum");
System.out.println(CustNum);
ret.addElement(CustNum);
}
int[] retint=int[ret.size()];
for (int i=0;i<ret.size();i++){
retint[i]=((BigDecimal)ret.elementAt(i)).intValue();
}
return retint;
}

In this manner you can manage the int value "directly".
int[] values=getNumber();

Is it clear now?
Hope this could help you.
Bye, Giant.
0
 
salasadiAuthor Commented:
C:\com\test\getnumber.java:15: illegal start of expression
         public int[] getNumber()
         ^
0
 
Giant2Commented:
check the { } parentesis. Maybe someone is not closed.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
It would be more helpful if you post your entire code.

>> illegal start of expression

You have probably written that outside a class or inside a method.
0
 
CEHJCommented:
>>retint[i]=((BigDecimal)ret.elementAt(i)).intValue();

You *can't* do that, for the reason i stated
0
 
salasadiAuthor Commented:
public class getNumber
{

    public getNumber()
    {
       public int[] getNumber()
             {
            Vector ret=new Vector();
        throws MyException
            {
                  Connection connection = null;
                  MyException exception = null;
                  Statement statement = null;
                  ResultSet rs = null;
                  BigDecimal CardAcctNum= null;        
        try
        {
            connection = DBConnection.getConnection();
            statement = connection.createStatement();
            String CardNum = "select Custnum from evisa where ACCOUNT=208487";
                  rs = statement.executeQuery(CardNum);
                  while (rs.next())
                        {
                        CardAcctNum=rs.getBigDecimal("Custnum");
                        System.out.println(CardAcctNum);
                        ret.addElement(CardAcctNum);
                        }
         catch(SQLException sqle)
        {
            exception = new MyException(HBError.get("DatabaseFailure.exCode"), HBError.get("DatabaseFailure.exDescription"), HBError.get("DatabaseFailure.exRecovery"), sqle);
        }
        finally
        {
            DBConnection.freeConnection(connection, statement, null);
            if(exception != null)
                throw exception;
        }
      return ret;
      }
  }
}
0
 
salasadiAuthor Commented:
sorry guys, here is the correct code:

public class getNumber
{

    public getNumber()
    {
       public int[] getCustnum()
             {
            Vector ret=new Vector();
        throws MyException
            {
                  Connection connection = null;
                  MyException exception = null;
                  Statement statement = null;
                  ResultSet rs = null;
                  BigDecimal CardAcctNum= null;        
        try
        {
            connection = DBConnection.getConnection();
            statement = connection.createStatement();
            String CardNum = "select Custnum from evisa where ACCOUNT=208487";
                  rs = statement.executeQuery(CardNum);
                  while (rs.next())
                        {
                        CardAcctNum=rs.getBigDecimal("Custnum");
                        System.out.println(CardAcctNum);
                        ret.addElement(CardAcctNum);
                        }
         catch(SQLException sqle)
        {
            exception = new MyException(HBError.get("DatabaseFailure.exCode"), HBError.get("DatabaseFailure.exDescription"), HBError.get("DatabaseFailure.exRecovery"), sqle);
        }
        finally
        {
            DBConnection.freeConnection(connection, statement, null);
            if(exception != null)
                throw exception;
        }
      return ret;
      }
  }
}
0
 
CEHJCommented:
You need to declare your method as

public Vector getNumber()
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> public getNumber()
>>     {
>>       public int[] getCustnum()
>>           {

You cannot have a method inside a method like I said earlier. Perhaps just remove the public int[] getCustnum () and { after it, and also one } in the end. Plus make your method public getNumber () as public Vector getNumber ()
0
 
salasadiAuthor Commented:
I am getting the following error:

C:\com\test\getNumber.java:15: illegal start of expression
        throws MyException
        ^

here is my final version of the class

public class getNumber
{
           public Vector getCustNumber()
      {
            Vector ret=new Vector();
        throws MyException
            {
                  Connection connection = null;
                  MyException exception = null;
                  Statement statement = null;
                  ResultSet rs = null;
                  BigDecimal CardAcctNum= null;        
        try
        {
            connection = DBConnection.getConnection();
            statement = connection.createStatement();
            String CustNum = "select Custnum from evisa where ACCOUNT=208487";
                  rs = statement.executeQuery(CustNum);
                  while (rs.next())
                        {
                        CustNum=rs.getBigDecimal("Custnum");
                        System.out.println(CustNum);
                        ret.addElement(CustNum);
                        }
            }
         catch(SQLException sqle)
        {
            exception = new MyException(HBError.get("DatabaseFailure.exCode"), HBError.get("DatabaseFailure.exDescription"), HBError.get("DatabaseFailure.exRecovery"), sqle);
        }
        finally
        {
            DBConnection.freeConnection(connection, statement, null);
            if(exception != null)
                throw exception;
        }
            }
      return ret;
      }
}
0
 
CEHJCommented:
>>throws MyException

should come directly after

>>public Vector getCustNumber()
0
 
salasadiAuthor Commented:
you mean it should look like this:

public Vector getCustNumber()
        Vector ret=new Vector();
        throws MyException
         {
          {
0
 
CEHJCommented:
public Vector getCustNumber() throws MyException
0
 
salasadiAuthor Commented:
yaeeeeeeeeeee, the class is compiled successfully and ready to be test it. thank you guys for your help.

how can call vector from JSP?

before the vector I have the following:

Myclass test  = new myclass();
String CustNum = String.valueOf(test.getCustNumber());

now I changed this to be:

static Vector keyw = new Vector(number);

0
 
CEHJCommented:
In your JSP you need to do

<%
    Vector v = new getNumber().getCustNumber();
    // Now use it
%>

Incidentally, class names should begin with a capital letter or your code gets hard to read
0
 
salasadiAuthor Commented:
you mean getNumber should be GetNumber?  This is how I design my code in VB and ASP, someone told me that in Java the standard is to use small letter for the first letter of variable and class, but the second word in the name should be Uppercase.  

0
 
Mayank SAssociate Director - Product EngineeringCommented:
No. In Java it is usually uppercase for the first character of a class name.
0
 
CEHJCommented:
>>in Java the standard is to use small letter for the first letter of variable and class

Only for variables
0
 
salasadiAuthor Commented:
I can loop through my record in JSP, however, I want to be able to check each record to identified the CustNum. I catigorized each CustNum as Gold(G), Silver(S). base on the first 6 difit of the Custnum I can detrmine whether this Cust is G or S.  

I tried  to do the following but faild:

int CardAcctNum=(linteger)e.nextElement();

however, in my JSP I am able to display it as <%= e.nextElement()%>.  what is the data type of the e.nextElement()? and how can I assign it variable and check the first 6 digit?

Thanks
0
 
CEHJCommented:
Where is the Enumeration coming from - on what collection is it called and what's in it?
0
 
salasadiAuthor Commented:
CEHJ,
as you see that I am learning my way to do this.   I find out that I have 2 type custmer, and each have the starting digit diffrent. I tried to do if statement in JSP to detrmine what type of customer. however, the type of custmer is exist in my database and I can pull it in.   so I did add the field the vector.


rs = statement.executeQuery(CustNum);
               while (rs.next())
                    {
                    CustNum=rs.getBigDecimal("Custnum");
                    CType = rs.getString("Type")
           
                    ret.addElement(CustNum);
                    ret.addElement(CType);
                    }

this is working, but is this correct?  when I do v.size() the size show 2 record.  I look at it as 1 record diffrent field but Vector see it as 2 record.

when I retrieve it in JSP I do:

Vector v = new getNumber().getCustNumber();
Enumeration e = v.elements();
while (e.hasMoreElements()) {

%>
<td>Cust Number:</td><td align=left><%= e.nextElement()%></td><td>Type: <%= e.nextElement()%></td>

<%
}
%>

the output is:
Cust Number: 1111111111111112222    Type G

Please let me know if I am doing this correctly.

Thanks

0
 
CEHJCommented:
I would use an Iterator. You're right - it's two values in one row. You need


Vector row = new Vector(2);                  
row.addElement(CustNum);
row.addElement(CType);
ret.addElement(row);

0
 
salasadiAuthor Commented:
and how retrieve it? would that change?
0
 
CEHJCommented:
You retrieve it thus


Iterator i = ret.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    BigInteger custNum = (BigInteger)row.get(0);
    String name = (String)row.get(1);
}
0
 
Mayank SAssociate Director - Product EngineeringCommented:
You can also loop through:

for ( int i = 0, count = ret.size () ; i < count ; i ++ )
{
  Vector row = ( Vector ) ret.get ( i ) ;
}
0
 
salasadiAuthor Commented:
CEHJ
I tried to use this:
Vector v = new getNumber().getCustNumber();
Iterator i = ret.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    BigInteger custNum = (BigInteger)row.get(0);
    String name = (String)row.get(1);
}

but I am getting error:

Undefined variable or class name: ret
                Iterator i = ret.iterator();
0
 
salasadiAuthor Commented:
I change the code to be:
Vector v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    BigInteger CardAcctNum = (BigInteger)row.get(0);
    String name = (String)row.get(1);
.......

I am getting the following error stack..

java.lang.ClassCastException

======================================

Message:
======================================
null

======================================
      
Localized Message:
======================================
null

======================================

Stack Trace:
======================================
java.lang.ClassCastException
      at org.apache.jsp.eVisa$jsp._jspService(eVisa$jsp.java:662)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
      at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
      at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
      at java.lang.Thread.run(Thread.java:534)
0
 
CEHJCommented:
>>getNumber().getCustNumber()

I thought that returned BigInteger ..?
0
 
objectsCommented:
if you are just displaying them then you may not even need to cast:

Vector v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    Object CardAcctNum = row.get(0);
    Object name = row.get(1);
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> Vector row = (Vector)i.next();

What does i.next () return? A Vector, or something else?
0
 
salasadiAuthor Commented:
Mayankeagle

it should retrun 2 values, here is the how they have been added to the vector:

row.addElement(AcctNum);
row.addElement(CType);
ret.addElement(row);

0
 
salasadiAuthor Commented:
CHEJ,
the value retrun from the vector to JSP working just fine with the following:

if you are just displaying them then you may not even need to cast:

Vector v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    Object CardAcctNum = row.get(0);
    Object name = row.get(1);

But I need to convert them to String and Integer so I can do the following:

1-display the number on my JSP like Credit card like Cust Number: ************44445
2-before displaying them I need to be able to send them include an XML string to be encrypted 3DES.  I don't think I will be able to encrypt object.

Thanks
0
 
salasadiAuthor Commented:
I was able to convert the name to string but not the CustNum:

BigInteger CustNum = (BigInteger)row.get(0); <-- this is not working
String name = (String)row.get(1); <--- this is working fine
0
 
salasadiAuthor Commented:
in my class the vector use BigDecimal not bigInteger.  but my problem now is that my vector retruns the first value stored in vector until the loop completed.

the following is print statment while I add to the vector:

while (rs.next())
       {
      CustNum=rs.getBigDecimal("CustNum");
      CType = rs.getString("TYPE");
      row.addElement(CustNum);
      row.addElement(CType);
      ret.addElement(row);
      System.out.println(CustNum);
     }
***********1119
***********2220
***********3331

but in my JSP:

Vector v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    Object CustNum = row.get(0);
    Object name = row.get(1);
system.out.println(CustNum);
}
that retrun the wrong result:

***********1119
***********1119
***********1119

0
 
CEHJCommented:
>>
row.addElement(CustNum);
     row.addElement(CType);
>>

You're not *creating* a Vector row in each iteration of the loop. You should be
0
 
objectsCommented:
> But I need to convert them to String and Integer so I can do the following:

you can just use the toString() method, no need to cast to do that.

Vector v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Vector row = (Vector)i.next();
    Object CardAcctNum = row.get(0);
    Object name = row.get(1);


...
out.println("Card#: "+CardAcctNum);
0
 
salasadiAuthor Commented:
so you want me to do the following:



while (rs.next())
       {
    Vector row=new Vector(2);
     CustNum=rs.getBigDecimal("CustNum");
     CType = rs.getString("TYPE");
     row.addElement(CustNum);
     row.addElement(CType);
     ret.addElement(row);
     System.out.println(CustNum);
     }
0
 
salasadiAuthor Commented:
Vector ret=new Vector();
while (rs.next())
       {
    Vector row=new Vector(2);
     CustNum=rs.getBigDecimal("CustNum");
     CType = rs.getString("TYPE");
     row.addElement(CustNum);
     row.addElement(CType);
     ret.addElement(row);
     System.out.println(CustNum);
     }

0
 
CEHJCommented:
That looks OK
0
 
objectsCommented:
you'd be better off using an ArrayList instead of a Vector, it'll give you better performance.
In fact an array would probably be more suitable as you know tyhe required size

List ret=new ArrayList();
while (rs.next())
       {
    Object[] row=new Object[2];
     CustNum=rs.getBigDecimal("CustNum");
     CType = rs.getString("TYPE");
     row[0] = CustNum;
     row[1] CType;
     ret.add(row);
     System.out.println(CustNum);
     }
0
 
objectsCommented:
then to read it:

List v = new getNumber().getCustNumber();
Iterator i = v.iterator();
while (i.hasNext()) {
    Object[] row = (Object[])i.next();
    String CardAcctNum = row[0].toString();
    String name = row[1].toString();

0
 
CEHJCommented:
:-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 28
  • 19
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now