mysql to SQLServer conversion

(6 posts) (2 voices)
  1. eugfri, Member


    I m converting my app build on mydbr from mysql to SQL Server backend.

    I am running into most basic issue but I can't seems to find any resolution for.
    When one of my parameters i need to pass to dbr.editable is a varchar constant, in mysql everything works like a charm

    SELECT 'dbr.editable', '[FirstName]', 'sp_DBR_Generic_edit_col_char', 'inID=ID', 'inTable="customers"','inCol="FirstName"', 'inNewVal=FistName', 'type=autosize';

    I am trying to pass params to my sp_DBR_Generic_edit_col_char proc in SQL Server same exact way

    SELECT 'dbr.editable', '[FirstName]', 'sp_DBR_Generic_edit_col_char', 'inID=ID', 'inTable="customers"','inCol="FirstName"', 'inNewVal=FistName', 'type=autosize';

    i.e. inTable and inCol are varchar constants and my proc has params declared as follows

    sp_DBR_Generic_edit_col_char(@inID int, @inTable varchar(100), @inCol varchar(50), @inNewVal varchar(1000))

    But seems like inTable ends up being null as well as inCol ends up being null when sp_DBR_Generic_edit_col_char is executed.

    I also could not find any dbr.editable examples for SQLServer in the demo I have in my installation.

    What am I doing wrong?

    Thank you

  2. myDBR Team, Key Master

    No change in myDBR code is needed when you change to SQL Server.

    Could you check the ANSI settings you are using in your SQL Server:

    @@OPTIONS AS user_options,
    CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END AS implicit_transactions,
    CASE WHEN @@OPTIONS & 4 = 4 THEN 'ON' ELSE 'OFF' END AS cursor_close_on_commit,
    CASE WHEN @@OPTIONS & 8 = 8 THEN 'ON' ELSE 'OFF' END AS ansi_warnings,
    CASE WHEN @@OPTIONS & 16 = 16 THEN 'ON' ELSE 'OFF' END AS ansi_padding,
    CASE WHEN @@OPTIONS & 32 = 32 THEN 'ON' ELSE 'OFF' END AS ansi_nulls,
    CASE WHEN @@OPTIONS & 256 = 256 THEN 'ON' ELSE 'OFF' END AS quoted_identifier,
    CASE WHEN @@OPTIONS & 1024 = 1024 THEN 'ON' ELSE 'OFF' END AS ansi_null_dflt_on,
    CASE WHEN @@OPTIONS & 1342 = 1342 THEN 'ON' ELSE 'OFF' END AS ansi_defaults

    You can also set the ANSI settings via user/defaults.php:

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

    myDBR Team

  3. eugfri, Member

    thank you!

    I have ran settings check.
    Here is what I got

    implicit_transactions OFF
    ansi_warnings ON
    ansi_padding ON
    ansi_nulls ON
    quoted_identifier ON
    ansi_null_dlft_on ON
    ansi_defaults OFF

    So I have added $mydbr_defaults['db_connection']['sqlsrv_server_init'] setting to my user/defaults.php and bounced Apache, but the problem persists.

    Is there anything else I need to check?

  4. eugfri, Member

    Is there a way to catch column headers of the dataset?

  5. myDBR Team, Key Master

    What is the output from the SQL Editor if you run the dbr.editable command directly?

    Can you open a support ticket with full SQL export of the both reports (main and the editing) so we can take a look at it.

    Is there a way to catch column headers of the dataset?

    The report receives the fetched data, not the metadata.

    myDBR Team

  6. eugfri, Member

    Just an update for anyone reading this in the future - i was on version 6.4.4 when ran into the issue described above. However, patching to the latest 6.5 version made everything work.
    Looks like 6.4.4 may had a bug


You must log in to post.