Not able to connect to mysql database

Created by poonam chaudhari, last modified on Jul 24, 2012

Hi,I am new to joget.I have created a form with textfield name and wants to insert the entered data in name textfield to mysql table.But when i am ruunin the application and submitting the data,i am getting the data in the datalist created but not able to find that data in mysql table. for inserting data into the table i am using database update tool.In the plugin configuration i have used the url:jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF-8.and wants to run the following query 'insert into jwdb.test (name) values ("poonam");'.currently i have hardcoded the value to test.Test the previously created table in the jwdb database.

Flow of my process is : start->activity->tool(database update tool)->end

We are using joget v3 community edition.

Plz suggest the solution its very urgent for us...

Regards,

poonam chaudhari

No labels

4 Comments

Hugo
Hi Poonam,

Welcome to the Joget community. It's actually a bit hard to read as there's no proper line break in your posting.

Since you are new here, maybe you can start looking at Build Your First App first to find out on how Joget works.

Cheers.
- Permalink
- Jul 25, 2012
poonam chaudhari
Thanks for your reply..

I came to know that using form binder all the data gets saved in mysql automatically.

If i want to perform delete or update operation on same table how can i do that?

I am using database update tool for executing sql query.But not able to figure it out how exactly the plugin works.

Can you please suggest me steps to use database update tool?

Or i have to use bean shell store binder for executing sql query?

Its very urgent for us...

Regards,

Poonam chaudhari
- Permalink
- Jul 25, 2012
poonam chaudhari
Code i am using in bean shell store binder for executing sql query:

import java.sql.*;
import java.util.*;

Connection con = null;

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?useUnicode=true&characterEncoding=UTF-8", "root", ""); // declare datasource

if(!con.isClosed())

Unknown macro: { PreparedStatement stmt = con.prepareStatement("delete from app_ad_test where c_field1="preranachaudhari""); // SQL query ResultSet rs = stmt.executeQuery();    }

} catch(Exception e) {
System.err.println("Exception from BeanShell activity: " + e.getMessage());
} finally{
try

Unknown macro: { if(con != null)   con.close(); }

catch(SQLException e) {}
}

return "1";

But i am getting the following error:

shark.WorkflowScriptingManager' implementation of Scripting API
2012-07-26 09:41:10,737: SharkEngineManager -> Working without Security API impl
ementation - SecurityManager is not specified.
2012-07-26 09:41:11,667: StandardToolAgentManager -> Working with 'org.enhydra.s
hark.appmappersistence.DODSApplicationMappingAdmin' implementation of Applicatio
nMapPersistence API
2012-07-26 09:41:11,667: SharkEngineManager -> Working with 'org.enhydra.shark.t
oolagent.StandardToolAgentManager' implementation of ToolAgentManager API
2012-07-26 09:41:11,667: SharkEngineManager -> Working without wfEngineInteroper
ability API implementation - WfEngineInteroperability implementation is not spec
ified.
2012-07-26 09:41:11,742: SharkUtilities -> synchronizing XPDL cache
Shark -> shark engine initialization is finished, it lasted 3 [s]
Shark -> SharkExampleJSP ready and waiting ...
INFO 26 Jul 2012 09:41:15 org.displaytag.filter.ResponseOverrideFilter - Filte
r initialized. Response buffering is enabled
Jul 26, 2012 9:41:16 AM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive jwdesigner.war
Jul 26, 2012 9:41:17 AM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Jul 26, 2012 9:41:17 AM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Jul 26, 2012 9:41:17 AM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/15 config=null
Jul 26, 2012 9:41:17 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 28162 ms
INFO 26 Jul 2012 09:42:18 org.joget.apps.workflow.security.WorkflowHttpAuthProc
essingFilter - Authentication for user admin: true
Jul 26, 2012 9:56:27 AM org.joget.apps.form.lib.SubmitButton actionPerformed
INFO: -- SubmitButton actionPerformed submit
Jul 26, 2012 9:56:27 AM org.joget.apps.form.lib.BeanShellFormBinder executeScrip
t
WARNING: Error executing script
Parse error at line 11, column 48. Encountered: (
        at bsh.Parser.generateParseException(Unknown Source)
        at bsh.Parser.jj_consume_token(Unknown Source)
        at bsh.Parser.BlockStatement(Unknown Source)
        at bsh.Parser.Block(Unknown Source)
        at bsh.Parser.Statement(Unknown Source)
        at bsh.Parser.IfStatement(Unknown Source)
        at bsh.Parser.Statement(Unknown Source)
        at bsh.Parser.BlockStatement(Unknown Source)
        at bsh.Parser.Block(Unknown Source)
        at bsh.Parser.TryStatement(Unknown Source)
        at bsh.Parser.Statement(Unknown Source)
        at bsh.Parser.BlockStatement(Unknown Source)
        at bsh.Parser.Line(Unknown Source)
        at bsh.Interpreter.Line(Unknown Source)
        at bsh.Interpreter.eval(Unknown Source)
        at bsh.Interpreter.eval(Unknown Source)
        at bsh.Interpreter.eval(Unknown Source)
        at org.joget.apps.form.lib.BeanShellFormBinder.executeScript(BeanShellFo
rmBinder.java:73)
        at org.joget.apps.form.lib.BeanShellFormBinder.store(BeanShellFormBinder
.java:53)
        at org.joget.apps.form.service.FormService.recursiveExecuteFormStoreBind
ers(FormService.java:407)
        at org.joget.apps.form.service.FormService.executeFormStoreBinders(FormS
ervice.java:384)
        at org.joget.apps.form.service.FormService.submitForm(FormService.java:2
50)
        at org.joget.apps.form.lib.SubmitButton.actionPerformed(SubmitButton.jav
a:39)
        at org.joget.apps.form.service.FormUtil.executeActions(FormUtil.java:461
)
        at org.joget.apps.form.service.FormUtil.executeActions(FormUtil.java:469
)
        at org.joget.apps.form.service.FormUtil.executeActions(FormUtil.java:469
)
        at org.joget.apps.form.service.FormUtil.executeActions(FormUtil.java:469
)
        at org.joget.apps.form.service.FormService.executeFormActions(FormServic
e.java:334)
        at org.joget.apps.userview.lib.FormMenu.submitDataForm(FormMenu.java:479
)
        at org.joget.apps.userview.lib.FormMenu.submitForm(FormMenu.java:236)
        at org.joget.apps.userview.lib.FormMenu.getJspPage(FormMenu.java:131)
        at org.joget.apps.userview.model.UserviewMenu.getReadyJspPage(UserviewMe
nu.java:84)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
        at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
        at org.apache.el.parser.AstValue.getValue(AstValue.java:118)
        at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:1
86)
        at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageCon
