Format field from SQL Server to currency?

[Salvatore]

Here is my problem in a nutshell....

I have a JSP that is connecting to a database on SQL Server 7.0..one of the fields is a currency...in SQL the data type is money...in the output on the JSP, I need the field to read with a $ and 1000 seperator...

I can not for the life of me find out how to do this ...

Please, any help or workarounds will be appreciated!!

Thank you.

[Dillinger4]

  
  import java.util.Locale; 
  import java.text.NumberFormat;
  
  public class B{
    public static void main(String[] args){

    int x = 1000; 

    NumberFormat nf = NumberFormat.getCurrencyInstance(Locale.US); 
    System.out.println(nf.format(x)); 

    NumberFormat nf2 = NumberFormat.getCurrencyInstance(Locale.JAPAN); 
    System.out.println(nf2.format(x)); 

    NumberFormat nf3 = NumberFormat.getCurrencyInstance(Locale.GERMANY);    
    System.out.println(nf3.format(x)); 
  }
}

[Dillinger4]

Salvatore just curious; how is the currency field accessed from your jsp page? Is currency field mapped to a native java type and stored in a bean then access from the jsp page via a <jsp:getProperty name = String property = String/> tag?

[Salvatore]

Here is the code that I am using...it is connecting to the SQL Server through JDBC Connection and then I am looping through the array to place the values in a table for the JSP:

<table border='0' cellspacing='1' celpadding='1' width='520' height='10' BORDERCOLOR='#0000FF' BORDERCOLORLIGHT='#33CCFF' BORDERCOLORDARK='#0000CC' align='center'>
<td>
<%

String prodRow[];
String month = (String)request.getSession(true).getAttribute("month");
String year = (String)request.getSession(true).getAttribute("year");
String rep = (String)request.getSession(true).getAttribute("rep");

try {
	ddpe.sqltools.SQLTool q2= new ddpe.sqltools.SQLTool("sun.jdbc.odbc.JdbcOdbcDriver","SAMSWeb2",true);
	out.println(q2.getError());
	q2.doSelectQuery("select Distinct(Product), Sum(Units) as Units, Sum(DollarAmt) as [Dollar Amount] from Sales Where Month = '"+month+"' and Year = '"+year+"' and RepName = '"+rep+"' GROUP BY Product");
	out.println(q2.getError());
		String bg="#003366";
		String mrow[];
		mrow = q2.getColumnNames();
		out.println("<tr>");
		
		for (int cx=0; cx<mrow.length; cx++) {
			out.print("<td align='center'><font color='darkblue' size=3><b>"+mrow[cx]+"</font></td></b>");
		
		}
		out.println("</tr>");
		
		while ((prodRow=q2.s_next())!=null) {

			for (int t=0; t<q2.getNumCols(); t++) {
				
				if (prodRow[t].length()<1) prodRow[t]="&nbsp;";
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+prodRow[t]+"</font></td>");
				
			}
			
			out.println("</tr>");
			if (bg.equals("#003366")) bg="#000000"; else bg="#003366";
		}
								
		q2.close();

}
   	catch (Exception e) {
   		System.out.println(e.toString());
   	}

%>
</td>
</table>

What do you think?

[Salvatore]

I didn't get a response....so I was wondering if it is possible with the code I provided?

[Dillinger4]

I would go about structuring everything a bit differently. Perhaps using the MVC architecture. Nesting java code in an html page is just as bad as nesting html elements within servlets which as i understand it is what programmers used to do. I can't really see what you want to format. I am assuming that the data which you want to format is within the String[] prodRow array.

[Salvatore]

Hey Dilinger4,

I appreciate your comments...I am hoping that you are reading this today, because I could use some of your expertise in solving this problem of mine....

You see I am having no problem incorporating java, javascript and HTML into dynamic JSP's....I am fairly new to this and the proper structure for such pages...The pages I am creating are used within an INTRANET structure that is utilized acrross the world for a major Financial Institution...

What I need to know is how I can format the money value that I am retrieving from the SQL Server into a currency format...I have attempted several things such as javascript functions, but I can not get the eventHandler to be called while the values are being retrieved in the loop.

