Sybase Adaptive Server IQ can't SELECT *, always limited to 30?

Question

I've this problem with a Sybase IQ database (version SELECT @@version shows Adaptive Server IQ/12.5.0/0306) using the PHP SQL Anywhere extension.

I can't select all rows, i.e. SELECT * from anytable always returns 30 rows.

The only workaround I've found is using SELECT TOP 1000 * from anytable (maximum is 32767) but there are certain situations where I need all rows.

Any help is much appreciated.

EDIT: example script (much like the documentation)

$conn = sasql_connect("HOST=host:port;DBN=dbn;UID=uid;PWD=pwd");
if (!$conn) { echo "Connection failed."; die(); }

$result = sasql_query($conn, "SELECT * FROM dba.anytable" );
sasql_result_all($result); // display 30 rows in a formatted table
sasql_free_result($result);
sasql_disconnect($conn);

EDIT: specs of both machines where I'm experiencing the same exact problem:

Development machine:

• Windows 7 x64
• PHP 5.4.23 TS x86
• SAP SQL Anywhere PHP Module for PHP 5.4
• SAP SQL Anywhere Database Client 12.0.1.4134

Production sever:

• CentOS 6.6 x64
• PHP 5.5.23 NTS (x86_64 as per YUM)
• SAP SQL Anywhere PHP Module for PHP 5.5
• SAP SQL Anywhere Database Client 12.0.1.4127

Answer 1

Probably a stupid question, but just to be sure.

Did you check in Client --> Tools -> Sybase IQ -> Maximum number of rows to display.

(sry for posting this as an answer, but i dont have enough rep to ask you in comments.) Cheers

Answer 2

Well, there is probably some configurable property somewhere that will release you from the evil clutches of 30. I do not know where that property is. I hope someone finds it.

In case nobody does, however, here is a big hack which orders by the primary key of the given table, retrieves as many rows as it can given your constraints, and keeps track of the last fetched primary key in order to retrieve the next batch of rows. It would be better to adapt this to use START AT or LIMIT / OFFSET if available, but I'm assuming they're not. If one of those is available, you could use this approach for any table. If not, this approach can be adapted to any table which has a unique non-null key.

$conn = sasql_connect("HOST=host:port;DBN=dbn;UID=uid;PWD=pwd");
if (!$conn) {echo "Connection failed."; die(); }

$highest_id = -1;
$num_rows_retrieved = 0;

do {
  if (!sasql_real_query($conn, "SELECT TOP 32767 * FROM dba.anytable where anytable_id > $highest_id order by anytable_id")) {
    echo "Query failed.";
    die();
  }
  $result = sasql_use_result($conn);
  if (!$result) {
    echo "No result set.";
    die();
  }
  $num_rows_retrieved = 0;
  $num_fields = sasql_num_fields($result);
  while ($row = sasql_fetch_row($result)) {
    $highest_id = $row[0];  // assumes anytable_id is the first field
    $i = 0;
    while ($i < $num_fields) {
      echo "$row[$i]\t"; 
      $i++;
    }
    $num_rows_retrieved++;
    echo "\n";
  }
  sasql_free_result($result);
} while ($num_rows_retrieved == 32767);

sasql_disconnect($conn);