Bug #13931: unexpected SQL error using SQL_CUR_USE_ODBC in odbc_pconnect()

From: g.sollis@syntegra.nl Operating system: Windows NT4 SP6a PHP version: 4.0.6 PHP Bug Type: ODBC related Bug description: unexpected SQL error using SQL_CUR_USE_ODBC in odbc_pconnect() 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 bug report at: http://bugs.php.net/?id=13931&edit=1