How to use ODBC on Fuxa?

[IonuDragomir1]

Hi, I have seen that in the latest version was added ODBC plugin. I have created a new ODBC Data Source.

How can I use it in FUXA?

[MatthewReed303]

@IonuDragomir1 it's not in the latest release yet, will be in the next. Check out the ODBC branch. Are you using docker? I recommend doing a docker build from the odbc branch. The docker build will setup and install all the odbc drivers for all the major databases such as mySQL, PostgreSQL, MSSQL etc in the docker file you will need to add on the git the branch, -b odbc to pull the odbc branch rather than the master before you do the build.

Download the docker file from: https://github.com/frangoteam/FUXA/blob/odbc/Dockerfile

Edit in docker file to:

# Clone FUXA repository
RUN git clone -b odbc https://github.com/frangoteam/FUXA.git

Build the docker file: sudo docker build -t fuxa-odbc . You will now have an image called fuxa-odbc you can now use in your docker compose fine.

version: '3.5'

services:
    fuxa:
        image: fuxa-odbc
        network_mode: "host"
        volumes:
            - ./fuxa_appdata:/usr/src/app/FUXA/server/_appdata
            - ./fuxa_db:/usr/src/app/FUXA/server/_db
            - ./fuxa_logs:/usr/src/app/FUXA/server/_logs
        environment:
            - TZ=Pacific/Auckland
        restart: always

You can now start it using sudo docker compose up -d note you can see in above example I use host network mode so you have direct access to devices on your network likes PLCs and Database etc

You can then setup a odbc device and a connection string for you DB DRIVER=PostgreSQL;SERVER=localhost;PORT=5432;UID=admin;PWD=mypassword;DATABASE=testDB

Now you can create a server side script and make a query: Hi Matthew, I updated the odbc branch, new there is a sys function getDevice to use in script:

let device = $getDevice('odbc', true);
let result = await device.pool.query(`SELECT * FROM SampleTable`);

You can then put the data into a table, chart etc write to the DB

[Kapket]

Where do I put connection string? In odbc.ini?

[MatthewReed303]

In the Fuxa Devices ODBC settings in the UI

[Kapket]

Here, right? But there is no place for port.

[MatthewReed303]

@Kapket just put the complete string in the Data Source Name field.

@unocelli can we add Fields for all of these parameters to make it easy. DRIVER=PostgreSQL;SERVER=localhost;PORT=5432;UID=admin;PWD=mypassword;DATABASE=testDB

[abishur]

I was following the advice in this issue, but can't seem to get connected to a database. I've tried with both mySQL and PostgreSQL. With mySQL I was able to connect to it from another computer using mySQL workbench, but within FUXA it kept saying there was an issue connecting to the database. In the DSN field I put

DRIVER=MySQL;SERVER=localhost;PORT=3306;UID=myuser;PWD=mypassword;DATABASE=mySchema

When I tried with Postgre I used the example Matthew lists above.

Also, I did have to change the Dockerfile. Line 9 and Line 47 both have you trying to clone the FUXA repository, in your docker image which causes it to fail when you get to line 47. I replaced line 9 with line 47 and commented out the existing line 47 and it worked.

Then when I created the container from the image I made sure to pass in port 1881 so I could still reach the web server. Are there any other ports or volumes I needed to map in to make sure it can each my SQL server?

[MatthewReed303]

@abishur we are currently fixing dockerfile now

If you execute into the container it's self can you ping your DB IP/host? SERVER=localhost this needs to be your database IP address or hostname if dns setup.

If you database is running in docker and not host networking mode, you need to add that network to the Fuxa container so it has access, please try with Fuxa in host network mode and database in host network mode and use the IP address of the PC you have them running on.

[abishur]

the mysql database is running on the host machine (not in a docker container). I changed the ip address from localhost to the ip of my host machine and confirmed that I can ping it from the docker container.

I'm still getting an error message that says "getendpoints-error: Error: [odbc] Error connecting to the database

[MatthewReed303]

@abishur here are the correct names for the drivers https://github.com/frangoteam/FUXA/blob/master/odbc/odbcinst.ini

DATABASE=mySchema are you sure this is the database name?

You can ping the host, but firewall not blocking access to the port? nmap -p 3306 yourIP/host from the Fuxa container ( may have to install nmap in the container )

See https://github.com/markdirish/node-odbc/ for more details

@unocelli could the issue be your DSN field where we are putting the complete connection string?

[abishur]

I installed nmap and it reports 1881 and 3306 are both open (which makes sense since I can connect to the database from another PC using workbench)

I've double checked permissions and table names and everything looks like it should be connecting, but it keeps giving me that error connecting to the database message

[MatthewReed303]

@abishur I have just done a test with PostgreSQL on latest master, please build the latest dockerfile with sudo docker build -t fuxa-odbc --no-cache .

Also you now need to enter the user and password in the fields and remove from DSN connection string

