Hello, I am currently using docker openHAB 4.1.1 on host system Ubuntu 22.04 LTS. I have problems with implementing a rule. openHAB is set up with docker-compose using “network_mode: host”. On the host mariaDB is running. Using the shell command
mysql -u openhab -pxxx -D Telefonverzeichnis1 -e “SELECT Name FROM Kontakte1 WHERE Telefonnummer=‘yyy’”
I receive a caller name for a phone number as input.
I have already designed a rule which sends an incoming phone number as speech output to Alexa. For the last two weeks I am trying to expand the rule to generate the caller name from the phone number via mariadb. There are various problems:
I configured JDBC Persistence MariaDB with
Datenbank-URL jdbc:mariadb://192.168.178.xxx:3306/Telefonverzeichnis1. Shall I use the IPv4 address of host network 192.168.178.xxx or the address for docker, e.g. 172.17.0.1?
I made several rule approaches, e.g.
…
var String url = ‘jdbc:mariadb://192.168.178.xxx:3306/Telefonverzeichnis1’
var String user = ‘openhab’
var String password = ‘xxx’
var String sql_query = “"SELECT Name FROM Kontakte1 WHERE Telefonnummer=‘015231234567’"”
Connection con = DriverManager.getConnection(url, user, password)
var stmt = con.prepareStatement(sql_query)
var rs = stmt.executeQuery()
if (rs.next()) {
val name = rs.getString(“name”)
logInfo(“default.rules”, “Name {}”, name)
} else {
logInfo(“default.rules”, “No name found for the given phone number”)
}
rs.close()
stmt.close()
con.close()
…
Usign this code I got several errors and therefore I have the following questions:
a. Do I have to establish con, when I have already configured jdbc.cfg?
b. How does the code look like to receive a string with the caller name?
You’re course to using code fences correctly but not quite.
```
code goes here
```
What rules language is this? You’ve but down any imports. UI or file?
Assuming you can import the library to talk to your DB into the rule, yes. OH’s persistence API is it’s own thing and does not support arbitrary SQL (or the equivalent for rrd4j, InfluxDB, MongoDB, etc none of which support SQL) from rules.
If it’s not getting historic values from a specific Item you are on your own.
That’s not going to be OH specific. Assuming you can somehow import the libraries you need to connect (may be impossible in some rules languages) what you do with that library is going to be defined by that library. OH’s persistence isn’t involved.
What rules language is this? You’ve but down any imports. UI or file?
For creating the rule, I used the UI, category “rules” and tried to execute an inline script:
“Edit Script application/vnd.openhab.dsl.rule”
Regarding the language, I intended to use java. However, I also read, that it is not allowed to use the import command within the rule script, e.g. “import java.sql.DriverManager”. Hence, the DriverManager commands are not known so far.
Based on the fact, that importing the libraries to connect may take a while, I thought of an easier approach. What about storing name and phone number as an array inside openHAB?
Do you have any hints?
You chose “vnd.openhab.dsl.rule” as the rules language. Rules DSL is not Java. It has access to the core Java classes and a number of the Classes that make up openHAB, but it is not Java in structure, syntax nor how it works overall.
If you want to create rules in Java, you need to install the Java Rule add-on from the marketplace. But the last time I looked that does not support creating rules through the UI.
Other rules languages available through separate add-ons include:
thanks for your hints. I decided to use application/x-ruby for creating the rule. Instead of using the input of mariaDB I created a file in csv format to store the names and phone numbers. Meanwhile, I got it running and maybe some of you want to use parts of the code for your own rules:
require 'csv'
def identify_call(file_path)
# Check if the file exists
unless File.exist?(file_path)
logger.info("File does not exist.")
return
end
# Get phone number
extractedstring = AVMFritzProdukte_EingehenderAnruf.state.to_s
# format of extractedstring = "5368,12345", while 12345 is the incoming phone number
commaindex = extractedstring.index(",")
extractedstring = extractedstring[(commaindex + 1)..-1]
# Open the file using CSV module
xhit = 0
CSV.foreach(file_path, headers: true) do |row|
name = row['Name']
phone_number = row['Telefonnummer']
# logger.info("Name: " + name + ", Telefonnummer: " + phone_number)
if phone_number == extractedstring
Alexa_Note2.command("Eingehender Anruf von " + name)
xhit = 1
end
end
if xhit == 0
extractedstring = extractedstring.strip.gsub(/(\d)/, '\1 ')
Alexa_Note2.command("Eingehender Anruf mit der Rufnummer " + extractedstring)
return
end
end
# Path to the file
file_path = 'phone_data.csv'
# Call the function to read the file
identify_call(file_path)
That’s not going to be OH specific. Assuming you can somehow import the libraries you need to connect (may be impossible in some rules languages) what you do with that library is going to be defined by that library. OH’s persistence isn’t involved.