Topic
  • 5 replies
  • Latest Post - ‏2014-02-28T17:10:53Z by CRPence@vnet.ibm.com
mkoni
mkoni
25 Posts

Pinned topic SEND MSG FROM SQL

‏2014-02-26T10:16:55Z |

Is it possible to send a message (e.g. to QSYSOPR) from within a SQL Procedure ?

  • NickLawrence
    NickLawrence
    60 Posts
    ACCEPTED ANSWER

    Re: SEND MSG FROM SQL

    ‏2014-02-27T19:01:28Z  
    • mkoni
    • ‏2014-02-27T17:00:05Z

    The Question is how can I send a message with blank charactrer in it ?

    The following Command works from within SQL:

    CALL QCMDEXC ('SNDMSG MSG(TEST) TOUSR(QSYSOPR)', 0000000031.00000)

     

    but the following commands don't work (here is a blank between the two TEST)

    CALL QCMDEXC ('SNDMSG MSG(TEST TEST) TOUSR(QSYSOPR)', 0000000036.00000)

    CALL QCMDEXC ('SNDMSG MSG('TEST TEST') TOUSR(QSYSOPR)', 0000000038.00000)

     

    Could anybody help me ?

    I think the problem are the quotes (') , I tried different (double quotes, single quotes) but nothing works.

    CL requires that the MSG parameter contain a quoted string for you example.  (single quotes).

    SQL rules state that if you need to include a single quote in a string, you can do that with two single quotes.

     

    CALL QCMDEXC ('SNDMSG MSG(''TEST TEST'') TOUSR(QSYSOPR)')

     

    Double quotes ("") in SQL are used to delimit a name (such as a column, schema, or table).

     

    BTW:  There was an enhancement made to the QCMDEXC procedure recently that allows you to omit the length of the string. You can read about it here: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.QCMDEXC%28%29%20procedure%20no%20longer%20requires%20a%20command%20length
     

    Kent Milligan has a few posts in this forum for workshops on SQL modernization (for various skill levels)...if you are looking for SQL education (or have co-workers that are) - you might find those helpful.

    Updated on 2014-03-02T19:25:18Z at 2014-03-02T19:25:18Z by NickLawrence
  • mkoni
    mkoni
    25 Posts

    Re: SEND MSG FROM SQL

    ‏2014-02-27T08:53:02Z  

    I found the solution myself

    I found it here:  https://www-304.ibm.com/support/docview.wss?uid=nas8N1012893

  • mkoni
    mkoni
    25 Posts

    Re: SEND MSG FROM SQL

    ‏2014-02-27T17:00:05Z  

    The Question is how can I send a message with blank charactrer in it ?

    The following Command works from within SQL:

    CALL QCMDEXC ('SNDMSG MSG(TEST) TOUSR(QSYSOPR)', 0000000031.00000)

     

    but the following commands don't work (here is a blank between the two TEST)

    CALL QCMDEXC ('SNDMSG MSG(TEST TEST) TOUSR(QSYSOPR)', 0000000036.00000)

    CALL QCMDEXC ('SNDMSG MSG('TEST TEST') TOUSR(QSYSOPR)', 0000000038.00000)

     

    Could anybody help me ?

    I think the problem are the quotes (') , I tried different (double quotes, single quotes) but nothing works.

  • NickLawrence
    NickLawrence
    60 Posts

    Re: SEND MSG FROM SQL

    ‏2014-02-27T19:01:28Z  
    • mkoni
    • ‏2014-02-27T17:00:05Z

    The Question is how can I send a message with blank charactrer in it ?

    The following Command works from within SQL:

    CALL QCMDEXC ('SNDMSG MSG(TEST) TOUSR(QSYSOPR)', 0000000031.00000)

     

    but the following commands don't work (here is a blank between the two TEST)

    CALL QCMDEXC ('SNDMSG MSG(TEST TEST) TOUSR(QSYSOPR)', 0000000036.00000)

    CALL QCMDEXC ('SNDMSG MSG('TEST TEST') TOUSR(QSYSOPR)', 0000000038.00000)

     

    Could anybody help me ?

    I think the problem are the quotes (') , I tried different (double quotes, single quotes) but nothing works.

    CL requires that the MSG parameter contain a quoted string for you example.  (single quotes).

    SQL rules state that if you need to include a single quote in a string, you can do that with two single quotes.

     

    CALL QCMDEXC ('SNDMSG MSG(''TEST TEST'') TOUSR(QSYSOPR)')

     

    Double quotes ("") in SQL are used to delimit a name (such as a column, schema, or table).

     

    BTW:  There was an enhancement made to the QCMDEXC procedure recently that allows you to omit the length of the string. You can read about it here: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.QCMDEXC%28%29%20procedure%20no%20longer%20requires%20a%20command%20length
     

    Kent Milligan has a few posts in this forum for workshops on SQL modernization (for various skill levels)...if you are looking for SQL education (or have co-workers that are) - you might find those helpful.

    Updated on 2014-03-02T19:25:18Z at 2014-03-02T19:25:18Z by NickLawrence
  • mkoni
    mkoni
    25 Posts

    Re: SEND MSG FROM SQL

    ‏2014-02-28T07:48:12Z  

    CL requires that the MSG parameter contain a quoted string for you example.  (single quotes).

    SQL rules state that if you need to include a single quote in a string, you can do that with two single quotes.

     

    CALL QCMDEXC ('SNDMSG MSG(''TEST TEST'') TOUSR(QSYSOPR)')

     

    Double quotes ("") in SQL are used to delimit a name (such as a column, schema, or table).

     

    BTW:  There was an enhancement made to the QCMDEXC procedure recently that allows you to omit the length of the string. You can read about it here: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.QCMDEXC%28%29%20procedure%20no%20longer%20requires%20a%20command%20length
     

    Kent Milligan has a few posts in this forum for workshops on SQL modernization (for various skill levels)...if you are looking for SQL education (or have co-workers that are) - you might find those helpful.

    many thanks for your answer and for the information of the enhancement, it helps.

    thx

  • CRPence@vnet.ibm.com
    38 Posts

    Re: SEND MSG FROM SQL

    ‏2014-02-28T17:10:53Z  
    • mkoni
    • ‏2014-02-27T17:00:05Z

    The Question is how can I send a message with blank charactrer in it ?

    The following Command works from within SQL:

    CALL QCMDEXC ('SNDMSG MSG(TEST) TOUSR(QSYSOPR)', 0000000031.00000)

     

    but the following commands don't work (here is a blank between the two TEST)

    CALL QCMDEXC ('SNDMSG MSG(TEST TEST) TOUSR(QSYSOPR)', 0000000036.00000)

    CALL QCMDEXC ('SNDMSG MSG('TEST TEST') TOUSR(QSYSOPR)', 0000000038.00000)

     

    Could anybody help me ?

    I think the problem are the quotes (') , I tried different (double quotes, single quotes) but nothing works.

    Given the double-quote character and two consecutive apostrophes are almost indistinguishable in many proportional\variable-pitch fonts, the expression offered by Nick may appear [to many eyes] to show the use of double-quotes, even if seeming to contradict the associated words.  The simplest of explanations IMO is that the apostrophes, when embedded within an apostrophe-delimited string, must be escaped with an apostrophe character; i.e. doubling the apostrophes [referred to elsewhere as single-quote, thus doubling single-quotes would describe the corrective, which is IMO just as deceptive in wording, as is the visual presentation having utilized a poorly chosen\defaulted font].  Thus resolution could be either one of the following, but shown here in a monospace\fixed-width font:

    CALL QCMDEXC ('SNDMSG MSG(''TEST TEST'') TOUSR(QSYSOPR)', 0000000038.00000)

    CALL QCMDEXC ('SNDMSG MSG(''TEST TEST'') TOUSR(QSYSOPR)')

    Notice however, that the size of the string is not impacted by the escape characters; i.e. the length of 38 [visible in the example where a length is provided] is unchanged, because the escape characters are merely an indication of how the string is to be processed, rather than actually being part of the string.