Javascript data populating chart issue

100 views
Skip to first unread message

Kieran Green

unread,
Apr 5, 2013, 11:03:19 AM4/5/13
to google-visua...@googlegroups.com
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

Sergey

unread,
Apr 5, 2013, 11:26:58 AM4/5/13
to google-visua...@googlegroups.com
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.
  5. You're creating a DataTable with 3 columns, but are only adding 2.

Kieran Green

unread,
Apr 5, 2013, 12:04:55 PM4/5/13
to google-visua...@googlegroups.com
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'],
                    ['<%= rs.getString(1)%>',<%= rs.getInt(2)%> ]], false);

Sergey

unread,
Apr 5, 2013, 12:11:42 PM4/5/13
to google-visua...@googlegroups.com
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);

Kieran Green

unread,
Apr 5, 2013, 12:18:14 PM4/5/13
to google-visua...@googlegroups.com
Thankyou very much!!!
Well explained help and very clear and concise implementation, thankyou Sergey

Sergey Grabkovsky

unread,
Apr 5, 2013, 12:19:26 PM4/5/13
to google-visua...@googlegroups.com
You're very welcome, Kieran! Happy coding!

- Sergey


On Fri, Apr 5, 2013 at 12:18 PM, Kieran Green <kier...@gmail.com> wrote:
Thankyou very much!!!
Well explained help and very clear and concise implementation, thankyou Sergey

--
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 google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
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.
 
 

Reply all
Reply to author
Forward
0 new messages