Unique Identifiers & Select Queries to MSSQL in PHP
Moderator: General Moderators
-
intergroove
- Forum Commoner
- Posts: 32
- Joined: Wed Oct 11, 2006 4:42 pm
Unique Identifiers & Select Queries to MSSQL in PHP
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...
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...
-
intergroove
- Forum Commoner
- Posts: 32
- Joined: Wed Oct 11, 2006 4:42 pm
Yes. & when I paste it into SLQ Query Analyser the query works, just not when used from within PHP.volka wrote:Have you echo'd the query string?
Did you get an error message?
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)
-
intergroove
- Forum Commoner
- Posts: 32
- Joined: Wed Oct 11, 2006 4:42 pm
OK - wasn't aware of the mssql_get_last_message function.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??
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
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
btw: is there any chance you can switch to pdo? http://de2.php.net/pdo
-
intergroove
- Forum Commoner
- Posts: 32
- Joined: Wed Oct 11, 2006 4:42 pm
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";}
- 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";}
I'm not sure what you did.
Please post the whole query code in
Please post the whole query code in
Code: Select all
tags.
e.g. my current test code isCode: 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());
?>-
intergroove
- Forum Commoner
- Posts: 32
- Joined: Wed Oct 11, 2006 4:42 pm
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...
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...