Hyland Connect

croc · ‎11-23-2010

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

mikeh · ‎11-23-2010

As always, it's going to be very difficult for anyone to help you unless you post the exact error you're getting.

Mike

croc · ‎11-23-2010

Sorry about that.
It first complained about navigator (var browser = navigator.appName

on index.jsp, i commented it out and I got the error below:


he Web Script /alfresco/service/sita/dbsave has responded with a status of 500 - Internal Error.

500 Description:   An error inside the HTTP server which prevented it from fulfilling the request.
 
Message:   10220012 Wrapped Exception (with status template): 10220202 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10220201 ReferenceError: "document" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#28)
    
Exception:   org.mozilla.javascript.EcmaError - ReferenceError: "document" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#28)
    
   org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3350)
   org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3340)
   org.mozilla.javascript.ScriptRuntime.notFoundError(ScriptRuntime.java:3413)
   org.mozilla.javascript.ScriptRuntime.name(ScriptRuntime.java:1612)
   org.mozilla.javascript.gen.c37._c1(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js:28)
   org.mozilla.javascript.gen.c37.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:108)
   org.mozilla.javascript.gen.c37._c0(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js:48)
   org.mozilla.javascript.gen.c37.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:393)
   org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2834)
   org.mozilla.javascript.gen.c37.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.gen.c37.exec(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.alfresco.repo.jscript.RhinoScriptProcessor.executeScriptImpl(RhinoScriptProcessor.java:453)
   org.alfresco.repo.jscript.RhinoScriptProcessor.execute(RhinoScriptProcessor.java:179)
   org.alfresco.repo.processor.ScriptServiceImpl.executeScript(ScriptServiceImpl.java:268)
   org.alfresco.repo.web.scripts.RepositoryScriptProcessor.executeScript(RepositoryScriptProcessor.java:102)
   org.springframework.extensions.webscripts.AbstractWebScript.executeScript(AbstractWebScript.java:971)
   org.springframework.extensions.webscripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:86)
   org.alfresco.repo.web.scripts.RepositoryContainer$2.execute(RepositoryContainer.java:367)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:325)
   org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:417)
   org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:434)
   org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:298)
   org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:319)
   org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:177)
   org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:116)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
   org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
   org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
   java.lang.Thread.run(Thread.java:619)
    
Exception:   org.alfresco.error.AlfrescoRuntimeException - 10220201 ReferenceError: "document" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#28)
    
   org.alfresco.repo.jscript.RhinoScriptProcessor.executeScriptImpl(RhinoScriptProcessor.java:469)
    
Exception:   org.alfresco.scripts.ScriptException - 10220202 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10220201 ReferenceError: "document" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#28)
    
   org.alfresco.repo.jscript.RhinoScriptProcessor.execute(RhinoScriptProcessor.java:183)
    
Exception:   org.springframework.extensions.webscripts.WebScriptException - 10220012 Wrapped Exception (with status template): 10220202 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10220201 ReferenceError: "document" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#28)
    
   org.springframework.extensions.webscripts.AbstractWebScript.createStatusException(AbstractWebScript.java:740)
    
Server:   Enterprise v3.3.1 (96) schema 4,013
Time:   22 Nov 2010 4:15:00 PM
    
Diagnostics:   Inspect Web Script (org/alfresco/sita/dbsave/dbsave.post)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

croc · ‎11-23-2010

Below is the error when function createObject() is not commented out:


The Web Script /alfresco/service/sita/dbsave has responded with a status of 500 - Internal Error.

500 Description:   An error inside the HTTP server which prevented it from fulfilling the request.
 
Message:   10230006 Wrapped Exception (with status template): 10230573 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10230572 ReferenceError: "navigator" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#7)
    
