Defining an "SQL template"

(10 posts) (2 voices)

Tags:

No tags yet.

  1. apao, Member

    Hello everyone,

    When I explicitely define an option...

    SET QUOTED_IDENTIFIER OFF
    GO

    ...in a procedure, it is "forgotten" by myDBR the next time I edit my procedure since this option is in fact located before the CREATE PROCEDURE statement.

    Is there any workaround so that I don't have so rewrite this option every time I edit my procedure?

    Or would it be possible to define an "SQL template" that would enable me to choose what the SQL editor displays by default when I create a procedure.
    The editor currently displays this when I launch it :

    CREATE PROCEDURE sp_DBR
    AS
    BEGIN

    select
    from
    where

    END
    go

    I would need it to display this instead:


    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE sp_DBR
    AS
    BEGIN

    select
    from
    where

    END
    go

    Best regards.

  2. myDBR Team, Key Master

    Hi,
    For SQL Server SET options, a better solution is to define a default database connection initialization in user/defaults.php, overriding the default set in defaults.php.

    The default is:

    $mydbr_defaults['db_connection']['sql_server_init'] = 'SET ANSI_WARNINGS ON;SET ANSI_NULLS ON;';

    You can add your option:

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

    As for the SQL templates, the next version will include possibility to create your own templates.

    --
    myDBR Team

  3. apao, Member

    Many thanks for your quick answer!
    But it did not work. Is there any other thing to do to complete the action ?
    Or might this option specified in the user/defaults.php be overriden somewhere else? (I checked the "mydbr" database options in SQL Server and they're ok)

  4. myDBR Team, Key Master

    If should work. You can test it by setting different options in user/defaults.php and then run:

    select @@options

    in the SQL Editor. You should see the option value to change.

    --
    myDBR Team

  5. apao, Member

    You had forgotten a ";" in your previous answer (after "SET QUOTED_IDENTIFIER OFF"). In fact, the PHP to add was :

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

    However, many thanks, if works fine now!

    By the way what's the difference between defaults.php and user/defaults.php?
    When do I use one/the other?

  6. myDBR Team, Key Master

    See documentation for more info.

    --
    myDBR Team

  7. apao, Member

    Many thanks!

  8. apao, Member

    Hello everyone,

    I'm back to this thread to know if this possibility is now available:

    As for the SQL templates, the next version will include possibility to create your own templates.

  9. myDBR Team, Key Master

    It is.

    If you do not have any text selected in the editor and you click the "Code snippets"-button, you will see the snippet palette. If you however do have text selected, myDBR offers to make a new snippet from the selection.

    --
    myDBR Team

  10. apao, Member

    Thanks!


Reply

You must log in to post.