Running customised database query in Local SS
Running customised database query in Local SS
At the moment you can assign DBExecuteScalar to an account and that account can run any query in the database.
It would be nice to have an option to have similar functionality but a little restricted. Instead of giving the user to run any query, the user is only allowed to run few defined queries in the local SS.
This can work like this. The system administrator of the Local SS defined a queries in the config file or database and name it. Now the user assigned to run the application (query defiend in the config/database) will be able to run these queries from the dial plan.
One good application of such functionality is allow a group of users to share the dailplan data with each other.
It would be nice to have an option to have similar functionality but a little restricted. Instead of giving the user to run any query, the user is only allowed to run few defined queries in the local SS.
This can work like this. The system administrator of the Local SS defined a queries in the config file or database and name it. Now the user assigned to run the application (query defiend in the config/database) will be able to run these queries from the dial plan.
One good application of such functionality is allow a group of users to share the dailplan data with each other.
Re: Running customised database query in Local SS
Hi can you explain how this worksjainpj wrote:At the moment you can assign DBExecuteScalar to an account and that account can run any query in the database.
It would be nice to have an option to have similar functionality but a little restricted. Instead of giving the user to run any query, the user is only allowed to run few defined queries in the local SS.
This can work like this. The system administrator of the Local SS defined a queries in the config file or database and name it. Now the user assigned to run the application (query defiend in the config/database) will be able to run these queries from the dial plan.
One good application of such functionality is allow a group of users to share the dailplan data with each other.
What is the command to read database values
Can you read all the tables in the database and is it also possible to write values to different tables in the database this would be a verry nice feature to write for example speedials in the database
Todoy Ypu can use the function DBExecuteScalra in your dial plan
result = sys. DBExecuteScalar(dbType, dbConnStr, query). To be able to use this function, you need to have dbexecutescalar permission in the authoriseapp column of the dialplan. I think authoriseapp column takes a comma separated list of applications.
The value of dbtype and dbconnstr can be taken from the config file.
The issue here is that it gives the user to do whatevre he likes with the database.
I really like the ability to execute some database query through the dialplan. but only queries defined by the system administrator. For example I store few queries in the config/database and name them. Some queries can be public and allowed to all users and others may be private and only allowed to users that has be given access through the authorisedapp field of the dialplan table.
The system supports an function such as ExecuteAuthorisedApp(). As long as the user has access to the app or its an public app, he shall be able to execute these applications.
result = sys. DBExecuteScalar(dbType, dbConnStr, query). To be able to use this function, you need to have dbexecutescalar permission in the authoriseapp column of the dialplan. I think authoriseapp column takes a comma separated list of applications.
The value of dbtype and dbconnstr can be taken from the config file.
The issue here is that it gives the user to do whatevre he likes with the database.
I really like the ability to execute some database query through the dialplan. but only queries defined by the system administrator. For example I store few queries in the config/database and name them. Some queries can be public and allowed to all users and others may be private and only allowed to users that has be given access through the authorisedapp field of the dialplan table.
The system supports an function such as ExecuteAuthorisedApp(). As long as the user has access to the app or its an public app, he shall be able to execute these applications.
I figured out how it works
But I only can read one field in the database selected
I cannot do something like this
res["customer"]
res["number"]
res["prefix"]
res["sipuri"]
I would really like it if I can read all the fields but this seems not to be working
and I also can't count the rows
so this not works
Code: Select all
res = sys.DBExecuteScalar("MySQL", "Database=sipsorcery;Data Source=127.0.0.1;User Id=username;Password=password", "select * from sipadresbook where customer = #{sys.Username}")
sys.Log("#{res}")
I cannot do something like this
res["customer"]
res["number"]
res["prefix"]
res["sipuri"]
I would really like it if I can read all the fields but this seems not to be working
and I also can't count the rows
so this not works
Code: Select all
while row = res.fetch_row do
sys.Log( "row[0], row[1]" )
end
sys.Log( "Number of rows returned: #{res.num_rows}" )
Nope This doesn't work
Maybe I am doing something wrong
Normally with php you can do this
$query = "SELECT * FROM sipadresbook WHERE customer = #{sys.Username}"
$query = mysql_query($query);
$num = mysql_num_rows($query);
$query = mysql_fetch_array($query)
for ($i = $num; $i <= $num; $i++) {
echo "$query[prefix]";
echo "$query[number]";
echo "$query[sipuri]";
}
I was trying the same but then with ruby (But I am no ruby guru)
Code: Select all
res = sys.DBExecuteScalar("MySQL", "Database=sipsorcery;Data Source=127.0.0.1;User Id=username;Password=password", "select number||','||prefix from sipadresbook where customer = #{sys.Username}")
sys.Log("#{res["number"]}")
sys.Log("#{res["prefix"]}")
Normally with php you can do this
$query = "SELECT * FROM sipadresbook WHERE customer = #{sys.Username}"
$query = mysql_query($query);
$num = mysql_num_rows($query);
$query = mysql_fetch_array($query)
for ($i = $num; $i <= $num; $i++) {
echo "$query[prefix]";
echo "$query[number]";
echo "$query[sipuri]";
}
I was trying the same but then with ruby (But I am no ruby guru)
I think the trick here is that the query shall return a string with single column. If you want multiple columns then get an output as a comma separated values or separated with any other delimiter of your choice. In the smaple query we are using comma (Note ||','||).
Once the value is returned, use the split method to split and returned comma separated list. So the code shall be
Once the value is returned, use the split method to split and returned comma separated list. So the code shall be
Code: Select all
res = sys.DBExecuteScalar("MySQL", "Database=sipsorcery;Data Source=127.0.0.1;User Id=username;Password=password", "select number||','||prefix from sipadresbook where customer = #{sys.Username}")
res_fields = res.split(",");
if res_fields.length >=2 then
sys.Log("Number = #{res_fields[0]}")
sys.Log("Prefix = #{res_fields[1]}")
end
MikeTelis and Jack9901 already pointed me in to the right direction to use the split method
So when I use this I don't need the sys.DBExecuteScalar function
the normal DBRead and DBWrite are enough to do this trick
But Thanks for trying it
Code: Select all
#Write
sys.DBWrite(name, [value1, value2, ...].join("|||")) # "|||" or any appropriate separator
#Read
value = sys.DBRead(name).to_s.split("|||")
value1 = value[0]
value2 = value[1]
...
the normal DBRead and DBWrite are enough to do this trick
But Thanks for trying it