Page 1 of 1

Unique Identifiers & Select Queries to MSSQL in PHP

Posted: Wed Oct 11, 2006 4:55 pm
by intergroove
This has been bugging me for a couple of days now:

I am writing a script to regularly transfer data from a MYSQL db to a MSSQL.

Being new to MSSQL I'm a bit freaked out about the UNIQUEIDENTIFER (UI) system.

I have to INSERT data to a table where I need the UI for items from 2 other tables. This negates my ability to use a JOIN. So I need to grab the UI from within PHP and the reference the variable in the query.

To test this I am using a simple SELECT:

First I need to get the UI:
SELECT CAST(CATALOGGUID as VARCHAR(36)) as CGUID FROM CATALOG WHERE CATALOGNAME='TESTCATALOG'

I then assign CGUID to $CGUID

The problem query is now:
SELECT * FROM CATALOG WHERE CATALOGGUID = '".$CGUID."';

I have tried wrapping the string with curly brackets { & }. But it won't work.

I copy the query and put it into SQL QUERY ANALYZER then it works perfectly. The problem is just from inside PHP.

Has anybody else had this problem? or knows of a solution?

The server OS is Windows 2003 Server. PHP is 5+.


Thanx in advance...

Posted: Wed Oct 11, 2006 5:19 pm
by volka
Have you echo'd the query string?
Did you get an error message?

Posted: Wed Oct 11, 2006 5:27 pm
by intergroove
volka wrote:Have you echo'd the query string?
Did you get an error message?
Yes. & when I paste it into SLQ Query Analyser the query works, just not when used from within PHP.

Here is the echo:
SELECT * FROM CATALOG WHERE CATALOGGUID = '212B8201-05BB-4ABC-B6D6-78EA5222C270'

I'm using the mssql_query() function.

$rs=mssql_query($sql)

Posted: Wed Oct 11, 2006 5:31 pm
by volka
Is there any error handling for the mssql operations like $rs=mssql_query($sql) or die('error: '.mssql_get_last_message()); in your script??

Posted: Wed Oct 11, 2006 5:43 pm
by intergroove
volka wrote:Is there any error handling for the mssql operations like $rs=mssql_query($sql) or die('error: '.mssql_get_last_message()); in your script??
OK - wasn't aware of the mssql_get_last_message function.

Just tried it and got:

Changed database context to 'dbname'

This was for the initial query that worked:
$sql="SELECT CAST(CATALOGGUID as VARCHAR(36)) as CGUID FROM CATALOG WHERE CATALOGNAME='TESTNAME' ";

For the problem query there is no error message...

I am connected to two databses, the MySQL and MSSQL dbs. But I am using the respective functions to query. mssql_query and

Posted: Wed Oct 11, 2006 5:48 pm
by volka
that's strange. You used the or die(...) construct and got this message?

btw: is there any chance you can switch to pdo? http://de2.php.net/pdo

Posted: Wed Oct 11, 2006 5:55 pm
by intergroove
Sorry wasn't sure how to use the DIE function
- just gave me a blank screen
- now I check it out it is a script debugging function
- I got my error message using: echo 'error: '.mssql_get_last_message()."<br>";


Just tried the DIE and got: error:

ie nothing.

I am obviously testing the success of a query with:

$row=mssql_fetch_array($rs,MSSQL_BOTH);
if ($row){echo "SUCCESS";} else {echo "FAILED";}

Posted: Wed Oct 11, 2006 6:01 pm
by volka
I'm not sure what you did.
Please post the whole query code in

Code: Select all

tags.

e.g. my current test code is

Code: Select all

<?php
mssql_min_error_severity(0);
mssql_min_message_severity(0);

$mssql = mssql_connect('myconputer\\SQLEXPRESS', null, null) or die('error: '.mssql_get_last_message());
mssql_select_db('master', $mssql) or die('error: '.mssql_get_last_message());

$r = mssql_query('SELECT Count(*) FROM doesnotexist', $mssql) or die('error: '.mssql_get_last_message());
?>

Posted: Wed Oct 11, 2006 6:19 pm
by intergroove
Volka - thanks for your help - as a solo developer I get into these cul-de-sacs frequently.

There was a bug in my test - TYPO!!

I was testing because an INSERT well into the code wasn't working. I assumed that it was entirely down to the uniqueidentifier.

As always the code plays smoke & mirrors with me. The original bug was an accidental copy & paste of MSSQL_BOTH as a second parameter in my mssql_query function.


Thanks again. Problem, what problem!! Can't believe I've spent so long on this...