Bug #13931 Updated: unexpected SQL error using SQL_CUR_USE_ODBC in odbc_pconnect()
ID: 13931
User updated by: g.sollis@syntegra.nl
Reported By: g.sollis@syntegra.nl
Status: Open
Bug Type: ODBC related
Operating System: Windows NT4 SP6a
PHP Version: 4.0.6
New Comment:
oops...missed a ')' in my code here at the end of the 'do' loop. I have got this
right in my original code ;)
Previous Comments:
------------------------------------------------------------------------
[2001-11-05 06:57:41] g.sollis@syntegra.nl
I am using the pre-compiled version of PHP 4.06 on Win32. The web server is IIS-4 on WinNT with
Option Pack and the database is MS SQL-Server 6.5. Connection is made via ODBC using a system DSN. I
am using the latest MDAC(2.6) and JET(4) drivers on this DSN.
What i want to do is the following:
* the user creates a query (by form) that results in 1 or more projects being retrieved.
* if more than 1 projects are found i want to display a shortlist of projects. The user should be
able to sort the result on all displayed columns.
When a project is selected, the form submits and retrieves the details for that project. The project
is retrieved using the primary key in the WHERE clause and there is no ORDER BY clause:
$qry="SELECT projects.proj_num, projects.status, clients.name FROM
projects INNER JOIN clients ON projects.clientID=clients.clientID
WHERE projects.proj_num=12345";
* if one project is found, the form should display the project details.
projects.proj_num and clients.clientID are the table's primary key.
The code I am running is basically this:
$qry="SELECT projects.proj_num, projects.status, clients.name FROM
projects INNER JOIN clients ON projects.clientID=clients.clientID
WHERE projects.proj_status='Open'";
$conID=ODBC_PCONNECT(DSN,USER,PASSWORD);
$res =ODBC_DO($conID,$qry);
$rowCnt=0;
while(odbc_fetch_row($res)) {
$rowCnt=$rowCnt+1;
}
odbc_fetch_row($res,1)
if($rowCnt==1) {
//display the detailed info function here
//because we only have found one project.
} else {
do {
//this section displays a list of found projects
//display some fields using odbc_result() here.
} while(odbc_fetch_row($res);
}
situation 1:
------------
Using the default cursor by specifying
ODBC_PCONNECT(DSN,USER,PASSWORD) and then performing the query by i get a result set which i can
display.
Then a user selects a column to sort and the query gets the following addition (example):
" ORDER BY clients.name ASC"
The form is submitted using the POST method. Trying to sort on a different column than the primary
key returns only the last row, if more than one project is found. The query has only changed in that
an ORDER BY clause has been added.
I understand this behaviour is due to the driver not supporting row numbers in ODBC_RESULT(), even
though it does work from MS-Access or the MS-Query.
In short, i can get one or more projects, but cannot sort on any column except the primary key.
situation 2:
------------
In this situation i have changed the connection by specifying it to use an ODBC cursor:
$conID=ODBC_PCONNECT(DSN,USER,PASSWORD,SQL_CUR_USE_ODBC);
Now i can sort through the list to my heart's content, but when a project is selected it does
not work.
I get the following error from SQL in this situation:
"AMBIGUOUS COLUMN NAME clientID".
I don't understand how this can happen as i have specified which fields it should retrieve from
each table. Also, i do not get this error using any other software.
I have made a workaround by querying twice using the default CURSOR-type from situation 1 and
storing the results in seperate variables. I don't really want to do this due to wasted
resources, so please get it fixed, or re-educate me ;)
------------------------------------------------------------------------
Edit this bug report at http://bugs.php.net/?id=13931&edit=1
Thread (3 messages)