Problem Connecting to Database in custom module.

[shaggi7]

Hi, I am building a custom module to get all registerd user from a list of users . I am trying to coneect to database. ODBC are all set up as i am seeing the output of the following command:

$ isql MySQL ejabberd 9009
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

Ejabberd compiling succesfully. But when running the Live command i am gettin the following errors:

$ ejabberdctl live
--------------------------------------------------------------------

IMPORTANT: ejabberd is going to start in LIVE (interactive) mode.
All log messages will be shown in the command shell.
You can interact with the ejabberd node if you know how to use it.
Please be extremely cautious with your actions,
and exit immediately if you are not completely sure.

To exit this LIVE mode and stop ejabberd, press:
  q().  and press the Enter key

--------------------------------------------------------------------
To bypass permanently this warning, add to ejabberdctl.cfg the line:
  EJABBERD_BYPASS_WARNINGS=true
Press return to continue

Erlang/OTP 22 [erts-10.4.4] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1]

Eshell V10.4.4  (abort with ^G)
(ejabberd@localhost)1> 15:45:38.418 [notice] Changed loghwm of /usr/local/var/log/ejabberd/error.log to 100
15:45:38.418 [notice] Changed loghwm of /usr/local/var/log/ejabberd/ejabberd.log to 100
15:45:38.454 [info] Loading configuration from /usr/local/etc/ejabberd/ejabberd.yml
15:45:38.546 [info] Configuration loaded successfully
15:45:38.644 [info] Building language translation cache
15:45:38.701 [warning] Path /etc/letsencrypt/live/localhost/fullchain.pem is empty, please make sure ejabberd has sufficient rights to read it
15:45:38.702 [warning] Path /etc/letsencrypt/live/localhost/privkey.pem is empty, please make sure ejabberd has sufficient rights to read it
15:45:38.731 [info] Loading modules for 192.168.4.69
15:45:38.784 [info] >>>>>>>>>>>>>>>>>>>>>>>>>>>HERE is my MODULE<<<<<<<<<<<<<<<<<<<<<<<<<<<<
15:45:38.791 [critical] Internal error of module mod_search_users has occurred during start:
** Options: #{}
** exception error: no match of right hand side value {error,
                                                     "[unixODBC][Driver Manager]Data source name not found and no default driver specified SQLSTATE IS: IM002 Connection to database failed."}
   in function  mod_search_users:start/2 (src/mod_search_users.erl, line 16)
   in call from gen_mod:start_module/4 (src/gen_mod.erl, line 149)
   in call from lists:foreach/2 (lists.erl, line 1338)
   in call from gen_mod:start_link/0 (src/gen_mod.erl, line 76)
   in call from supervisor:do_start_child_i/3 (supervisor.erl, line 379)
   in call from supervisor:do_start_child/2 (supervisor.erl, line 365)
   in call from supervisor:'-start_children/2-fun-0-'/3 (supervisor.erl, line 349)
   in call from supervisor:children_map/4 (supervisor.erl, line 1157)
15:45:38.791 [critical] ejabberd initialization was aborted because a module start failed.

What is wrong . please Help!!!!

[zinid]

Why don't you use MySQL driver directly?

[shaggi7]

Hi, I was searching "Setting the DSN" to set up a datasource . Well now forget about it.

Why don't you use MySQL driver directly?

well i am now using MySQL/OTP (https://github.com/mysql-otp/mysql-otp) I downloaded it , coppied all file from it's SRC folder to ejabberd SRC folder, Compiled and installed.

But stll i am getting the error.

16:30:09.042 [info] >>>>>>>>>>>>>>>>>>>>>>>>>>>HERE is my MODULE<<<<<<<<<<<<<<<<<<<<<<<<<<<<
16:30:09.047 [critical] Internal error of module mod_search_users has occurred during start:
** Options: #{}
** exception error: undefined function mysql:start_link/1
   in function  mod_search_users:start/2 (src/mod_search_users.erl, line 16)

Its the piece of code where the error is coming.

start(_Host, _Opts) ->
    ?INFO_MSG(">>>>>>>>>>>>>>>>>>>>>>>>>>>HERE is my MODULE<<<<<<<<<<<<<<<<<<<<<<<<<<<<", []),
    odbc:start(),
    {ok, Ref} =  mysql:start_link([{host, "localhost"}, {user, "ejabberd"}, {password, "9009"}, {database, ejabberd}]),
    ?INFO_MSG('Ref========================>',[Ref]),
    %odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (FIRSTNAME char varying(20),
    %LASTNAME char varying(20), AGE integer, SEX char(1), INCOME integer)"),
    ?INFO_MSG('Refs ==============>', [Ref]),
    ok.

[zinid]

You don't need to compile or download anything, just do as written in the doc:

./configure --enable-mysql

[shaggi7]

How to access it. Like I want to perfoem some queries on the database. Right now I am using this code :

start(_Host, _Opts) ->
    ?INFO_MSG("My message!!!!", []),
    ?INFO_MSG("Hello, ejabberd world!", []),
    {ok, Pid} = mysql:start_link([{host, "localhost"},{user, "root"},{password, "9009"}, {database, "ejabberd"}]),
    ?INFO_MSG("PID: ", [Pid]),
    ok.

I want to get all users in user table.

[zinid]

You don't need to create a MySQL connection by hands, configure mysql in ejabberd.yml, then look into any module with _sql.erl suffix for an example.