1. 

   apao, Member

   Hello everyone,

   I had to upgrade to 5.0.9 recently so that the license I had just renewed could be taken into account.
   Unfortunately, since this update, my reports have no longer been working.

   The error displayed in my reports is the following :
   

   Les requêtes hétérogènes requièrent les options ANSI_NULLS et ANSI_WARNINGS pour être définies pour la connexion. Cela assure la cohérence sémantique de la requête. Activez ces options et réexécutez la requête.

   To sum it up, it says ANSI_NULLS and ANSI_WARNINGS options are not defined. But they are (and have been for several years). I defined them in the mydbr/user/defaults.php file, like this :
   $mydbr_defaults['db_connection']['sql_server_init'] = 'SET ANSI_WARNINGS ON;SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER OFF;';

   It's always been working since I set it up a few years ago so the problem is likely to come from (one of) myDBR latest version(s).
   Is there a reason why myDBR would no longer take into account the defaults.php file or would not longer take into account the instructions written inside?

   Thanks in advance for your help.

   PS: I can't say exactly which version of myDBR we ran before the upgrade but it was probably not very old (I upgrade it rather often because there's usually no problem when I do)
2. 

   myDBR Team, Key Master

   Hi,
   what is the database driver you are using to connect to your instance (mssql or sqlsrv)?

   The entry for the mssql is

   $mydbr_defaults['db_connection']['sql_server_init']

   and the entry for the sqlsrv is

   $mydbr_defaults['db_connection']['sqlsrv_server_init']

   If the entries above are correct, try with a simple report what the value for the @@OPTIONS is inside the report.

   There is no change in the initialization in 5.0.9.

   --
   myDBR Team
3. 

   apao, Member

   First of all, thanks for your answer.

   The drivers I had installed are "Microsoft PHP Drivers for SQL Server". How do I know if they are mssql or sqlsrv? What's the difference between them?
   However, my mydbr/user/defaults.php file hasn't been modified for months, so why would the $mydbr_defaults['db_connection']['sql_server_init'] instruction inside suddenly stop working?

   As you asked, I've displayed the @@OPTIONS in a report and it says 5424.

   I've done another test, adding this line to my report :
   SELECT 'test', "25";
   If the option SET QUOTED_IDENTIFIER OFF; was properly enforced, SQL Server would consider 25 as a string, just like test, even if I use double quotes for 25.
   Instead, this raises an error (invalid column name: '25'), which shows that the option defined below in my mydbr/user/defaults.php is not enforced :
   $mydbr_defaults['db_connection']['sql_server_init'] = 'SET ANSI_WARNINGS ON;SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER OFF;';
4. 

   myDBR Team, Key Master

   The "Microsoft PHP Drivers for SQL Server" is identified as sqlsvr in PHP. This is the driver whose initialization in myDBR is controlled with setting:

   $mydbr_defaults['db_connection']['sqlsrv_server_init']

   The one you have been using (sql_server_init), if for older FreeTDS based mssql-driver (that was dropped from PHP 7).

   The reason for the change in behavior after update was that initialization for both mssql and sqlsrv were harmonized in myDBR 5.0.3 back in February. The change was done as many users were updating tp PHP 7, hence also changing from mssql to sqlsrv. The change made the transition easier (yours would have continued to work if you were using the correct initialization setting).

   Just change the setting to:

   $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER OFF;';

   and everything will be ok again.

   --
   myDBR Team
5. 

   apao, Member

   Ok, thanks, this seems to work.
   Is there any other file I should modify?

   PS: I see PHP 5.6 will soon be mandatory to run myDBR. Do you know when?
6. 

   myDBR Team, Key Master

   We've put up an advance recommendation to update PHP to at least 5.6. There really is no reason to use PHP older than 5.6 as older version no longer receive security updated and updating PHP to a newer version is usually quite simple procedure.

   For myDBR development phasing out the obsolete PHP-versions allows for us to use libraries which require newer PHP version.

   The timetable for PHP 5.6 requirement is late this year / early next year. We do highly recomment using latest PHP versions (7.2 as of this writing). The performance gains are substantial with smaller resource requirements.

   --
   myDBR Team
7. 

   apao, Member

   Thanks.
   And could you please confirm that there is no other mydbr instruction I need to change in any other file? (like the instruction $mydbr_defaults['db_connection']['fix_sqlsrv_field_metadata'] = true; for instance)
8. 

   myDBR Team, Key Master

   No other settings is required.

   The 'fix_sqlsrv_field_metadata' was there to fix a problem the SQLSRV Windows-drivers had prior 4.1.5 release. Microsoft has since fixed the issue and therefore the fix in myDBR is disabled by default.

   --
   myDBR Team
9. 

   apao, Member

   OK, thanks!

Reply

You must log in to post.