Your problem is that you exhaust your row iterator during the while loop,
and will not have any data left over when you call rs.getString() and
rs.getInt() when constructing your DataTable. The other issue here is that
you're only going to get one row in your chart. Another issue is that now
you don't have a comma between your string and int when you're constructing
theData. The correct solution is to utilize the theData variable which
already contains all the data from your database. I fixed your code inline.
Hopefully this gets you the rest of the way.
On Friday, April 5, 2013 12:04:55 PM UTC-4, Kieran Green wrote:
>
> I've implemented the issues like you said and change the adding data
> method but the issue I've come accross now is an SQL one
> I only return two fields from my database the departments and cores. with
> the changes I have implemented do you see why the potential sql issue
> occurs, just after the end of the result set
>
> <html>
> <head>
> <script type="text/javascript">
>
> var theData = [ // Start of JavaScript data object
> <%
> Class.forName("com.mysql.jdbc.Driver");
> Connection con =
> DriverManager.getConnection("jdbc:mysql://localhost:3306/project", "root",
> "nbuser");
> PreparedStatement ps = con.prepareStatement("SELECT
> department,SUM(cores) FROM project.booking GROUP BY department;");
> ResultSet rs = ps.executeQuery();
>
> while (rs.next()) {
> %>
> ["<%= rs.getString(1)%>", <%= rs.getInt(2)%>],
> <%
> };
> // 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([['Department',
> 'Cores']].concat(theData), false);
>
> var options = {
> title: 'Initial graph test'
> };
>
> 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>
> <a href="/ProjectAndBackend/System?action=livecharts">Live
> Charts</a>
> </body>
> </html>
>
>
>
>
>
>
>
> On Friday, April 5, 2013 4:26:58 PM UTC+1, Sergey wrote:
>>
>> Hello, your code has a number of issues. I will attempt to list them all
>> below.
>>
>> 1. theData will have a trailing comma: Since you indiscriminately add
>> a comma to the end of every row, you will wind up with a trailing comma
>> at
>> the end of your array of rows. The issue with this is that different
>> browsers will interpret this differently, and your chart may error out in
>> certain browsers.
>> 2. Your integer should not be surrounded in quotes, that will cause
>> it to be interpreted as a string.
>> 3. You're never loading theData into your data table: You're just
>> charting constant data.
>> 4. If you intend to create the DataTable the way that you're doing
>> (by instantiating it via new google.visualization.DataTable), you will
>> have
>> to specify your data in the same way that you are doing when you specify
>> your rows (i.e. "{v: '<%=rs.getString(1)%>'}"). The alternative is to use
>> the arrayToDataTable method documented
>> here<https://developers.google.com/chart/interactive/docs/datatables_dataviews#arraytodatatable>
>> .
>> 5. You're creating a DataTable with 3 columns, but are only adding 2.
>>
>>
>> On Friday, April 5, 2013 11:03:19 AM UTC-4, Kieran Green wrote:
>>>
>>> I'm having the issue in my .jsp page, where I get my data from a MySQL
>>> Database then put it as a Javascript Object.
>>>
>>> I then want to display those objects , in this case names and integers
>>> to produce a chart, but when I produce it my chart data is off.
>>>
>>>
>>>
>>> <html>
>>> <head>
>>> <script type="text/javascript">
>>>
>>> var theData = [ // Start of JavaScript data object
>>> <%
>>> Class.forName("com.mysql.jdbc.Driver");
>>> Connection con =
>>> DriverManager.getConnection("jdbc:mysql://localhost:3306/project", "root",
>>> "nbuser");
>>> PreparedStatement ps = con.prepareStatement("SELECT
>>> department,SUM(cores) FROM project.booking GROUP BY department;");
>>> ResultSet rs = ps.executeQuery();
>>>
>>> while (rs.next()) {
>>> %>
>>> [" <%= rs.getString(1)%>","<%= rs.getInt(2) %>"],
>>> <%
>>> };
>>> // 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 = new google.visualization.DataTable(
>>> {
>>> cols: [{id: '1', label: 'Department', type:
>>> 'string'},
>>> {id: '4', label: 'Cores', type: 'number'},
>>> {id: '5', label: 'Disk Space', type: 'number'}],
>>> rows: [{c:[{v: 1}, {v: 2}]}
>>> ]
>>> }
>>> )
>>> var options = {
>>> title: 'Initial graph test'
>>> };
>>>
>>> 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>
>>> <a href="/ProjectAndBackend/System?action=livecharts">Live
>>> Charts</a>
>>> </body>
>>> </html>
>>>
>>>
>>> How do I correctly pass data in to the google charts from the MySQL data
>>> I've previously accumulated
>>> First post on this group so I hope I'm not out of context
>>>
>>> Thanks
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at
http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
