?
Solved

MS-SQL Stored Procedure executed by PHP over ODBC, returning field->value pairs

Posted on 2012-09-04
7
Medium Priority
?
1,508 Views
Last Modified: 2012-09-10
Hi there

I'm trying to run a PHP script that calls a MS SQL 2005 stored procedure that has some cursors and effectively returns a single row, as if it was a normal query.

$conn = odbc_connect($DSN, $username, $pw) or die();
if ($conn) {
	 $rs = odbc_exec($conn, $query);
            if($rs) {
                 while ($data= odbc_fetch_array($rs)) {
				print_r($data);
                 }

	} else {
		echo "no result set";
	}
} else {
	echo 'couldn’t connect';
}

Open in new window


This generates a php warning;
odbc_fetch_array(); [Microsoft][ODBC SQL Server Driver] Invalid descriptor index, SQL state S1002 in SQLGetData

When using odbc_result_all() instead of odbc_fetch_array(), the results are printed as html. However, we need to get the result set as an associative array (colname=>value, colname=>value... etc) so to avoid a double-maintainance issue should new return values be  added to the stored procedure at a later stage.

What should we do?

Many thanks!

bh
0
Comment
Question by:butterhook
  • 5
  • 2
7 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 38364393
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38364400
here how to work with mssql:
http://www.php.net/manual/en/book.mssql.php
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38364405
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 21

Expert Comment

by:oleggold
ID: 38364408
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38364416
Example #1 mssql_fetch_assoc() example

 
<?php
// Send a select query to MSSQL
$query = mssql_query('SELECT [username], [name] FROM [php].[dbo].[userlist]');

// Check if there were any records
if (!mssql_num_rows($query)) {
    echo 'No records found';
}
else
{
    // Print a nice list of users in the format of:
    // * name (username)

    echo '<ul>';

    while ($row = mssql_fetch_assoc($query)) {
        echo '<li>' . $row['name'] . ' (' . $row['username'] . ')</li>';
    }

    echo '</ul>';
}

// Free the query result
mssql_free_result($query);
?> 

Open in new window

0
 
LVL 1

Accepted Solution

by:
butterhook earned 0 total points
ID: 38367110
Thanks, we ended up using the pdo_sqlsrv extension which is installed by default with our build of php

For reference, this would be something along the lines of:

$results = array();

$myParameter = 123456;

$dbHandle = new PDO("sqlsrv:Server=XXX;Database=XXX", "username", "password");

/* 
	Display error info, if required for testing purposes
*/
$dbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		
$stmt = $dbHandle->prepare("EXEC spMyStoredProc :1;");
		
$stmt->bindParam(':1', $myParameter, PDO::PARAM_INT);
				
$stmt->execute();
				
while($dataset = $stmt->fetch(PDO::FETCH_ASSOC))
{	
	foreach($dataset as $key=>$value) {
		$results[$key] = $value;
	}
}

$dbHandle = Null;

print_r($results);

Open in new window

0
 
LVL 1

Author Closing Comment

by:butterhook
ID: 38382274
solves problem without installation of additional drivers.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question