I have also imported the java.text and java.util packages in order to use the NumberFormat...but to no success....

I am very hopeful that either yourself or others could help to resolve this imperative issue of mine...

Thank you

[CornedBee]

You need to pre-process the values. Currently you have them as strings, which is pretty bad for processing.
prodRow[t]
You first need to parse them into integers.
Int.parseInt(prodRow[t])
Or longs, if the values might be larger than 4 billions.
Long.parseLong(prodRow[t])
Or BigDecimal, if you have places after the comma.
new java.math.BigDecimal(prodRow[t])
Then you have a proper value and can format it using the java.text package.
java.text.NumberFormat.getCurrencyInstance(Locale.US).format(new java.math.BigDecimal(prodRow[t]))

Feel free to introduce temporary variables at will.

[Salvatore]

Hello CornedBee,

Thank you very much for your suggestion...

I just have one followup question, that I am hoping you could assist me with...

When should the parsing occur?? What I mean is that I am retrieving the data by way of a loop...so I am not sure where in the code I should place the parsing to Integers??

Here is the snippet of the loop...my previous replies in this thread show the complete code:

String prodRow[];
String bg="#003366";

while ((prodRow=q2.s_next())!=null) {

			for (int t=0; t<q2.getNumCols(); t++) {
				if (t==0) {
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+prodRow[t]+"</font></td>");
				} else if (t==1) {
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+prodRow[t]+"</font></td>");
				} else if (t==2) {	
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+prodRow[t]+"</font></td>");
				}
			}
			
			out.println("</tr>");
			if (bg.equals("#003366")) bg="#000000"; else bg="#003366";
		}

I hope that you can assist me with this...thank you!

[CornedBee]

Inside the loop. (Mind you, the inner loop you have is rather useless, but that's a different topic.) Just before the ifs, you create a local variable that holds the final value and calculate it from the database cell. Then you can use it instead of the row inside the HTML.

[Salvatore]

Ok, I probaly should have stated that I am fairly new to JAVA....so I apologize for my ignorant questions...

Here is what I have, but I am receiving errors from:

while ((prodRow=q2.s_next())!=null) {

			for (int t=0; t<q2.getNumCols(); t++) {
				Int nwProdrow[] = Int.parseInt(prodRow[t]);
				if (t==0) {
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+nwProdrow[t]+"</font></td>");
				} else if (t==1) {
					out.print("<td align='center' bgcolor="+bg+"><font size=2 color='white'>"+nwProdrow[t]+"</font></td>");
				} else if (t==2) {
					System.out.println(nf.format("+nwProdrow[t]+")); 
				}
			}
			
			out.println("</tr>");
			if (bg.equals("#003366")) bg="#000000"; else bg="#003366";
		}

The error is as follows from my Web Server 6.0 Error Log:

JasperException: Unable to compile class for JSPC:\iPlanet\Servers\https-het7\config\..\ClassCache\_jsps\_sams\_sams_rep_result_jsp.java:119: Class _jsps._sams.Int not found. Int nwProdrow[] = Int.parseInt(prodRow[t]); ^

Do you know what I am doing wrong?

Also if you notice in the output to the table in HTML, the third column represented under the else if (t==2) statement is where the dollar amount is to show...while the other 2 columns are using the out.print, I am not sure how to output the format for the currency....

Thank you so much for your time and consideration!!

[CornedBee]

First things first.
Int nwProdrow[] = Int.parseInt(prodRow[t]);
This should be
int curprod = Integer.parseInt(prodRow[t]);

Then replace the nwProdrow[t] with curprod.

That should get rid of the error, though it may not yet show what you want.

[Salvatore]

OK....NO Errors!!

But as you probably assessed, no results either....

So where am i going wrong???

I mean that I was able to show the results prior to this...and so that you may understand the output data that I am trying to show...

The first column of data should be string, the 2nd and 3rd is integer...the last column which is why I created this thread is the currency field....

Also the data for the last 2 columns are set up in the SQL server as integer and money respectively....

So the output should have 1st column string followed by the integer then currency...I am assuming that the problem lies in the output to the table???

You have helped me greatly so far, maybe you have a moment to see if I can get this to work with what I have???

Thank you.