textImpl.java:925)
        at org.apache.jsp.WEB_002dINF.jsp.ubuilder.view_jsp._jspx_meth_c_005fset
_005f18(view_jsp.java:1543)
        at org.apache.jsp.WEB_002dINF.jsp.ubuilder.view_jsp._jspx_meth_c_005fwhe
n_005f5(view_jsp.java:1465)
        at org.apache.jsp.WEB_002dINF.jsp.ubuilder.view_jsp._jspx_meth_c_005fcho
ose_005f4(view_jsp.java:1419)
        at org.apache.jsp.WEB_002dINF.jsp.ubuilder.view_jsp._jspx_meth_c_005fset
_005f15(view_jsp.java:1349)
        at org.apache.jsp.WEB_002dINF.jsp.ubuilder.view_jsp._jspService(view_jsp
.java:178)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:374)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
42)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:394)
        at org.springframework.security.intercept.web.FilterSecurityInterceptor.
invoke(FilterSecurityInterceptor.java:99)
        at org.springframework.security.intercept.web.FilterSecurityInterceptor.
doFilter(FilterSecurityInterceptor.java:83)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.SessionFixationProtectionFilter.doFil
terHttp(SessionFixationProtectionFilter.java:52)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHt
tp(ExceptionTranslationFilter.java:101)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.providers.anonymous.AnonymousProcessingF
ilter.doFilterHttp(AnonymousProcessingFilter.java:105)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp
(AbstractProcessingFilter.java:278)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp
(AbstractProcessingFilter.java:278)
        at org.joget.apps.workflow.security.WorkflowHttpAuthProcessingFilter.doF
ilterHttp(WorkflowHttpAuthProcessingFilter.java:40)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.logout.LogoutFilter.doFilterHttp(Logo
utFilter.java:89)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.context.HttpSessionContextIntegrationFil
ter.doFilterHttp(HttpSessionContextIntegrationFilter.java:192)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.util.FilterChainProxy.doFilter(FilterCha
inProxy.java:185)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(D
elegatingFilterProxy.java:236)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(Delegat
ingFilterProxy.java:167)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDisp
atcher.java:630)
        at org.apache.catalina.core.ApplicationDispatcher.processRequest(Applica
tionDispatcher.java:436)
        at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationD
