Hi guys!

I want to fill select box which list all username from Users. So,whenever i delete one username from Users automatically it does not appear at select box. I try to make query ("Select username from.....")..but I stuck at from because i don't know what table for Users.So,where can I locate database like User so that I can make query to retrieve record to fill select box?

Thanks.

  • No labels

13 Comments

  1. Owen Ong

    Hi Sarah,

    Users are store in dir_user table.

    Hope this's help.

    Owen

    1. sarah

      Thanks Owen. I try it first.

      1. sarah

        Hi guys!

        Below are codes to fill select box with username from dir_user. However, username are not appear at select box even i try make a query which select all username and use FormRow to list it at select box. Anyone can guide if  I should make some changes to these codes?

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

        Connection con = null;

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

         if(!con.isClosed()){
          PreparedStatement stmt = con.prepareStatement("("SELECT *  
          FROM dir_user WHERE username=" ")");
          ResultSet rs = stmt.executeQuery();

          while (rs.next()) {
        public FormRowSet user_rec()

        Unknown macro: {FormRowSet select_user = new FormRowSet();   select_user.setMultiRow(true);   FormRow user_row = new FormRow();            user_row.put(FormUtil.PROPERTY_VALUE, " ");     user_row.put(FormUtil.PROPERTY_LABEL, " ");   select_user.add(user_row);}

           return user_row;
          }
        return user_rec;
          }
         }
        } 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";

  2. Hugo

    Hi Sarah,

    You can actually achieve this by using the Form Options Binder to DirectoryManager (User, Group, Department, Organization) binder. I have fixed your codes as well. Try the following one.

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

Connection con = null;

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

FormRowSet select_user = new FormRowSet();
select_user.setMultiRow(true);

if(!con.isClosed()){
	PreparedStatement stmt = con.prepareStatement("SELECT * FROM dir_user");
	ResultSet rs = stmt.executeQuery();


	while (rs.next()){
		FormRow user_row = new FormRow();
		user_row.put(FormUtil.PROPERTY_VALUE, rs.getString(2));
		user_row.put(FormUtil.PROPERTY_LABEL, rs.getString(1));
		select_user.add(user_row);
	}
}

return select_user;

    PS: Try to use code block macro when you embed codes in here next time.

    1. sarah

      Hi Hugo!

      I had tried the codes that you fixed,still not working. Username still not fill in the select box. Besides that, I'm just a beginner and for starting I used Joget Workflow v3 Community Edition.. :-). Or I need to add:

      while (rs.next()){
		FormRow user_row = new FormRow();
		user_row.put(FormUtil.PROPERTY_VALUE,"user_1");
		user_row.put(FormUtil.PROPERTY_LABEL, "user_1");
		select_user.add("user_1");

                user_row.put(FormUtil.PROPERTY_VALUE,"user_2");
		user_row.put(FormUtil.PROPERTY_LABEL, "user_2");
		select_user.add("user_2");
                }

      1. Owen Ong

        Hi Sarah,

        I just tested the code provided by Hugo. It's work.

        You just need to copy and paste the code to Bean Shell Form Binder and put the correct database connection will do.

        I think you may be put the wrong database name. Default database should be jwdb.

        con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?useUnicode=true&characterEncoding=UTF-8", "root", "");

        1. sarah

          Thank you very much! It works!

          1. sarah

            Hi everyone!

            If I used (SELECT * FROM dir_user) or (SELECT id,username FROM dir_user), it runs perfectly. However, if I use (SELECT username FROM dir_user) or (SELECT id,firstName FROM dir_user), it doesn't work. May I know why? Or is it related to key table which is "id" and sort by "username"?

            Thanks.

  3. Owen Ong

    Hi Sarah,

    In the code below, it expect at least 2 columns selected in your query. So, first column will be used as Label and second column will be used as Value.

    user_row.put(FormUtil.PROPERTY_VALUE, rs.getString(2));
user_row.put(FormUtil.PROPERTY_LABEL, rs.getString(1));

    I tested with the below query that you used, it is working.

    PreparedStatement stmt = con.prepareStatement("SELECT firstName, id FROM dir_user");

    1. sarah

      Oh, really thanks. It works!

      1. sarah

        Hi guys!

        I have a problem here. After submit form to user (choose username from select box), the user will get the email.

        And I used email tools which map tool to plug in. The problem is email can't send out. I've used #performer.activityDefId.email# but it doesn't work. However, I questioned myself, I want to send email to the user. Can anyone helps me?

        1. Hugo

          Hi Sarah,

          Please try to post in appropriate topic. This would be confusing for others to navigate. Thanks.

    2. Dinx

      hello, i got one question here. How  to write the code if i want the firstName according to which department there are placed? For example ICT department, the select box just show up firstName of user in ICT department.. really appreciate your feedback.

      Beginner Dinx