I will spin up mySQL and give that a go also

[abishur]

I had some issues building the docker file on my Pi zero 2 w, but once I swapped over to a Pi 4, it built fine.

It still won't let me connect to MySQL (I removed the UID and PWD fields from the DSN string but kept the rest the same), but I'll try installing PostgreSQL this evening and see if it works then.

[abishur]

I can confirm that after making the docker image on the Pi 4, I was able to transfer it to my Pi Zero 2 W and connect to the PostgreSQL server! 🎉 There appears to be some issue with MySQL at this point in time, but I'm counting this as a win.

PostgreSQL is a new one for me, so it took me a minute to realize I needed to edit the file /etc/postgresql/15/main/postgresql.conf file, uncomment the listen_address line and change the 'localhost' to '*' Then I had to edit the file /etc/postgresql/15/main/pg_hba.conf file and add host all all 0.0.0.0/0 md5 I could make this more secure by entering the subnet information for my docker machines, but this is good for now.

[MatthewReed303]

@abishur is your Pi Zero 2 W 32bit? mySQL ODBC driver does not support arm 32bit I'm just doing a build and installing mySQL now and will test and see if I can get it to work

I did not have to change any of that for postgresql, I used docker compose along with pgadmin

version: "3.8"

services:
  db:
    image: postgres
    container_name: local_pgdb
    restart: always
    network_mode: "host"
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: mypass
    volumes:
      - local_pgdata:/var/lib/postgresql/data
  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin4_container
    restart: always
    ports:
      - "8888:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: user
      PGADMIN_DEFAULT_PASSWORD: mypass
    volumes:
      - pgadmin-data:/var/lib/pgadmin

volumes:
  local_pgdata:
  pgadmin-data:

[abishur]

I'm using Pi OS Lite (64-bit) released 2024-03-15. I had to make the PostgreSQL changes since installed it directly on the host machine and not in a docker. I HAD hoped to cut down on any lurking gotchas by not having it in a docker container, but I ended up creating a couple new ones for me to deal with by installing it on the host, lol.

[MatthewReed303]

@abishur The MySQL drivers were not being installed correctly, I have created a PR https://github.com/frangoteam/FUXA/pull/1246

I have also added mariaDB ODBC, you will just need to wait for @unocelli to merge it.

There is still an issue with Fuxa/node-odbc where it crashes using MySQL driver, the driver is working from within the Docker container. You can test yourself

myodbc-installer -s -a -c2 -n "test" -t "DRIVER=MySQL;SERVER=YourIP;PORT=3306;DATABASE=testDB;UID=User;PWD=MyPass"

Then

isql test

You can then test your SQL commands from the console

[MatthewReed303]

Hi @abishur I have now got MySQL driver now working, the crash is caused by libssl if you add ;SSLMODE=DISABLED to the end of the connection string DRIVER=MySQL;SERVER=YourIP;PORT=3306;DATABASE=testDB;SSLMODE=DISABLED

You will have to just wait for the PR to be done by @unocelli or you can try it from my Github fork, just be sure to change the git clone on the docker file from the Fuca github to my github

[hxjackcn]

Hi @abishur I have now got MySQL driver now working, the crash is caused by if you add to the end of the connection string libssl``;SSLMODE=DISABLED``DRIVER=MySQL;SERVER=YourIP;PORT=3306;DATABASE=testDB;SSLMODE=DISABLED

You will have to just wait for the PR to be done by @unocelli or you can try it from my Github fork, just be sure to change the git clone on the docker file from the Fuca github to my github

DRIVER=MSSQL;SERVER=192.168.33.136;PORT=1433;DATABASE=etong_rules

The database is correct；I can connect to the DSN that comes with the local operating system, but I cannot connect to it in fuxa.

[MatthewReed303]

@hxjackcn can you try the FreeTDS driver

DRIVER=FreeTDS;SERVER=192.168.33.136;PORT=1433;DATABASE=etong_rules

Please also go through all the checks in the above steps to debug. Please make sure you are running the latest master and have done a correct docker build, all the drivers were fixed.

Check you can ping the MSSQL server from within the docker container and port is open with nmap

[hxjackcn]

I use it under Windows, I don't need docker.

There is no problem with the remote database server port via telnet, and a connection can be established via other software.

[hxjackcn]

The latest compiled fuxa is used.

[MatthewReed303]

@hxjackcn you need to run it in docker to use the built in drivers, the docker build will install and setup the odbc drivers. See the install script here: https://github.com/frangoteam/FUXA/blob/master/odbc/install_odbc_drivers.sh and these are there drivers in Linux https://github.com/frangoteam/FUXA/blob/master/odbc/odbcinst.ini

If you are using directly in windows you will have to use the DSN name of your connection, the Linux drivers will not work. @unocelli had it working within windows using DSN, maybe he can guide you here. I recommend using docker, you can install docker on windows or WSL on windows and run it there.