Running customised database query in Local SS

New features you'd like see on SIP Sorcery
Post Reply
jainpj
Posts: 173
Joined: Tue Dec 30, 2008 10:13 am

Running customised database query in Local SS

Post by jainpj » Wed Sep 02, 2009 8:05 am

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.

jvwelzen
Posts: 716
Joined: Thu Sep 11, 2008 1:56 pm

Re: Running customised database query in Local SS

Post by jvwelzen » Wed Sep 09, 2009 4:40 pm

jainpj 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.
Hi can you explain how this works

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

jainpj
Posts: 173
Joined: Tue Dec 30, 2008 10:13 am

Post by jainpj » Wed Sep 09, 2009 8:41 pm

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.

jvwelzen
Posts: 716
Joined: Thu Sep 11, 2008 1:56 pm

Post by jvwelzen » Wed Sep 09, 2009 8:56 pm

I figured out how it 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}")
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

   while row = res.fetch_row do
     sys.Log( "row[0], row[1]" )
   end
   sys.Log( "Number of rows returned: #{res.num_rows}" )


jainpj
Posts: 173
Joined: Tue Dec 30, 2008 10:13 am

Post by jainpj » Thu Sep 10, 2009 10:18 am

I think you need to write SQL queries or stored procedures that return a string. I guess this query shall work
select column1||','||column2 from sipadresbook where customer = #{sys.Username}

This query shall return a single column (field)

jvwelzen
Posts: 716
Joined: Thu Sep 11, 2008 1:56 pm

Post by jvwelzen » Thu Sep 10, 2009 1:22 pm

Nope This doesn't work

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"]}")
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)

jainpj
Posts: 173
Joined: Tue Dec 30, 2008 10:13 am

Post by jainpj » Fri Sep 11, 2009 9:28 am

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

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

jvwelzen
Posts: 716
Joined: Thu Sep 11, 2008 1:56 pm

Post by jvwelzen » Fri Sep 11, 2009 10:07 am

MikeTelis and Jack9901 already pointed me in to the right direction to use the split method

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] 
... 
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

Post Reply