Summary:	ASTERISK-07722: [post 1.4] Return multiple records (rows) when executing ODBC queries
Reporter:	Steven Sokol (ssokol)	Labels:
Date Opened:	2006-09-11 23:06:21	Date Closed:	2011-06-07 14:03:20
Priority:	Major	Regression?	No
Status:	Closed/Complete	Components:	Functions/General
Versions:		Frequency of Occurrence
Related Issues:
Environment:		Attachments:	( 0) extensions.conf ( 1) func_odbc_multi.c ( 2) func_odbc_multi.conf
Description:	It is often useful to return more than one record when executing a database query (really!) so I've created a new version of func_odbc which can do it. The function builds on the existing func_odbc.  It follows the same essential conventions in terms of the configuration: your still create queries in the configuration file (func_odbc_multi.conf).  Just like the original, the query name (i.e. the name defined in the [section] header) becomes the name of the function in the dialplan. See the example func_odbc_multi.conf file. The big difference here is that when the query executes, it packs the returned data into a single long string in which the fields are separated using \03 and the records are separated using \04.  (Come on, when's the last time you used an End Of Data character.) Yes, this is probably an issue if you're using data encoded in anything other than good old ASCII, but hey, I'm lousy C programmer who knows nothing of UTF-8 and other such fancy stuff. See the example snippet of extensions.conf to see a query being executed. When you want to use the data, you pull it out row by row.  Row 0 is always the field names, actual data rows always start at 1.  You can walk through it (forwards or backwards) using the numeric row identifier (index) to RECORD_FETCH the row you want.  By default the RECORD_FETCH function returns the record in standard comma-separated (ARRAY) format.  However, for giggles you can also add an optional parameter (ASSOC) which tells the RECORD_FETCH function to return a "pseudo-associative" array. The "pseudo-associative array" is nothing more than a two-row mini recordset with the first row (row 0) containing the field names and the second row containing the actual data row requested.  This allows the RECORD function to return fields by name.  This allows you to query using the wildcard (*) and return the data you want by name (without having to remember the field ordinal position. If anyone who is a better c programmer wants to spiff this up, please do! Thanks, Steve ****** ADDITIONAL INFORMATION ****** The function file actually introduces several new functions: RECORD_FETCH - Fetch a record from the recordset variable. RECORD_COUNT - Return a count of the records in the recordset. RECORD - Do stuff to a record (generally get fields from it) FIELD_COUNT - Return the number of fields in a record.
Comments:	By: Jason Parker (jparker) 2006-09-18 21:58:13 I think this would be better handled inside of func_odbc, with a multirow=yes option in the function. By: Tilghman Lesher (tilghman) 2006-09-19 01:45:13 Please note that: 1. You are not to use strtok(3), as it is not thread-safe.  It is generally better to use strsep(3) or even AST_NONSTANDARD_APP_ARGS(). 2. You are also not to declare variables anywhere but at the beginning of a code block. 3. You should be using the argument functions to lay out all of your arguments, instead of creating a 100-argument array and using only 2 spaces.  The reason func_odbc uses that syntax is because the number of arguments is indeterminate at compile time.  Most of your functions, however, have a determinate number of arguments and need explicit specification of each fieldname. 4. You should not use strcat(3) unless you do specific bounds catching before calling it.  Use strncat(3) for the general case. 5. You should not use sprintf(3) unless you do specific bounds catching before calling it.  Use snprintf(3) for the general case. By: jmls (jmls) 2006-10-31 11:36:50.000-0600 ssokol, could you comment on the two notes above ? Thanks. By: Tilghman Lesher (tilghman) 2006-11-30 02:01:11.000-0600 No response from reporter -- please feel free to reopen when you have an updated patch.