FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » PDO fetch with SQL server
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
PDO fetch with SQL server [message #186301] Mon, 23 June 2014 11:02 Go to next message
Shelly[1] is currently offline  Shelly[1]
Messages: 16
Registered: March 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
I have a strange case. I have a query in SQL server that has in the
beginning to determine the action:

SELECT
CASE
WHEN x.NEW_HASH = x.PREVIOUS_HASH THEN 'SAME'
WHEN x.NEW_HASH != x.PREVIOUS_HASH AND
x.PREVIOUS_HASH IS NOT NULL AND x.[KEY] IS NOT NULL THEN 'UPDATE'
WHEN x.PREVIOUS_HASH IS NULL AND
x.count_of_tracking_records > 0 THEN 'UPDATE'
WHEN x.PREVIOUS_HASH IS NULL THEN 'INSERT'
WHEN x.[KEY] IS NULL THEN 'DELETE'
ELSE 'UNKNOWN'
END AS ACTION,
x.*

so that it will choose the action. There is a FULL OUTER JOIN at the
end to the destination table. Another, later, query, will do a merge
and perform the action specified in the results of this query.

The PHP function that this uses is a PDO statement with the fetch method
and the PDO::FETCH_ASSOC as the parameter.

When this query is run against an empty destination table, 1991 records
are obtained which are subsequently inserted into the destination table
by the second query.

When this same query is then run against this filled table, with the
same records, the PDO fetch retrieves zero records. I have put in log
files into the PHP code and it is clear that the fetch returns zero
records.

However, when this query is cut and pasted into Sqlserver Management
Studio and run, it produces 3982 records. The first 1981 are with
action DELETE and the rest are with action INSERT.

One additional point that might, or might not, have a bearing on this.
Some variables have bindings. For example a :grouping_1 has a string
value that is the same for the entire query. When I run it in Sqlserver
Management Studio I replace those bind variable with their values.

Crossposted to both the php and sqlserver news groups.

--
Shelly
Re: PDO fetch with SQL server [message #186303 is a reply to message #186301] Mon, 23 June 2014 12:10 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 6/23/2014 11:02 AM, Shelly wrote:
> I have a strange case. I have a query in SQL server that has in the
> beginning to determine the action:
>
> SELECT
> CASE
> WHEN x.NEW_HASH = x.PREVIOUS_HASH THEN 'SAME'
> WHEN x.NEW_HASH != x.PREVIOUS_HASH AND
> x.PREVIOUS_HASH IS NOT NULL AND x.[KEY] IS NOT NULL THEN 'UPDATE'
> WHEN x.PREVIOUS_HASH IS NULL AND
> x.count_of_tracking_records > 0 THEN 'UPDATE'
> WHEN x.PREVIOUS_HASH IS NULL THEN 'INSERT'
> WHEN x.[KEY] IS NULL THEN 'DELETE'
> ELSE 'UNKNOWN'
> END AS ACTION,
> x.*
>
> so that it will choose the action. There is a FULL OUTER JOIN at the
> end to the destination table. Another, later, query, will do a merge
> and perform the action specified in the results of this query.
>
> The PHP function that this uses is a PDO statement with the fetch method
> and the PDO::FETCH_ASSOC as the parameter.
>
> When this query is run against an empty destination table, 1991 records
> are obtained which are subsequently inserted into the destination table
> by the second query.
>
> When this same query is then run against this filled table, with the
> same records, the PDO fetch retrieves zero records. I have put in log
> files into the PHP code and it is clear that the fetch returns zero
> records.
>
> However, when this query is cut and pasted into Sqlserver Management
> Studio and run, it produces 3982 records. The first 1981 are with
> action DELETE and the rest are with action INSERT.
>
> One additional point that might, or might not, have a bearing on this.
> Some variables have bindings. For example a :grouping_1 has a string
> value that is the same for the entire query. When I run it in Sqlserver
> Management Studio I replace those bind variable with their values.
>
> Crossposted to both the php and sqlserver news groups.
>

This is not a PHP question.

Just because you're using PDO does not mean PHP is involved.



--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex(at)attglobal(dot)net
==================
Re: PDO fetch with SQL server [message #186314 is a reply to message #186301] Tue, 24 June 2014 07:19 Go to previous messageGo to next message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Shelly wrote:
^^^^^^
Please use your real name here.

> Crossposted to both the php and sqlserver news groups.

Pointless until you post *PHP* code, too.

Furthermore, comp.database.ms-sqlserver does not exist.
It is <news:comp.databases.ms-sqlserver>.

Unfortunately, Thunderbird?s Network News capabilities are still inferior
even in version 24. This mistake would not have happened with, for example,
KNode, where you can mark the checkbox from a list of newsgroups that you
want to post to.

However, a properly maintained news*server* would have issued an error
message as well, and a decent newsreader would have displayed it.


PointedEars
--
var bugRiddenCrashPronePieceOfJunk = (
navigator.userAgent.indexOf('MSIE 5') != -1
&& navigator.userAgent.indexOf('Mac') != -1
) // Plone, register_function.js:16
Re: PDO fetch with SQL server [message #186316 is a reply to message #186314] Tue, 24 June 2014 08:33 Go to previous messageGo to next message
Richard Damon is currently offline  Richard Damon
Messages: 58
Registered: August 2011
Karma: 0
Member
add to buddy list
ignore all messages by this user
On 6/24/14, 7:19 AM, Thomas 'PointedEars' Lahn wrote:
>
> Furthermore, comp.database.ms-sqlserver does not exist.
> It is <news:comp.databases.ms-sqlserver>.
>
> Unfortunately, Thunderbird?s Network News capabilities are still inferior
> even in version 24. This mistake would not have happened with, for example,
> KNode, where you can mark the checkbox from a list of newsgroups that you
> want to post to.
>
> However, a properly maintained news*server* would have issued an error
> message as well, and a decent newsreader would have displayed it.
>
>
> PointedEars
>

News servers should NOT give an error on an unrecognized newsgroup name,
as no news server knows it has the canonical listing of newsgroups, only
the list of newsgroup it maintains. It is quite allowed for a couple of
peering sites (or even a single site) to create a newsgroup on their
own. Even in comp, which is a managed hierarchy, it isn't "illegal" to
create a "local" newsgroup in the hierarchy, just strongly discouraged.

Now, if a server gets a message submitted where NONE of the newsgroups
exist (in its opinion), it could be justified in rejecting the message.
Re: PDO fetch with SQL server [message #186317 is a reply to message #186316] Tue, 24 June 2014 09:38 Go to previous message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Richard Damon wrote:

> On 6/24/14, 7:19 AM, Thomas 'PointedEars' Lahn wrote:
>> However, a properly maintained news*server* would have issued an error
>> message as well, and a decent newsreader would have displayed it.
>
> News servers should NOT give an error on an unrecognized newsgroup name,
> as no news server knows it has the canonical listing of newsgroups, […]

Nonsense.

<http://tokak.us/big8/articles/c/h/e/Checkgroups.html>


PointedEars
--
Prototype.js was written by people who don't know javascript for people
who don't know javascript. People who don't know javascript are not
the best source of advice on designing systems that use javascript.
-- Richard Cornford, cljs, <f806at$ail$1$8300dec7(at)news(dot)demon(dot)co(dot)uk>
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: is possible with own mail server have same results of mailchimp?
Next Topic: str_replace does not like empty quotes
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Oct 24 03:57:07 EDT 2017

Total time taken to generate the page: 0.00693 seconds