Ok, so, I fixed this.

For anyone else looking for an answer (and to save any hair pulling):

If using PHP, prior to Sage v29, you could used extension=php_odbc.dll (Have a look in your php.ini file) which gives you odbc_connect, odbc_exec etc.

Now with v29 that doesn't work. You need to use extension=php_pdo_odbc.dll and do PDO style connection and queries.

Example connection string:

$conn = new PDO('odbc:Driver={Sage Line 50 v29};ServerDSN=SageLine50v29;Uid=YOURSAGELOGIN;Pwd=YOURSAGEPASSWORD;');

Example query:

$sql = "SELECT blah FROM thetable";
$query = $conn->query($sql);
while ($rows = $query->fetch(PDO::FETCH_ASSOC))
{
// Do stuff with data
}
// Close the connection
$conn = NULL;


For the future, could we have some release notes for the ODBC driver about any changes ? Might help people.  I note that the dll file has increased by about 60Kb from v28 to v29. Thats a fair bit of code added/changed.

Ok, so, I fixed this.

For anyone else looking for an answer (and to save any hair pulling):

If using PHP, prior to Sage v29, you could used extension=php_odbc.dll (Have a look in your php.ini file) which gives you odbc_connect, odbc_exec etc.

Now with v29 that doesn't work. You need to use extension=php_pdo_odbc.dll and do PDO style connection and queries.

Example connection string:

$conn = new PDO('odbc:Driver={Sage Line 50 v29};ServerDSN=SageLine50v29;Uid=YOURSAGELOGIN;Pwd=YOURSAGEPASSWORD;');

Example query:

$sql = "SELECT blah FROM thetable";
$query = $conn->query($sql);
while ($rows = $query->fetch(PDO::FETCH_ASSOC))
{
// Do stuff with data
}
// Close the connection
$conn = NULL;


For the future, could we have some release notes for the ODBC driver about any changes ? Might help people.  I note that the dll file has increased by about 60Kb from v28 to v29. Thats a fair bit of code added/changed.

Thank you for this, you just saved my life!

EDIT

For search engines and others who may have this issue, the error I was receiving from my SOAP Server when trying to connect was:

[SQLSTATE=01000][Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behaviour that the application requested (see SQLSetEnvAttr)

WRT the commentary about using PDO for v29:

Has anybody else experienced PHP 8.1 PDO fetch hanging with the "Sage Line 50 v28" driver after a certain number of rows ?

In this example there are approx 3000 records in the STOCK table.
If I set $lngStopAfterRow to 100 then everything works as expected.
If I set $lngStopAfterRow to 150 then it hangs with no output at all.
Same behaviour in both CLI and CGI modes.

------

$query = $conn->query("SELECT * FROM STOCK");

$lngStopAfterRow=100;
$lngRowCount=0;
$boolRunning=true;

while ($boolRunning){
   $lngRowCount++;
   $arrRow = $query->fetch(PDO::FETCH_ASSOC);

  if (!$arrRow){  
    print "EOF at row " . $lngRowCount . "\n";
    $boolRunning=false;
  }

   print "Row " . $lngRowCount . ": STOCK_CODE: " . $arrRow["STOCK_CODE"] . "\n";

  if ($lngRowCount >$lngStopAfterRow){
      print "Stopped at row " . $lngRowCount . "\n";
      break;
   }

}

Same hanging behaviour on "Sage Line 50 v29" on Windows Server 2022.

You should have really started your own thread as this thread is unrelated to your issue.

However, I recommend not using fetch() and instead use fetchAll() as the ODBC driver can be somewhat unstable, and you want to hit that driver as few times as possible.

$query = $conn->query("SELECT * FROM STOCK");

$arrRows = $query->fetchAll(PDO::FETCH_ASSOC);

If you need to limit your results, then you should combine your fetchAll() usage with the ORDER BY and LIMIT keywords within the query itself, pulling each record individually will increase the frequency of your scripts failing.

Hi Iain,

Many thanks for your response. I did consider starting a new thread but thought it would be of more service to the community to have it follow in this thread. I can see the argument both ways :-)

I've just tried using fetchAll: Unfortunately It also hangs.

I never knew that LIMIT was an option in a Sage ODBC query ? What's the correct syntax ?
The following SQL produces an "Invalid table specification" error: SELECT * FROM STOCK LIMIT 10

I did a bunch of tests yesterday with PDO:FETCH_LAZY which produces the weird non-iterable  PDORow object. (Code below)
The very action of assigning the data to a variable, for one of those records for one of those  fields for one of those values, causes the hang. (See highlighted line)
I'm guessing there's a unicode character which is blowing up a string parser down in the basement somewhere.
I've not traced it any further because, even if I corrected the data in this instance, it could show up elsewhere, making the whole thing totally unreliable.

$query=$conn->query("SELECT * FROM STOCK");

$lngStopAfterRow=100;
$lngRowCount=0;
$boolRunning=true;

while ($boolRunning)
{
   $lngRowCount++;
   $objLazy = $query->fetch(PDO::FETCH_LAZY);

   if (!$objLazy){
      print "EOF at row " . $lngRowCount . "\n";
      break;
    }

    $lngFieldCount=0;
    $arrRow=[];

   while(property_exists($objLazy,$lngFieldCount)){
       $arrRow[$lngFieldCount]=$objLazy[$lngFieldCount];
       $lngFieldCount++;
    }

   print "Row " . $lngRowCount . ": STOCK_CODE: " . $arrRow[0] . "\n";
    echo 'lazy assign ', round(memory_get_usage() / 1024), PHP_EOL;

   if ($lngRowCount >$lngStopAfterRow){
      print "Stopped at row " . $lngRowCount . "\n";
      break;
   }

}

I haven't actually used LIMIT in my queries, so I'm just making suggestions, however the way to apply a LIMIT is to have a starting value and an end value (eg: 0 to 10).

SELECT * FROM STOCK LIMIT 0, 10

If the LIMIT doesn't actually work, then I suggest trying the WHERE keyword instead to limit your results.

But really, if you can get away with just pulling the entire dataset with the one fetchAll() call, then you will see less problems (and investigate any data encoding issues outside of hitting the ODBC driver too much).

The LIMIT keyword is not supported by the ODBC driver. You need to use the TOP keyword/syntax instead. 

Thanks Darron.

This works: SELECT TOP 10 * FROM STOCK

Don't suppose there's a paging facility there as well like LIMIT 0,10 ?

Nope, afraid not