Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <pre>
- <?PHP
- $sqlserver = '';
- $username = '';
- $password = '';
- $database = "";
- /*
- * writer.php version 2.0 - Major update
- * Takes stored procedure name, the input variables and an array of output variables
- * outputs php code necessary for getting results using php pdo
- *
- */
- //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\\
- $procedureName = 'stored_procedure_name';
- //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\\
- $connectionInfo = array( "Database"=>$database, "UID"=>$username, "PWD"=>$password);
- $conn = sqlsrv_connect( $sqlserver, $connectionInfo);
- if( $conn ) {
- //echo "Connection established.<br />";
- }else{
- echo "Connection could not be established.<br />";
- die( print_r( sqlsrv_errors(), true));
- }
- //construct the query.
- $query = "SELECT name, type_name(user_type_id) AS type, max_length, is_output FROM sys.parameters WHERE object_id = object_id('DBO.".$procedureName."')";
- $stmt = sqlsrv_query( $conn, $query);
- if( $stmt === false ) {
- die( print_r( sqlsrv_errors(), true));
- }
- // Make the first (and in this case, only) row of the result set available for reading.
- $inputs = [];
- $outputs = [];
- while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
- if($row['is_output']==1){
- if($row['type']=='varchar'){
- $type = 'varchar('.$row['max_length'].')';
- } else {
- $type = $row['type'];
- }
- $outputs[$row['name']] = $type;
- } else {
- $inputs[] = $row['name'];
- }
- }
- $i = 1;
- $array = $inputs;
- $input = implode(', ', $inputs);
- //nospace on the first line because it makes it easier to copy and paste ;)
- print 'if(!isset($pdo) || $pdo==null){'."\r\n";
- print ' $pdo = DB::connection()->getPdo();'."\r\n";
- print ' }'."\r\n";
- print ' //SET NOCOUNT ON; --you might need this... '."\r\n";
- print ' $stmt = $pdo->prepare("DECLARE @return int';
- if(count($outputs)>0){
- foreach($outputs as $var=>$type){
- print ', '.$var.' '.$type;
- }
- }
- print '; EXEC @return = ['.$procedureName.'] ';
- print implode(' = ?, ', $array);
- $comma = '';
- if(count($array)>0 && strlen($input)>0){
- print ' = ?';
- $comma = ', ';
- }
- if(count($outputs)>0){
- foreach($outputs as $var=>$type){
- print $comma.$var.' = '.$var.' OUTPUT';
- $comma = ', ';
- }
- }
- print '; SELECT @return AS N\'@returnVal\'';
- if(count($outputs)>0){
- foreach($outputs as $var=>$type){
- print ', '.$var.' AS N\''.$var.'\'';
- }
- }
- print ';");'."\r\n";
- if(count($array)>0 && strlen($input)>0){
- foreach($array as $item){
- print' $stmt->bindValue('.$i.', $'.str_replace('@p_', '', str_replace('@', '', $item)).' ); //'.str_replace('_', ' ', str_replace('@p_', '', $item))."\r\n";
- $i++;
- }
- }
- print ' $stmt->execute();'."\r\n";
- //print ' //TODO: Format response'."\r\n";
- print ' //Should have the resultset...'."\r\n";
- print ' $rows = $stmt->fetchAll( \PDO::FETCH_ASSOC );'."\r\n";
- print ' $stmt->nextRowSet();'."\r\n";
- print ' //Should have the select response, or if there are multiple resultsets, it\'ll be here.'."\r\n";
- print ' $return = $stmt->fetchAll( \PDO::FETCH_ASSOC );';
- /*
- for returned variables:
- DECLARE @var type; SET @var = null; STATEMENT; SELECT @var AS N'var';
- ^-- And set the output var like this: @p_Service_Request_ID = @var OUTPUT
- */
Add Comment
Please, Sign In to add comment