[Home]

Summary:ASTERISK-10230: func_odbc call doesn't LOAD_FILE()
Reporter:Nick Barnes (bcnit)Labels:
Date Opened:2007-09-04 11:39:51Date Closed:2011-06-07 14:08:07
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Functions/func_odbc
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:I have an ODBC function defined in func_odbc.conf:

[NCTLOADSOUNDS]
dsn=nctreg
write=UPDATE registrations SET spokencall=LOAD_FILE('${ARG1}') WHERE uniqueid='${VAL1}'

"spokencall" is a mediumblob.

Which is called from the following code:

exten => h,n,Set(ODBC_NCTLOADSOUNDS(${RECFILE})=${UNIQUEID})

If I put a trace on the call, I can see that the command is accepted and acts on one row. All well and good, but the file isn't loaded into the blob.

If I change the function to update a different field with the passed argument (i.e. just insert it as text), it works absolutely fine. If I take the SQL and manually insert the fields then run it from the MySQL command line, it works fine. It appears that there's a problem with the MySQL LOAD_FILE() function when called through unixODBC from Asterisk, but I'll be bejiggered if I can work out where.

The file definitely exists (as can be seen by the cut and paste to the MySQL command line) and is not open (as shown by running 'lsof').

I can't work out why this isn't working, but I would like to know if there are any limitations of using func_odbc which may cause these symptoms. Is it just that I can't use MySQL specific functions? If this is the case, why don't I get an error message? Also, I can't seem to see any status variable being returned by func_odbc - is there one?

In the meantime, I've written a bash script to do the update instead which isn't ideal as I'm using odbc for everything else and it's working fine.

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

Asterisk verbose output:
-- Executing [s@nct-register:114] Set("SIP/vitell301-08b72c58", "ODBC_NCTLOADSOUNDS(/opt/nctrec/1188923556.41-recording.WAV)=1188923556.41") in new stack



sql trace:
[ODBC][14096][SQLPrepare.c][192]
               Entry:
                       Statement = 0x8baede0
                       SQL = [UPDATE registrations SET spokencall=LOAD_FILE('/opt/nctrec/1188923556.41-recording.WAV') WHERE uniqueid='1188923556.41'][length = 119 (SQL_NTS)]
[ODBC][14096][SQLPrepare.c][367]
               Exit:[SQL_SUCCESS]
[ODBC][14096][SQLExecute.c][183]
               Entry:
                       Statement = 0x8baede0
[ODBC][14096][SQLExecute.c][344]
               Exit:[SQL_SUCCESS]
[ODBC][14096][SQLRowCount.c][166]
               Entry:
                       Statement = 0x8baede0
                       Row Count = 0x15a4cb8
[ODBC][14096][SQLRowCount.c][237]
               Exit:[SQL_SUCCESS]
                       Row Count = 0x15a4cb8 -> 1
[ODBC][14096][SQLCloseCursor.c][135]
               Entry:
                       Statement = 0x8baede0
[ODBC][14096][SQLCloseCursor.c][227]
               Exit:[SQL_SUCCESS]
[ODBC][14096][SQLFreeHandle.c][372]
               Entry:
                       Handle Type = 3
                       Input Handle = 0x8baede0
[ODBC][14096][SQLFreeHandle.c][471]
               Exit:[SQL_SUCCESS]




Cut and paste the SQL above to the MySQL command line:

mysql> UPDATE registrations SET spokencall=LOAD_FILE('/opt/nctrec/1188923556.41-recording.WAV') WHERE uniqueid='1188923556.41';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

And the field does get updated.
Comments:By: Tilghman Lesher (tilghman) 2007-09-04 11:45:55

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

As it says in the mysql documentation, the file must be located on the SERVER HOST, not on the client host.  Additionally, you must have the FILE privilege and the file must be readable by the mysql user.
By: Nick Barnes (bcnit) 2007-09-04 12:37:51

> the file must be located on the SERVER HOST, not on the client host.

It is. Otherwise the cut and paste into the MySQL client wouldn't have worked either.

> Additionally, you must have the FILE privilege

I do. Otherwise the cut and paste into the MySQL client wouldn't have worked either.

> and the file must be readable by the mysql user.

It is. Otherwise the cut and paste into the MySQL client wouldn't have worked either.
By: Tilghman Lesher (tilghman) 2007-09-04 13:10:02

It still isn't a problem in the func_odbc source or in Asterisk.