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:
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.