php-windows Digest 13 Oct 2006 05:00:27 -0000 Issue 3056
Topics (messages 27231 through 27235):
PDO ODBC & MSSQL Stored Procedures that return record sets
27231 by: Aspen Olmsted
Re: SPAM-LOW: [PHP-WIN] PDO ODBC & MSSQL Stored Procedures that return record
sets
27232 by: Aspen Olmsted
Re: Checking for result of call to mssql_query()
27233 by: Niel Archer
27234 by: Frank M. Kromann
@mssql_connect() versus mssql_connect()
27235 by: Alf Stockton
Administrivia:
To subscribe to the digest, e-mail:
[EMAIL PROTECTED]
To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]
To post to the list, e-mail:
[email protected]
----------------------------------------------------------------------
--- Begin Message ---
Hello,
I am having trouble with pdo_odbc and calling a stored procedure that
returns a record set.
I have tried with out using parameters:
$stmt = $dbh->prepare("EXEC ASC_GridData
@StartRecordIndex=0,@PageSize=15,@SortColumn='',@SortDirection='ASC',@Filter
='lscShowCode=14 and
lscType=2',@PrimaryKey='lscCode',@Fields='lscComponent,lscDescription,lscPos
ition',@TableName='BB_LC_ShowComponent'");
And with using parameters
$a[StartRecordIndex] = 0;
$a[PageSize] = 15;
$a[SortColumn] = '';
$a[SortDirection] = 'ASC';
$a[Filter] = 'lscShowCode=14 and lscType=2';
$a[PrimaryKey] = 'lscCode';
$a[Fields] = 'lscComponent,lscDescription,lscPosition';
$a[TableName] ='BB_LC_ShowComponent';
//$stmt = $dbh->prepare("exec ASC_GridData
:StartRecordIndex,:PageSize,:SortColumn,:SortDirection,:Filter,:PrimaryKey,:
Fields,:TableName");
$stmt->execute($a);
print_r($stmt);
$row = $stmt->fetch();
print_r($row);
Both do not return data thought using sql profiler it looks like it is
running the query just not navigating the result set.
Aspen Olmsted
Alliance Software Corporation
17 Pitt St
Charleston SC, 29401
[EMAIL PROTECTED]
--- End Message ---
--- Begin Message ---
I just realised the problem was the SP was not setting set nocount on so the
first record set was the number of records returned
----------------------------------------
From: "Aspen Olmsted" <[EMAIL PROTECTED]>
Sent: Thursday, October 12, 2006 6:21 AM
To: [email protected]
Subject: SPAM-LOW: [PHP-WIN] PDO ODBC & MSSQL Stored Procedures that return
record sets
Hello,
I am having trouble with pdo_odbc and calling a stored procedure that
returns a record set.
I have tried with out using parameters:
$stmt = $dbh->prepare("EXEC ASC_GridData
@StartRecordIndex=0,@PageSize=15,@SortColumn='',@SortDirection='ASC',@Filter
='lscShowCode=14 and
lscType=2',@PrimaryKey='lscCode',@Fields='lscComponent,lscDescription,lscPos
ition',@TableName='BB_LC_ShowComponent'");
And with using parameters
$a[StartRecordIndex] = 0;
$a[PageSize] = 15;
$a[SortColumn] = '';
$a[SortDirection] = 'ASC';
$a[Filter] = 'lscShowCode=14 and lscType=2';
$a[PrimaryKey] = 'lscCode';
$a[Fields] = 'lscComponent,lscDescription,lscPosition';
$a[TableName] ='BB_LC_ShowComponent';
//$stmt = $dbh->prepare("exec ASC_GridData
:StartRecordIndex,:PageSize,:SortColumn,:SortDirection,:Filter,:PrimaryKey,:
Fields,:TableName");
$stmt->execute($a);
print_r($stmt);
$row = $stmt->fetch();
print_r($row);
Both do not return data thought using sql profiler it looks like it is
running the query just not navigating the result set.
Aspen Olmsted
Alliance Software Corporation
17 Pitt St
Charleston SC, 29401
[EMAIL PROTECTED]
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--- End Message ---
--- Begin Message ---
Hi Alf
You'd be better off asking this type of question in the database list.
I'm not familiar with MS-SQL, the syntax doesn't look much different to
the MySQL flavour.
When you say " whether the query works or not" do you mean the query is
failing with an error, or it simply returns no data? I'd suggest you
test for TRUE first to determine if the query suceeded, but returned no
data. If that fails, test for FALSE.
Niel
--- End Message ---
--- Begin Message ---
Hi Alf,
As far as I can tell it's working just fine. Hre is a sample:
<?php
if (!extension_loaded("mssql")) {
dl("php_mssql.dll");
}
$con = mssql_connect("server", "user", "secret");
if ($con) {
mssql_query("create table test_a (c0 int)");
$rs = mssql_query("insert into test_a (c0) values (1)");
var_dump($rs);
$rs = mssql_query("insert into test_a (c0) values ('aa')");
var_dump($rs);
mssql_query("drop table test_a");
mssql_close($con);
}
?>
and here is the output:
bool(true)
Warning: mssql_query(): message: Syntax error converting the varchar value
'aa' to a column of data type int. (severity
16) in C:\php5\test\mssql_error.php on line 10
Warning: mssql_query(): Query failed in C:\php5\test\mssql_error.php on
line 10
bool(false)
As you can see the first insert is successfull so true is returned. The
second insert has an error so errors are prontet from PHP and false is
returned.
- Frank
> Am I misunderstanding the PHP Manual, where it says:-
> mixed *mssql_query* ( string query [, resource link_identifier [, int
> batch_size]] )
>
> Returns: A MS SQL result resource on success, *TRUE* if no rows were
> returned, or *FALSE* on error.
>
> and my code reads:-
>
> $Reply = mssql_query($insert, $link);
> if ($Reply == FALSE)
> {
> whether the query works or not I have yet to find $Reply equal to
FALSE.
> Please tell me what I am misunderstanding and what is the correct method
>
> --
> Regards,
> Alf Stockton www.stockton.co.za
>
> You will always have good luck in your personal affairs.
> My email disclaimer is available at www.stockton.co.za/disclaimer.html
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--- End Message ---
--- Begin Message ---
What is the difference between @mssql_connect() and mssql_connect()?
i.e. What is the relevance of the prefixed @?
--
Regards,
Alf Stockton www.stockton.co.za
You may my glories and my state dispose,
But not my griefs; still am I king of those.
-- William Shakespeare, "Richard II"
My email disclaimer is available at www.stockton.co.za/disclaimer.html
--- End Message ---
