phpsql-server-2012odbcuser-defined-functionsisql

execute MSSQL user-defined function in PHP


I need to execute a user-defined function from within PHP on a remote host (via odbc).

The function itself is a simple calculation and has been created in MS SQL Server Studio like so:

CREATE FUNCTION dbo.__WeightCalculation(@_Length float, @_Width float, @_Height float, @_ShapeType nvarchar(255)) 
RETURNS float AS 

BEGIN
    return (select 
        (CASE
            WHEN @_ShapeType  = 'A'
            THEN (((@_Width * @_Height * 0.5) / 100) * @_Length) / 1000 
            WHEN @_ShapeType IN ('B', 'C', 'D', 'E') 
            THEN (((@_Width * @_Height * 0.75) / 100) * @_Length) / 1000 
            ELSE 'nA'
        END) as THIS_WEIGHT)
END;

I have been able to verify that this function is working and returning the expected result by executing

select dbo.__WeightCalculation(10, 20, 30, 'A');


----------------------
0,03

(1 Zeile(n) betroffen)

in the Management Studio.

However, I am unable to execute this function in any other way than this.

When trying to run that function via CLI on the webserver i.e.

user@server:~/tmp# isql dsn usr pwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select dbo.__WeightCalculation(10, 20, 30, 'A');
[ISQL]ERROR: Could not SQLExecute
SQL>

it will prompt [ISQL]ERROR: Could not SQLExecute. When trying to run that function from a PHP script i.e.

$connect = odbc_connect($dsn, $user, $pw);
$query = "SELECT dbo.__WeightCalculation(10, 20, 30, 'A')";
$result = odbc_exec($connect, $query);
$row = odbc_fetch_array($result);
echo '<pre>';
print_r($row);
echo '</pre>';

... the following can be found in the servers error log:

mod_fcgid: stderr: PHP Warning: odbc_exec(): SQL error: [FreeTDS][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.__WeightCalculation", or the name is ambiguous., SQL state 37000 in SQLExecDirect in /var/www/web/test/odbc.php on line 14

Apart from that I've tried to execute the function with DECLARE EXEC SELECT which as well is working as expected in SQL Studio but not via CLI or PHP.

Do I need to bind the parameters and execute the function separately? Any hints about how I can execute a MSSQL user-defined function via PHP (since CLI is just for testing/verifying) is greatly appreciated.

Please note:

  1. The function call is obviously just a part of a much bigger query
  2. The actual scenario/use-case is a product import (~20k products) for Magento CE 1.9.x using MAGMI's ability to odbc connect on a remote server. According to MAGMI's wiki this however requires the whole statement to be placed within a single .sql-file (Since this did not work so far i started digging deeper and came to above mentioned fundamental issue hence I decided to post my question here rather than at magento.stackexchange.com)
  3. The user who created the function, who is running the query in SQL Studio and who is connected via ODBC is the same, namely sa

Solution

  • Well, at one point it hits you, right? ;)

    My function, since not specified otherwise, was created within the database master. In fact I can not tell you whether this is always the case (at least when you're logged in as sa) - might be interesting if someone could answer that one.

    However, the initial thought of creating the function for a particular table (I think, @DanGuzman had that in mind as well at some point?) was right, just the way I have done it, was wrong, i.e. --

    CREATE FUNCTION [databasename].dbo.functionname(@params)
      ...
    GO
    

    -- does NOT work, while --

    USE [databasename];
    GO
    CREATE FUNCTION dbo.functionname(@params)
      ...
    GO
    

    -- does.