[asterisk-bugs] [Asterisk 0010640]: func_odbc call doesn't LOAD_FILE()

[noreply at bugs.digium.com]

The following issue has been REOPENED. 
====================================================================== 
http://bugs.digium.com/view.php?id=10640 
====================================================================== 
Reported By:                bcnit
Assigned To:                Corydon76
====================================================================== 
Project:                    Asterisk
Issue ID:                   10640
Category:                   Functions/func_odbc
Reproducibility:            always
Severity:                   minor
Priority:                   normal
Status:                     feedback
Asterisk Version:           1.4.11  
SVN Branch (only for SVN checkouts, not tarball releases): N/A  
SVN Revision (number only!):  
Disclaimer on File?:        N/A 
Request Review:              
====================================================================== 
Date Submitted:             09-04-2007 11:39 CDT
Last Modified:              09-04-2007 12:37 CDT
====================================================================== 
Summary:                    func_odbc call doesn't LOAD_FILE()
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.
====================================================================== 

---------------------------------------------------------------------- 
 bcnit - 09-04-07 12:37  
---------------------------------------------------------------------- 
> 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. 

Issue History 
Date Modified   Username       Field                    Change               
====================================================================== 
09-04-07 12:37  bcnit          Status                   closed => feedback  
09-04-07 12:37  bcnit          Resolution               no change required =>
reopened
09-04-07 12:37  bcnit          Note Added: 0069911                          
======================================================================