7 Replies Latest reply on Nov 12, 2015 9:29 AM by Ramesh Reddy

    DynamicUnionsProc

    virtualdatabase Apprentice

      Not positve I can expect this but....

       

      I have a proc

       

      BEGIN

        DECLARE integer VARIABLES.NBR;

        DECLARE integer VARIABLES.counter = 1;

        DECLARE string VARIABLES.execString = '';

        DECLARE string VARIABLES.LoopString = '';

        DECLARE string VARIABLES.BaseexecString = '';

        DECLARE string VARIABLES.GetNumberQuery = '';

        DECLARE string VARIABLES.ColumnsString = '';

        DECLARE string VARIABLES.ColumnsQueryString = '';

        VARIABLES.ColumnsQueryString = ((((((((((((('Select  name' || '||') || ' ') || chr(39)) || ' ') || chr(39)) || '|| datatype ||') || chr(39)) || ',') || chr(39)) || ' from  sys.columns where TableName = ') || Util.DynamicUnions.INPUT_TableName) || '_01') || Chr(39));

        EXECUTE IMMEDIATE VARIABLES.ColumnsQueryString AS x string INTO #TEMP1;

        VARIABLES.ColumnsString = (SELECT * FROM #TEMP1);

        VARIABLES.ColumnsString = substring(VARIABLES.ColumnsString, 1, (Length(VARIABLES.ColumnsQueryString) - 1));

        VARIABLES.BaseexecString = ('Select  *  FROM ' || Util.DynamicUnions.INPUT_TableName);

        VARIABLES.GetNumberQuery = ('SELECT EmdeonUtil.TableCounts.NbrOfTables FROM Util.TableCounts WHERE Util.TableCounts.ConcernedTables = ' || Util.DynamicUnions.INPUT_TableName);

        EXECUTE IMMEDIATE VARIABLES.GetNumberQuery AS y integer INTO #TEMP2;

        VARIABLES.NBR = (SELECT * FROM #TEMP2);

        WHILE(VARIABLES.counter <= VARIABLES.NBR)

        BEGIN

        VARIABLES.LoopString = (((VARIABLES.BaseExecString || '_') || CASE WHEN VARIABLES.counter < 10 THEN ('0' || VARIABLES.counter) ELSE VARIABLES.counter END) || ' UNION ALL ');

        VARIABLES.execString = ((VARIABLES.execString || ' ') || VARIABLES.LoopString);

        VARIABLES.counter = (VARIABLES.counter + 1);

        END

        VARIABLES.execString = substring(VARIABLES.execString, 1, (length(VARIABLES.execString) - 10));

        SELECT ((VARIABLES.execString || ' as ') || VARIABLES.ColumnsString);

      END

       

      it compiles fine

      At this point I'm just trying to return the ExecString that will later be replaced by an Execute Immediate VARIABLES.execString

      My problem is upon execution I get this error:

       

      Error: TEIID30168 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE VARIABLES.ColumnsQueryString AS x string INTO #TEMP1" with the SQL statement "VARIABLES.ColumnsQueryString" due to: TEIID31100 Parsing error: Encountered "TableName = CLIENT_RNC_HISTORY_01[*]'[*]" at line 1, column 94.

      Lexical error. Character is not a valid token: '

      SQLState:  50000

      ErrorCode: 30168

       

      I'm not sure what the issue is..

       

      I've already made some changes using the TEMP tables above (of which I'm not sure I Need to)

      Hopefully the idea of what I'm trying to do is clear...

      Can I expect to be able to do it?

      If so... what's wrong?


      Thanks in  advance!

        • 1. Re: DynamicUnionsProc
          Ramesh Reddy Master

          You can always test what you generating with System Procedures - Teiid 8.12 (draft) - Project Documentation Editor SYSADMIN.logMsg command and correct the query.

          • 2. Re: DynamicUnionsProc
            virtualdatabase Apprentice

            I've tried this..

            EXEC sysadmin.logMsg(context => 'INFO', msg => VARIABLES.CreateTableString);

             

            But nothing appears in the console window when executed.

            • 3. Re: DynamicUnionsProc
              Steven Hawkins Master

              It looks like you may have mixed up context and level.

              • 4. Re: DynamicUnionsProc
                Ramesh Reddy Master

                That is not correct. Try

                 

                EXEC sysadmin.logMsg(context => 'org.teiid',  level=>'INFO', msg => VARIABLES.CreateTableString);

                 

                Note context is something you need to configure in "logging" section of the standalone.xml and make sure it is configured to allow logging on that context. The above "org.teiid" is by default open at INFO level, so the above should work.

                • 5. Re: DynamicUnionsProc
                  jie tao Novice

                  I have also problem with this. I use exactly EXEC sysadmin.logMsg(level => 'INFO', context => 'org.teiid', msg => VARIABLES.result) but do not see the log in the file standalone/log/serve.log

                   

                  in the standalone-teiid.xml I see:

                   

                  <subsystem xmlns="urn:jboss:domain:logging:1.5">

                              <console-handler name="CONSOLE">

                                  <level name="INFO"/>

                                  <formatter>

                                      <named-formatter name="COLOR-PATTERN"/>

                                  </formatter>

                              </console-handler>

                              <periodic-rotating-file-handler name="FILE" autoflush="true">

                                  <formatter>

                                      <named-formatter name="PATTERN"/>

                                  </formatter>

                                  <file relative-to="jboss.server.log.dir" path="server.log"/>

                                  <suffix value=".yyyy-MM-dd"/>

                                  <append value="true"/>

                              </periodic-rotating-file-handler>

                              <logger category="com.arjuna">

                                  <level name="WARN"/>

                   

                  .......

                  • 6. Re: DynamicUnionsProc
                    jie tao Novice

                    I see the log at the runtime, but I do not see the log at design time with preview in Teiid designer. Actually I need the log in the design time

                    • 7. Re: DynamicUnionsProc
                      Ramesh Reddy Master

                      It is part of server log as the procedure gets executed in Server. Designer does not scrape or watch the logs in server. Unfortunately we do not have a step debugger for your procedure in Designer.