The script at form page validation not working correctly.

Hi,

I have created a form login page with two fields email and password and a submit button in bonitasoft studio community edition 6.4.1.

There is a form page validator that will check whether the email id and password
entered by the user in the login form already exist in the database.
If yes then submit will work else error will be given that email id or password is invalid.

The validator type is groovy expression and in the parameter I have written the groovy script :
import groovy.sql.Sql;
import java.sql.Driver;

def host = “localhost:3306”;
def user = “root”;
def pass = “root”;
def schema = “test”;
def sql = BonitaSql.newInstance(“jdbc:mysql://$host/$schema”,“$user”,“$pass”, new com.mysql.jdbc.Driver());
def query=“”;
query = “select count(‘*’) from test.project where email='”+loginemail+“’ and password='”+loginpassword+“';”
def rowcount = 0;
rowcount = sql.execute(query);
if(rowcount == 0)
return false; //this does not work or checked.
else
return true; //this is returned always irrespective of what is the outcome of the if condition.

The return type for the validator is boolean.
The issue is that the value returned by the script is always the last return statement.

There are two possibilities :

  1. The select count does not return the int value as I have put rowcount = sql.execute(query); and then compared the rowcount in if condition.
  2. The if condition is not correct.

Hope the issue is clear.

Regards,

Mohit.

Hi,

Anyone who can help in this issue.

Regards,

Mohit.

my quick 2 cents:
1- Have you tested the SQL query ? I would have use ‘like’ instead of ‘=’:
→ query = “select count(‘*’) from test.project where email like '”+loginemail+“’ and password like '”+loginpassword+“';”
2- I have found this on the internet ( Processing SQL Statements with JDBC )
→ execute: Returns true if the first object that the query returns is a ResultSet object.
I don’t know enough the sql package you use in the script. May be there is a misunderstanding about the “execute” method returned value.

Jérôme

Hi,

The database is mysql and the query :
“select count(‘*’) from test.project where email like '”+loginemail+“’ and password like '”+loginpassword+“';”

runs fine when I run it in the mysql query browser.

I have used similar script to insert values from the form fields into the database and that works fine.
It seems that the query is correct but the result value that I am getting by running select count(*) query may be the cause of the issue.

Regards,

Mohit.

Here again, just an idea: long time ago I solved a similar problem with a query such as:
select email from test.project where email like ‘“+loginemail+”’ and password like ‘“+loginpassword+”’;"
Indeed, there is no need to count the records. You just want to know whether there is a record or not.

This being said, may be there is also a misunderstanding here. I believe even with a count(), the execution of a select query returns a resultset and it will contain the number of records. The java code would look like:

query = "select count('
') as nbRecords from test.project where…"
ResultSet rs = stmt.executeQuery(query);
if (rs.next()){
int nbOfRecords = rs.getInt(“nbRecords”); // if the number of records is required

return true
} else {
return false
}

(you may need to translate into groovy)