AnsweredAssumed Answered

Connect to Database using javascript/ajax in alfresco

Question asked by croc on Nov 23, 2010
Latest reply on Feb 4, 2013 by anand6105
Hi Guys,

I would like to know how to connect to database using javascript/ajax in alfresco.
I am able to connect and add data using ajax, but when i used the code in alfresco it gives me error.
Below is the code:
index.jsp


<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Sita Ajax test</title>
        <script>
            /* —————————- */
            /* XMLHTTPRequest Enable */
            /* —————————- */
            function createObject() {
                var request_type;
                var browser = navigator.appName;
                if(browser == "Microsoft Internet Explorer"){
                    request_type = new ActiveXObject("Microsoft.XMLHTTP");
                }else{
                    request_type = new XMLHttpRequest();
                }
                return request_type;
            }

            var http = createObject();


            /* ————————– */
            /* INSERT */
            /* ————————– */
            /* Required: var nocache is a random number to add to request. This value solve an Internet Explorer cache issue */
            var nocache = 0;
            function insert() {
                // Optional: Show a waiting message in the layer with ID login_response
                document.getElementById('insert_response').innerHTML = "Just a second…"
                // Required: verify that all fileds is not empty. Use encodeURI() to solve some issues about character encoding.
                var name= encodeURI(document.getElementById('name').value);
                var city = encodeURI(document.getElementById('city').value);
                var phone = encodeURI(document.getElementById('phone').value);
                // Set te random number to add to URL request
                nocache = Math.random();
                // Pass the login variables like URL variable
                http.open('get', 'insert.jsp?name='+name+'&city=' +city+'&phone=' +phone+'&nocache = '+nocache);
                http.onreadystatechange = insertReply;
                http.send(null);
            }
            function insertReply() {
                if(http.readyState == 4){
                    var response = http.responseText;
                    // else if login is ok show a message: "Site added+ site URL".
                    document.getElementById('insert_response').innerHTML = ''+response;
                }
            }
        </script>
    </head>
    <body>
        <h1>Welcome!</h1>


        <form action="javascript:insert()" method="post">
            <table style="background-color: #ECE5B6;" width="30%" >

                <tr>
                    <th width="50%">Name</th>
                    <td width="50%">
                        <input type="text" name="name" id="name">
                    </td>
                </tr>
                <tr>
                    <th width="50%">City</th>
                    <td width="50%">
                        <input type="text" name="city" id="city">
                    </td>
                </tr>
                <tr>
                    <th width="50%">Phone</th>
                    <td width="50%">
                        <input type="text" name="phone" id="phone">
                    </td>
                </tr>

                <tr>
                    <th></th>
                    <td width="50%"><input type="submit" value="submit"></td>
                </tr>
            </table>
        </form>
        <br /><br /><br /><br />

        <!– Show Message for AJAX response –>
        <div id="insert_response"></div>

    </body>
</html>


insert.jsp


<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %> 

<%
            String name = request.getParameter("name");
            String city = request.getParameter("city");
            String phone = request.getParameter("phone");


            /* Create string of connection url within specified format with machine name,
            port number and database name. Here machine name id localhost and database name is student. */
            String connectionURL = "jdbc:mysql://localhost:3306/alfresco";

            // declare a connection by using Connection interface
            Connection connection = null;

            // declare object of Statement interface that uses for executing sql statements.
            PreparedStatement pstatement = null;

            // Load JBBC driver "com.mysql.jdbc.Driver"
            Class.forName("com.mysql.jdbc.Driver").newInstance();

            int updateQuery = 0;

            // check if the text box is empty
            if (name != null && city != null && phone != null) {

                // check if the text box having only blank spaces
                if (name != "" && city != "" && phone != "") {

                    try {
                        /* Create a connection by using getConnection() method that takes
                        parameters of string type connection url, user name and password to connect
                        to database. */
                        connection = DriverManager.getConnection(connectionURL, "root", "root");

                        // sql query to insert values in the secified table.
                        String queryString = "INSERT INTO stu_info(Name,City,Phone) VALUES (?, ?, ?)";

                        /* createStatement() is used for create statement object that is used for
                        sending sql statements to the specified database. */
                        pstatement = connection.prepareStatement(queryString);
                        pstatement.setString(1, name);
                        pstatement.setString(2, city);
                        pstatement.setString(3, phone);
                        updateQuery = pstatement.executeUpdate();

                        if (updateQuery != 0) {
                            out.println("Data was inserted successfuly.");
                        }
                    } catch (Exception ex) {
                        out.println("Unable to connect to batabase.");
                        out.println(ex);
                        ex.printStackTrace();

                    } finally {
                        // close all the connections.
                        pstatement.close();
                        connection.close();
                    }
                }
            }
%>

This code works fine if i run it as a separate web application, but gives errors when i copy it to alfresco.

Thanks in advance
Croc

Outcomes