Exception:   org.mozilla.javascript.EcmaError - ReferenceError: "navigator" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#7)
    
   org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3350)
   org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3340)
   org.mozilla.javascript.ScriptRuntime.notFoundError(ScriptRuntime.java:3413)
   org.mozilla.javascript.ScriptRuntime.name(ScriptRuntime.java:1612)
   org.mozilla.javascript.gen.c44._c1(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js:7)
   org.mozilla.javascript.gen.c44.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:108)
   org.mozilla.javascript.gen.c44._c0(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js:16)
   org.mozilla.javascript.gen.c44.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:393)
   org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2834)
   org.mozilla.javascript.gen.c44.call(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.mozilla.javascript.gen.c44.exec(workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js)
   org.alfresco.repo.jscript.RhinoScriptProcessor.executeScriptImpl(RhinoScriptProcessor.java:453)
   org.alfresco.repo.jscript.RhinoScriptProcessor.execute(RhinoScriptProcessor.java:179)
   org.alfresco.repo.processor.ScriptServiceImpl.executeScript(ScriptServiceImpl.java:268)
   org.alfresco.repo.web.scripts.RepositoryScriptProcessor.executeScript(RepositoryScriptProcessor.java:102)
   org.springframework.extensions.webscripts.AbstractWebScript.executeScript(AbstractWebScript.java:971)
   org.springframework.extensions.webscripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:86)
   org.alfresco.repo.web.scripts.RepositoryContainer$2.execute(RepositoryContainer.java:367)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:325)
   org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:417)
   org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:434)
   org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:298)
   org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:319)
   org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:177)
   org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:116)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
   org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
   org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
   java.lang.Thread.run(Thread.java:619)
    
Exception:   org.alfresco.error.AlfrescoRuntimeException - 10230572 ReferenceError: "navigator" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#7)
    
   org.alfresco.repo.jscript.RhinoScriptProcessor.executeScriptImpl(RhinoScriptProcessor.java:469)
    
Exception:   org.alfresco.scripts.ScriptException - 10230573 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10230572 ReferenceError: "navigator" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#7)
    
   org.alfresco.repo.jscript.RhinoScriptProcessor.execute(RhinoScriptProcessor.java:183)
    
Exception:   org.springframework.extensions.webscripts.WebScriptException - 10230006 Wrapped Exception (with status template): 10230573 Failed to execute script '/org/alfresco/sita/dbsave/dbsave.post.js (in repository store workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts)': 10230572 ReferenceError: "navigator" is not defined. (workspace://SpacesStore/Company Home/Data Dictionary/Web Scripts/org/alfresco/sita/dbsave/dbsave.post.js#7)
    
   org.springframework.extensions.webscripts.AbstractWebScript.createStatusException(AbstractWebScript.java:740)
    
Server:   Enterprise v3.3.1 (96) schema 4,013
Time:   23 Nov 2010 11:32:58 AM
    
Diagnostics:   Inspect Web Script (org/alfresco/sita/dbsave/dbsave.post)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

kevinr · ‎11-23-2010

Can you post the entire script? I'm not quite sure what you are trying to do.

croc · ‎11-23-2010

I need to connect to the database and insert data in alfresco. So i have created to files, the index and inser.jsp using ajax to do that, now i wanna move that logic to alfresco. But I am getting errors.

So in short I am looking for a way to connect to database in alfresco. I have created a form, i now want to send the data from that form to the database and be able to read it at a later stage.

gyro_gearless · ‎11-23-2010

If i remember right, some of Rhinos fancy features are not available to webscripts, most notably the creation of arbitrary Java objects

Thus, the most alfrescan way of doing things like direct database access would be writing some custom action (i.e., an subclass of ActionExecuterAbstractBase) in java.

However, there some points worth noting:

* Do you really need to use your own database? In most cases you are better off by doing things in Alfresco itself, e.g. by extending the document model and storing your data in custom node properties.

* If you really really want to access your own database, you should not directly load the JDBC-Driver - in a J2EE environment, you would rather define a database connection pool (this can be either done in Tomcat itself, or even better by providing some fancy Spring bean).

HTH
Gyro

croc · ‎02-23-2011

I was able to get data from the database into my dropdownlist in Alfresco V3.3x, but it doesn't work anymore in V3.4.

I have created a Java class that connects to the database, get the values and passes them to my model. then inside form.get.js file under createFieldConstraint method, i was populating the list by assigning a list like: fieldDef.control.params.options = constraintParams.status.join(); if my list name is status.
and in my model I have:

<constraint name="wfl

rocurementRequestStatus" type="ListOfValuesQueryConstraint">
            <parameter name="status">
                <list/>
            </parameter>
</constraint>

This was working fine, but now it doesn't work on V 3.4. form.get.js is not available anymore. can someone tell help me

Thanks,
Croc

anand6105 · ‎02-04-2013

Hi Croc,

I have also a similar requirement for my project. Here we have created custom forms out of alfresco, but we are not able to connect it to the alfresco database(PostgreSQL). We are using 4.2.c version of Alfresco community. ZIf you achieved in doing this can you share the steps with us. It would be of great help.

Thanks
Anand.

Hyland Connect

Connect to Database using javascript/ajax in alfresco