ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL REGEXP _COUNT works fine in interactive SQL, but fails when in embedded SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL REGEXP _COUNT works fine in interactive SQL, but fails when in embedded SQL

    When i run the following SQL in interactive SQL it runs fine and delivers a result that is expected,
    however doing the same in embedded sql never performs correctly

    i must be overlooking something.; but i can't find what it is.??

    IN INTERACTIVE SQL
    PHP Code:
    Select RegExp_count('Valid@Email..com',
     
    '^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$')     
    FROM sysibm/sysdummy1

    this shows 1.
    which is a valid address 


    Select RegExp_count
    ('InValid@Email..com',
     
    '^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$')     
    FROM sysibm/sysdummy1
    this shows 0.
    which is a also okbecause invalid address 


    in EMBEDDED SQL.
    always returns invalid address !! when debugging, both SQLCOD and sqlstate show ok

    PHP Code:
    **FREE
        dcl
    -s yValid          ind;

        
    yValid ValidEmail('Valid@Emailqplas.com');
        
    yValid ValidEmail('inValid@Email..com');
        *
    inlr = *on;
        return;

       
    DCL-PROC ValidEmail;
          
    Dcl-pi *N ind;
            
    p9email  char(80) const;
          
    End-pi;
          
    dcl-s inCount int(10);
          
    dcl-s inEmail varchar(80);
          
    inCount=0;
          
    inEmail P9Email;
          
    EXEC SQL SELECT regexp_count(TRIM(:inemail),
                     
    '^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$')
                  
    INTO :incount
                  FROM sysibm
    /sysdummy1;
          return 
    inCount 1;    //1=valid Address

       
    END-PROC VALIDEMAIL

  • #2
    Could it be a CCSID issue? Is program using the same CCSID as your interactive SQL?

    Comment

    Working...
    X