PHPBuilder - MSSQL SERVER stored procedure from php



RSS Twitter
Snippets Databases

MSSQL SERVER stored procedure from php

by: Cesar Correa
|
July 16, 2004

Version: 4.3.7

Type: Sample Code (HOWTO)

Category: Databases

License: Other

Description: hi, just a want to contribute with a little code for to call stored procedure from ms sql sever in php, using parameters and exporting the result query to Excel file. in spanish: hola solo queria contribuir con un poco de codigo que llama a un procedimiento almacenado de sql server desde php y exportando el resultado de la consulta a un archivo en Ms Excel. espero sea de mucha ayuda.



este es mi p.a en mi base de
datos.

CREATE PROCEDURE Ver_cliente
@Id nvarchar(30) output --   IMPORTANTE!!!
AS
select 
[clientes].cCedula    as Cedula,
[clientes].cNombres as Nombres,
[clientes].cApellidos as Apellidos,
[clientes].cDireccion as Direccion,
[clientes].desResidencia as "Ciudad Residencia",
--[clientes].cCiudadResidencia  as  "Codigo Ciudad",
[clientes].cTelefono as Telefono,
[clientes].cPase  as Pase,
[clientes].siCategoria  as Categoria,
[clientes].sdVencimiento as Vencimiento,
[clientes].cMovil   as Movil,
[clientes].promedioCalificacion   as Calificacion,
oficinas.cCiudad as Oficina,
[clientes].iCodigoFotografico  as CodigoFotografico,
[clientes].bActivo  as Activo,
--bAprobado  as Aprobado,
[clientes].bRevisado  as Revisado,
[clientes].deuda  as Debe_$,
[clientes].CreadoPor  as CreadoPor,
(usuarios.cNombres + ' ' + usuarios.cApellidos) as  
"NombreCreadoPor"
from [clientes] inner join
oficinas on [clientes].ioficina = oficinas.iCodigo 
inner join usuarios on usuarios.ccedula = clientes.CreadoPor
where [clientes].cCedula = @Id
GO

**********************************************
this my code in php , taked from others examples  ;-)

<?php
$file_type = "vnd.ms-excel";
$file_ending = "xls";
//}
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment;
filename=file_name.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
//get contents
//puede ser la fecha actual
$now_date = date('d-m-Y H:i');
$title = "Listado Personas  $now_date";
$sql = "Select * from clientes";
$valor = 8400038; //id from client
/*
Database Connection (Alternative- for mysql_fetch_array)    */
$conex =
mssql_connect("yourserver","sa","123");
mssql_select_db("yourdatabase"); 
$proc = mssql_init("Ver_Cliente", $conex) or die("Hay
problemas para conectar a Stored Procedure");
mssql_bind($proc, "@Id", &$valor, SQLVARCHAR, true) or
die("Hay problemas para crear bind");/*parametro &$valor
pasado por referencia.	true = is_output, like the declaration in the
procedure code above */
$result=mssql_execute($proc) or die ("Error sacando el
procedimiento almacenado"); 
//end of connection code

//define separator (defines columns in excel)
$sep = "\t";

//print excel header with timestamp:
echo("$title\n");

//start of printing column names
for ($i = 0; $i < mssql_num_fields($result); $i++) {
echo mssql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names

//start while loop to get data
/*      
note: the following while-loop was taken from phpMyAdmin 2.1.0.
--from the file "lib.inc.php".
*/
    $i = 0;
    while($row = mssql_fetch_row($result))
    {
        //set_time_limit(60); // HaRa
        $schema_insert = "";
        for($j=0; $j<mssql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
	$schema_insert = str_replace($sep."$",
"", $schema_insert);
		$schema_insert .= "\t";
        print(trim($schema_insert));
		print "\n";
        $i++;
    }
    return (true);
?>
it's all  folks, 
*****************************
Cesar Correa from Bello, Medellin, Colombia

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Cesar Correa

Comment:



Comment:

(Maximum characters: 1200). You have characters left.