uidev uidev - 6 months ago 26
Javascript Question

multiple columns from database resultset - Display bargraph using google charts

I am trying to draw a bar graph with x-axis as date and y-axis as count for different channels. Here below is my code and when i run it, I am not seeing anything on the page. Is this right way to do this? please let me know

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html lang="en">
<head>
<script type="text/javascript">

var theData = [ // Start of JavaScript data object
<%
Class.forName("oracle.jdbc.OracleDriver").newInstance();
String connectionURL = "somestring";
Connection connection = null;
connection = DriverManager.getConnection(connectionURL);

PreparedStatement ps = connection.prepareStatement("SELECT TO_CHAR(order_date, 'dd-Mon-yy') order_date, COUNT(DECODE(order_channel, 'CAE', acctnum, NULL)) AS cae, COUNT(DECODE(order_channel, 'COM', acctnum, NULL)) AS com, COUNT(DECODE(order_channel, 'COMPRO', acctnum, NULL)) AS compro, COUNT(DECODE(order_channel, 'EBIF', acctnum, NULL)) AS ebif, COUNT(DECODE(order_channel, 'FRC', acctnum, NULL)) AS frc, COUNT(DECODE(order_channel, 'IVR', acctnum, NULL)) AS ivr, COUNT(DECODE(order_channel, 'NATPRO', acctnum, NULL)) AS natpro, COUNT(DECODE(order_channel, 'PRO', acctnum, NULL)) AS pro, COUNT(DECODE(order_channel, 'X1U', acctnum, NULL)) AS x1u FROM order_channel WHERE order_date >= TRUNC(sysdate) - 30 GROUP BY order_date ORDER BY TO_DATE(order_date)");
ResultSet rs = ps.executeQuery();

while (rs.next()) {
%>
[" <%= rs.getString(1)%>",<%= rs.getString(2)%>",<%= rs.getString(3)%>",<%= rs.getString(4)%>",<%= rs.getString(5)%>",<%= rs.getString(6)%>",<%= rs.getString(7)%>",<%= rs.getString(8)%>",<%= rs.getString(9)%>",<%= rs.getString(10)%>"],
<%
};
// End of JavaScript object holding the data
%>
];
</script>

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([['OrderDate', 'CAE', 'Com','ComPro','EBIF','FRC','IVR','NAT','PRO','X1U']].concat(theData), false);

var options = {
title: 'National 30 days'
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>

</head>
<body>
<div id="chart_div" style="width: 900px; height: 500px;"></div>

</body>
</html>


This is the data which i getting from backend by sql..

var theData = [ // Start of JavaScript data object

[ "08-May-16",9,0,0,68,50,1,4,7,0],

[ "09-May-16",7,0,0,84,175,0,14,37,0],

[ "10-May-16",7,0,0,67,170,0,9,46,0],

[ "11-May-16",7,0,0,53,164,0,7,42,0],

[ "12-May-16",7,0,0,63,146,0,3,41,0],

[ "13-May-16",7,0,0,114,165,0,7,36,0],

[ "14-May-16",7,1,0,69,135,0,7,32,0],

[ "15-May-16",1,0,0,76,62,0,4,11,0],

[ "16-May-16",14,8,0,62,156,0,13,49,0],

[ "17-May-16",8,17,0,48,149,0,5,48,2],

[ "18-May-16",7,0,0,58,146,0,3,40,2],

[ "19-May-16",3,0,0,49,140,0,6,36,0],

[ "20-May-16",9,2,0,56,158,0,1,40,1],

[ "21-May-16",6,0,0,54,147,0,5,40,4],

[ "22-May-16",5,0,0,69,57,0,2,13,0],

[ "23-May-16",7,0,0,61,178,0,11,69,5],

[ "24-May-16",10,5,0,76,162,0,9,56,4],

[ "25-May-16",34,0,0,841,159,0,5,41,4],

[ "26-May-16",41,0,0,1206,85,0,4,21,0],

[ "27-May-16",56,0,0,745,216,0,10,36,2],

[ "28-May-16",94,1,0,198,146,0,9,30,2],

[ "29-May-16",66,0,0,12,67,0,3,7,0],

[ "30-May-16",59,0,0,876,46,0,6,17,0],

[ "31-May-16",140,4,0,2187,203,0,19,42,2],

[ "01-Jun-16",43,0,0,1029,147,0,95,47,0],

[ "02-Jun-16",136,18,0,941,510,0,260,163,11],

[ "03-Jun-16",79,0,0,462,446,0,146,116,9],

[ "04-Jun-16",29,1,0,669,387,0,83,81,0],

[ "05-Jun-16",45,0,0,791,215,0,36,48,5],

[ "06-Jun-16",111,0,0,643,478,0,238,159,8],

[ "07-Jun-16",3,0,0,170,12,0,0,0,0],

];

Answer

looks like there are some extra quotation marks

try replacing...

[" <%= rs.getString(1)%>",<%= rs.getString(2)%>",<%= rs.getString(3)%>",<%= rs.getString(4)%>",<%= rs.getString(5)%>",<%= rs.getString(6)%>",<%= rs.getString(7)%>",<%= rs.getString(8)%>",<%= rs.getString(9)%>",<%= rs.getString(10)%>"],

with...

["<%= rs.getString(1)%>",<%= rs.getString(2)%>,<%= rs.getString(3)%>,<%= rs.getString(4)%>,<%= rs.getString(5)%>,<%= rs.getString(6)%>,<%= rs.getString(7)%>,<%= rs.getString(8)%>,<%= rs.getString(9)%>,<%= rs.getString(10)%>],