1
ODBC driver Read-Only checkbox doesn't make the connection read-only
Problem reported by Stan Paulauskas - 3/1/2023 at 7:09 AM
Not A Problem
I'm using Atrex 19 and the Atrex/ElevateDB 2 Data Access Components 2.36.0.1. On my local system, I setup a System DSN and checked the "Read-Only" box on the first configuration page.
During a recent test, the test program successfully performed an update on the data. This seems to run counter to setting the DSN to be Read-Only. Can you provide some additional insight into what this checkbox actually does?

6 Replies

Reply to Thread
1
Terry Swiers Replied
Employee Post
The check box sets the ODBC DSN to read only.  Not sure what to tell you about your results, but I tried updating a read only DSN using Delphi FireDAC and dbGo components as well as straight php 8 and got the following results for each:
Delphi FireDac (32bit): [FireDAC][Phys][ODBC][Elevate Software][ElevateDB] ElevateDB Error #403 Data source is read-only.

Delphi dbGo (32bit): [Elevate Software][ElevateDB] ElevateDB Error #403 Data source is read-only.

PHP 8 (64bit): Warning: odbc_exec(): SQL error: [Elevate Software][ElevateDB] ElevateDB Error #403 Data source is read-only, SQL state in SQLExecDirect
PHP initially allowed me to make the update call, and it took me a little while to realize that the Delphi connections were 32bit and the php instance was 64bit.  I modified the 64bit DSN entry to set the read only flag and it then gave me the expected results.
0
Stan Paulauskas Replied
Since the ODBC driver is the one controlling the Read-Only status, I find it hard to believe that this is dependent upon the ODBC library being used to interface with the driver. 

Could you provide the exact version of the driver you're using along with the PHP code? That'll help me test on my end.
0
Terry Swiers Replied
Employee Post
I've tested both 2.37.0.1 and 2.36.0.1 under php 7.4.4, and 2.37.0.1 under php 8.0.26.  The test script is below, just make sure you change the settings in the GetConnection function to match your DSN.

<?php
class ODBCTest
{
    private $_Connection; //holds the ODBC connection

    function __construct() {
    }

    function __destruct() {
        $this->CloseConnection(false);
    }

    private function GetConnection() {
        $dbtype="odbc";
        $cursr="SQL_CUR_DEFAULT";
        $dsn="<dsn>";
        $usr="<user>";
        $pwd="<password>";

        $this->_Connection = odbc_connect($dsn, $usr, $pwd);
        if ($this->_Connection == false) { echo 'NO ODBC Connection'; die; }
    }

    public function TestUpdate() {
        $this->GetConnection();
        $_query = "update code set onhand = onhand where stockcode = 'zz44'";
        odbc_exec($this->_Connection, $_query);
        echo odbc_errormsg($this->_Connection);
    }
    private function CloseConnection($do_die)
    {
        if ($this->_Connection != false) { odbc_close($this->_Connection); }
        if ($do_die == true) {
            $this->_Connection = false;
            die;
        }
    }

}

$obj = new ODBCTest();
$obj->TestUpdate();
0
Stan Paulauskas Replied
Where can I get the latest version of the driver?
0
Terry Swiers Replied
Employee Post
I've emailed you the link.  While the version number changed to match that of the database engine updates, there are no changes between 2.37.0.1 and 2.36.0.1 related to the ODBC driver.
0
Stan Paulauskas Replied
Since there's no functional difference in the ODBC driver I didn't update that. I installed PHP 8.2.3. I've been able to reproduce the Read-Only state with PHP: 

Warning: odbc_exec(): SQL error: [Elevate Software][ElevateDB] ElevateDB Error #403 Data source is read-only, SQL state
I'm able to reproduce the expected behavior in python if I 
  • explicitly set the connection to read-only in my code and
  • explicitly committing the statement.
This is inconsistent with my results from yesterday. I'm lacking a good explanation of how the update happened since all the tests today are consistent. 

I'm more than willing to chalk this up to user error. Thanks for the help @Terry!

Reply to Thread