[Home]

Summary:ASTERISK-07622: [patch] Optionally return values returned by query as dialplan vars
Reporter:Steven Sokol (ssokol)Labels:
Date Opened:2006-08-29 11:30:12Date Closed:2006-09-11 15:05:34
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Functions/func_odbc
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) func_odbc.patch
Description:The current func_odbc returns the values selected in a read operation as a comma separated string.  This is fine for many operations but it strikes me that it's easier and generally more logical to turn the returned column values into variables.  This patch does that.

****** ADDITIONAL INFORMATION ******

The patch adds two new keywords to the func_odbc.conf file's lexicon:

setvars=[yes|no] ; Do we want the function to return fields as vars?  
                ; Defaults to "no"

colprefix=[prefix]  ; An optional prefix to prepend to column names
                   ; I.e. "DBField_"

So, if we have a query that reads:

SELECT dnd, fwd, fwd_number FROM user_features WHERE user_id = 'ssokol01';

And we set "setvars=yes" and "colprefix=DB_" then we wind up with:

${DB_dnd}
${DB_fwd}
${DB_number}

Please let me know what you think.

-S
Comments:By: Tilghman Lesher (tilghman) 2006-08-29 11:44:04

This idea was previously rejected as polluting the namespace.  You can do this
same thing as:

Set(ARRAY(whatever_field1,whatever_field2,whatever_field3)=${ODBC_whatever(args)}
By: Leif Madsen (lmadsen) 2006-08-29 11:51:33

I'm not sure why this is polluting the namespace? Also, with the ARRAY function you can do it... but doesn't it seem like a bit of a run around? Plus you're more prone to having typing errors if you have to type the field name twice.

In addition, what if you have some very long field names and a large amount of them? If you type it all out in ARRAY do you eventually run out of character space?
By: Steven Sokol (ssokol) 2006-08-29 11:54:59

I think the prefix option overcomes the namespace issue (and moreover, isn't it up to the end-user admin to manage their own variable namespace?).  It's also both more convenient and more flexible to allow the column names to be automatically returned as vars.  When Asterisk supports real associative arrays, I'll be more amenable to the array concept.

-S
By: Tilghman Lesher (tilghman) 2006-08-29 12:06:17

If associative arrays are what you want, why not write that, instead of writing around not having them?
By: Tilghman Lesher (tilghman) 2006-08-29 12:40:03

Okay, here's a better way of putting it:

For a new user looking at your dialplan, it has the appearance of new variables just popping up out of nowhere, which is confusing, and may lead someone to conclude that the dialplan is erroneous (especially when the reason they'd be looking at it is because something doesn't work right).

The reason why using ARRAY() is better is that you have obvious location where you are defining variables, as opposed to implicitly defining them.  The other troubling problem is that by depending upon a function to set variables, you're left with calling a function that doesn't return anything which gets used beyond a NoOp.

So, an example dialplan would be something like this:

exten => _XXX,1,NoOp(${ODBC_FOO(args)})
exten => _XXX,n,GotoIf($[${BAR_F1} = 1]?something)

Since there isn't anything which obviously ties ODBC_FOO and the prefix BAR_, this is a confusing dialplan to follow.  Compare this to:

exten => _XXX,1,Set(ARRAY(BAR_F1,BAR_F2,BAR_F3)=${ODBC_FOO(args)})
exten => _XXX,n,GotoIf($[${BAR_F1} = 1]?something)

Here, you have 3 variables, obviously connected to the return value of ODBC_FOO.  This is quite a bit more intuitive and easier to follow.

This is why setting arbitrary variables implicitly isn't a good interface and why we should avoid it as best we can.

Another example:

func_odbc.conf:
[ONE]
dsn=mysql1
read=SELECT * FROM foo WHERE f1='${ARG1}'
colprefix=BAR

[TWO]
dsn=mysql1
read=SELECT * FROM bar WHERE b1='${ARG1}'
colprefix=BAR

extensions.conf:
exten => _XXX,1,NoOp(${ODBC_ONE(${EXTEN})})
exten => _XXX,n,NoOp(${ODBC_TWO(${EXTEN})})
exten => _XXX,n,GotoIf($[${BARsomething} = 1]?somewhere)

You now have a situation where, simply by someone adding a field to the bar table, someone may unintentionally break a dialplan, if the field they add is identically named to a field in the foo table.

Again, this is not intuitive, and it certainly is problematic.  To identically break something using ARRAY(), a user would have to drop a named column from a table (and it would fail catastrophically, rather than silently succeeding).  While this is not entirely out of the question, it is a lot easier to track down than an arbitrary variable name overwritten implicitly.
By: Tilghman Lesher (tilghman) 2006-09-11 15:05:34

Please see the HASH dialplan function, located at http://svncommunity.digium.com/func_odbc/1.2/ in the func_array module.