odbc_exec Not returning Resource ID

[Anonymous]

Hi

I changed my connection in my application to odbc mssql
-> using adodb.org libraries
-> It connects fine
-> The update query below works
-> But when I pass the select query below SQL odbc_exec does not return a resource id for my query
-> None of my select queries returns a resource id

Below is the details that I logged.
-> Any idea how to fix
-> If I paste the sql in MS Sql management studio to executes fine.

Please assist in what could be the cause?

Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql = SET CONCAT_NULL_YIELDS_NULL OFF
odbc_exec = Resource id #89
Time->2020-07-30 19:09:14adodb-odbc.inc.php Connection Arguments => DSN=amphibian;UID=amphibian;PWD=speedster;DATABASE=crmapm;,,
Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql = Update pwd_incorrect set record_status_id = 2 where date <> '2020-07-30'
odbc_exec = Resource id #92
Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql = SET CONCAT_NULL_YIELDS_NULL OFF
odbc_exec = Resource id #95
Time->2020-07-30 19:09:14adodb-odbc.inc.php Connection Arguments => DSN=amphibian;UID=amphibian;PWD=speedster;DATABASE=crmapm;,,
Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql = SET CONCAT_NULL_YIELDS_NULL OFF
odbc_exec = Resource id #98
Time->2020-07-30 19:09:14adodb-odbc.inc.php Connection Arguments => DSN=amphibian;UID=amphibian;PWD=speedster;DATABASE=crmapm;,,
Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql = select c.name,t.name,c.length,c.isnullable, c.status,
(case when c.xusertype=61 then 0 else c.xprec end),
(case when c.xusertype=61 then 0 else c.xscale end)
from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='pwd_user_deny'
odbc_exec = Resource id #101
Time->2020-07-30 19:09:14 adodb-odbc.inc.php sql =
SELECT user_name_used,password_used,ip_address,date,ref_no,record_status_id FROM crmapm.dbo.pwd_user_deny WHERE record_status_id = 1 and ip_address = '::1'
odbc_exec =

 

[ahmedarain34]

Based on the information you provided, it seems that the issue lies with the execution of the SELECT queries using the ODBC driver and the ADODB library. There could be several potential causes for the problem. Here are a few suggestions to help troubleshoot and resolve the issue:

1. Check Error Messages: ODBC functions in PHP generally return FALSE if there is an error. After executing the `odbc_exec` function, check for any error messages using `odbc_error` and `odbc_errormsg`. For example:
```php
$result = odbc_exec($connection, $query);
if (!$result) {
echo "ODBC Error: " . odbc_errormsg($connection);
}
```

2. Confirm Correct Syntax: Ensure that your SELECT query syntax is correct. Even a small error, such as a missing or misplaced quotation mark or a typo in table or column names, can cause the query to fail.

3. Test with a Simple SELECT Query: Try executing a simple SELECT query that retrieves a single column from a known table to see if it returns a resource ID. This can help determine if the issue is specific to the complex SELECT query you provided.

4. Debugging the Query: To further debug the issue, try using the `odbc_exec` function in a step-by-step manner. Execute the SELECT query in smaller parts and check the result at each step. This can help identify the specific part of the query that is causing the problem.

5. Verify Table and Column Names: Double-check that the table and column names used in the SELECT query are correct, including the case sensitivity. Make sure that the database and table names are properly specified.

6. Try Different Fetch Methods: Instead of using the default fetch method, you can try using other fetch methods provided by the ADODB library, such as `GetAssoc` or `GetRow`, to retrieve the data from the executed query.

7. Check ODBC Driver Compatibility: Ensure that the ODBC driver you are using is compatible with the version of the database you are connecting to. Verify that the driver version supports the syntax and functionality used in your SELECT query.

8. Update ADODB Library: Make sure you are using the latest version of the ADODB library. Older versions may have compatibility issues with certain drivers or database systems. Upgrading to the latest version might resolve the problem.

If the issue persists after trying these steps, consider reaching out to the ADODB library's support resources or the ODBC driver's support to get further assistance in troubleshooting the problem specific to your environment and configuration.