ispatcher.java:374)
        at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDis
patcher.java:302)
        at org.springframework.web.servlet.view.InternalResourceView.renderMerge
dOutputModel(InternalResourceView.java:236)
        at org.springframework.web.servlet.view.AbstractView.render(AbstractView
.java:257)
        at org.springframework.web.servlet.DispatcherServlet.render(DispatcherSe
rvlet.java:1183)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(Dispatch
erServlet.java:902)
        at org.springframework.web.servlet.DispatcherServlet.doService(Dispatche
rServlet.java:807)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame
workServlet.java:571)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServ
let.java:511)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrid
eFilter.java:125)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:394)
        at org.springframework.security.intercept.web.FilterSecurityInterceptor.
invoke(FilterSecurityInterceptor.java:109)
        at org.springframework.security.intercept.web.FilterSecurityInterceptor.
doFilter(FilterSecurityInterceptor.java:83)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.SessionFixationProtectionFilter.doFil
terHttp(SessionFixationProtectionFilter.java:67)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHt
tp(ExceptionTranslationFilter.java:101)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.providers.anonymous.AnonymousProcessingF
ilter.doFilterHttp(AnonymousProcessingFilter.java:105)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp
(AbstractProcessingFilter.java:278)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp
(AbstractProcessingFilter.java:278)
        at org.joget.apps.workflow.security.WorkflowHttpAuthProcessingFilter.doF
ilterHttp(WorkflowHttpAuthProcessingFilter.java:40)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.ui.logout.LogoutFilter.doFilterHttp(Logo
utFilter.java:89)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.context.HttpSessionContextIntegrationFil
ter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)
        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringS
ecurityFilter.java:53)
        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain
.doFilter(FilterChainProxy.java:406)
        at org.springframework.security.util.FilterChainProxy.doFilter(FilterCha
inProxy.java:185)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(D
elegatingFilterProxy.java:236)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(Delegat
ingFilterProxy.java:167)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterIntern
al(CharacterEncodingFilter.java:96)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:76)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:286)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:845)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ss(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:44
7)
        at java.lang.Thread.run(Thread.java:619)
- Permalink
- Jul 26, 2012

Owen Ong

Hi poonam chaudhari,

Here is an example of using beanshell script and JDBC as form store binder for your reference.

import org.joget.apps.app.service.*;
import org.joget.apps.app.model.*;
import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import java.util.*;

public FormRowSet StoreUsingJDBC(Element element, FormRowSet rows, FormData formData) {
    Connection con = null;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF-8", "root", "");

        if(!con.isClosed()){
            FormRow row = rows.get(0);
            String id = row.getId();

            //manually handle insert and update by checking the data is exist or not
            String selectQuery = "SELECT username FROM dir_user WHERE username=?";
            PreparedStatement stmt = con.prepareStatement(selectQuery);
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();

            Boolean isExist = false;
            if (rs.next()) {
                isExist = true;
            }

            if (isExist) {
                String updateQuery = "UPDATE dir_user SET firstName = ?, lastName = ?, email = ? WHERE username = ?";
                PreparedStatement ustmt = con.prepareStatement(updateQuery);
                ustmt.setString(1, row.getProperty("firstName"));
                ustmt.setString(2, row.getProperty("lastName"));
                ustmt.setString(3, row.getProperty("email"));
                ustmt.setString(4, id);
                ustmt.executeUpdate();
            } else {
                String insertQuery = "INSERT INTO dir_user (id, username, firstName, lastName, password, email) values (?, ?, ?, ?, 'md5(password)', ?)";
                PreparedStatement istmt = con.prepareStatement(insertQuery);
                istmt.setString(1, id);
                istmt.setString(2, id);
                istmt.setString(3, row.getProperty("firstName"));
                istmt.setString(4, row.getProperty("lastName"));
                istmt.setString(5, row.getProperty("email"));
                istmt.executeUpdate();
            }
        }
    } catch (Exception ex) {
        System.err.println("Exception: " + ex.getMessage());
    } finally {
        try {
            if(con != null)
                con.close();
        } catch(SQLException e) {}
    }
    return rows;
}

return StoreUsingJDBC(element, rows, formData);

Hope this is help.

Best regards,

Owen

Permalink

Jul 31, 2012

Space shortcuts

Page tree

4 Comments

Hugo

poonam chaudhari

poonam chaudhari

Owen Ong