Link to home
Create AccountLog in
Avatar of kok_warlock
kok_warlock

asked on

How do I call a stored function in a MS SQL 2005 Server in php?

I might need to run a stored function in a MSSQL 2005 server from a php program for an authentication system, it basically sends a word, say, "abcd" and returns a value, say "FHJL".

I wanted to do an equivalent function in php for the MSSQL function, but it is way beyond my capability.

in sql to run the function(which I did not create), i only do a "SELECT dbo.FUNC_ENCRYPTMSG('abcd', 4)" in a view and returns the value.

How do I get that value in php? Below is the query I tried to use and the function.
<?php
include "sqldesen.inc";
 
 
$query = "SELECT FUNC_ENCRYPTMSG('abcd', 4)";
 
$result = mssql_query($query,$conexaosql) or die("Não foi possível efetuar a conexão");
 
echo "$result";
 
?>
 
------------------------------
 
CREATE FUNCTION dbo.FUNC_ENCRYPTMSG (@SENHA VARCHAR(7), @COD INT)
RETURNS VARCHAR(7)
AS
BEGIN
 
   DECLARE @FRASEFINAL VARCHAR(7)
   DECLARE @I          INT
   DECLARE @ORDEM      INT
 
   SET @FRASEFINAL     = ''
   SET @I              = 1
   SET @ORDEM          = 1
 
   WHILE @I < LEN(RTRIM(@SENHA))+1
   BEGIN
      SET @ORDEM      = ABS(ASCII(SUBSTRING(@SENHA,@I,1)) + @COD + @I)
      SET @FRASEFINAL = @FRASEFINAL + CHAR(@ORDEM % 255)
      SET @I = @I + 1
   END
 
RETURN (UPPER(@FRASEFINAL))
 
END

Open in new window

Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

http://www.sitepoint.com/article/php-microsoft-sql-server

Check out the section on the SqlCommand class
Avatar of kok_warlock
kok_warlock

ASKER

include "sqldesen.inc";
$valor = "abcd";

$query = mssql_init("dbo.FUNC_ENCRYPT",$conexaosql);
mssql_bind($query, "@SENHA", $valor,SQLVARCHAR,false,true,7) or die ("<br><br>aff<br>".mssql_get_last_message());
$result = mssql_execute($query);
unset($query);

print_r($result);

At the moment it is like that, and I'm getting an error on the bind, "error converting varchar to integer(...)"

PS: change the procedure to only declare @SENHA, the @COD is already declared in the new procedure "FUNC_ENCRYPT".
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yeah, I noticed that a couple days ago that I was using the wrong commands for that, you can call a function with a simple query, but when I used a print it didn't produce any result, rather a resource id, only using print_r, that it displayed the entire array.

It's basically like that.

// $connectionsql = MSSQL Connection variable to be defined.
$select = "dbo.FUNC_ENCRYPTMSG('".$_REQUEST['senha']."', 4)";
$sql = mssql_query($campos,$connectionsql);
$array = mssql_fetch_array($result);
// PRINT_R display the whole array, PRINT alone doesn't.
print_r($array);

// That only display the part you want from the array, considering I only get like 1 "true" result, it's [0],[0].
$result = $array[0][0];