6 Replies Latest reply on Aug 6, 2012 11:19 AM by 939732

    How to validate SQL syntax in Application Engine Peoplecode

    939732
      Hi,

      Can anyone help me out for how to validate SQL in Application Engine Peoplecode. I just need to validate SQL syntax. If syntax is wrong it should log exception in log file but Apllication engine should not go on NO SUCCESS. App Engine should continue with other SQL validation to check correct syntax and to fetch values.

      Below given is peoplecode which is I am currently using.

      Thanks in Advance.


      Local string &CSG_SQL_ID, &sqltext;
      Local date &effdt;
      Local boolean &sql_flag = True;

      &CSG_SQL_ID = CSG_REP_DIS_AET.CSG_SQL_ID.Value;
      SQLExec("SELECT CRAD.SQLTEXT FROM PS_CSG_SQL_TMP CST, PS_CSG_REP_AUT_DST CRAD WHERE CST.CSG_SQL_ID = CRAD.CSG_SQL_ID AND CST.EFFDT = CRAD.EFFDT AND CST.CSG_SQL_ID = :1", &CSG_SQL_ID, &sqltext);

      try

      &sql_flag = SQLExec(&sqltext);
      If &sql_flag = True Then
      &sql = CreateSQL(&sqltext);
      Local string &SqlString = &sql.Value;
      Logger(String(ExtractTablesJob(&SqlString)));
      Logger(String(ExtractTablesDept(&SqlString)));
      End-If;

      catch Exception &sqlerror
      MessageBox(0, "", 0, 0, "Exception found");
      end-try;
        • 1. Re: How to validate SQL syntax in Application Engine Peoplecode
          HakanBiroglu
          HI,

          SQLEXEC is never validated by PeopleCode, you should avoid this a much as possible.
          Rather use record/rowset objects to retrieve data. This will ensure the correct statements towards the database.

          Or better yet divide your peoplecode and write the sql statements in sql steps in application engines.

          Hakan
          • 2. Re: How to validate SQL syntax in Application Engine Peoplecode
            Stephen Phillips
            Do you see the "Exception Found" message? If so, you may be successfully trapping the error but may be closing a SQL cursor that is stopping the program.

            I don't know if this is the same problem, but I had an issue with CIs. Intermittently, when a CI errs, it rollsback. The rollback closes the cursor of my Do Select that was calling the CI PeopleCode, and that stops the program. It wouldn't give me any error messages as to what the problem was, it would just terminate at the end of the program. I could put a messagebox at the very end of the program to verify that it was reaching the end. Then, it wouldn't proceed to the next step.

            The solution ended up being to make the DoSelect restartable. So, if that is your problem, you might try changing the Select Type.

            I hope that tidbit helps.
            • 3. Re: How to validate SQL syntax in Application Engine Peoplecode
              939732
              Hi Hakan,

              Thanks for your help. Sorry for my late reply.

              I tried with with approach given by you but it didnot work. Should I call another application engine where SQL will be validated?

              Regards,
              Susmit
              • 4. Re: How to validate SQL syntax in Application Engine Peoplecode
                939732
                Hi Phillips,

                Thanks for your help. Sorry for my late reply.

                Yes, I am using DoSelect before validating each SQL in my program and I also tried to make restartable DoSelect step but still it failed.

                Regards,
                Susmit
                • 5. Re: How to validate SQL syntax in Application Engine Peoplecode
                  Stephen Phillips
                  Susmit,

                  I apologize: I remembered wrong. The Select Type should be reselect. I had opened a ticket with Oracle about this issue. I have hopes that maybe they will build better error messaging into the tools, but we'll see. The real outcome of the ticket is that changing the Select Type to Reselect fixed my issue.

                  Remember that when you choose Reselect, you will have to track where you are. The Reselect runs the DoSelect SQL statement every time until it returns no rows. So, you will need a processed flag on the table. You're DoSelect should only select the rows where the processed flag is N. Then, you should update the row you are processing to set the processed flag to Y. That way you will move through the rows processing each one.

                  Also, just to be clear, I don't think App Designer will ever "validate" your SQL. It lets the database do that when you run it. It might be frustrating as far it not telling you as you develop, but the advantage is that App Designer doesn't limit you. You can place whatever SQL you want into your program, and it will pass it on to the database. Stored procedures, functions, special platform specific things all work because App Designer doesn't try to validate it.

                  If you like, you can copy your SQL from the SQLExec to a SQL definition:
                  SELECT CRAD.SQLTEXT
                  FROM PS_CSG_SQL_TMP CST,
                  PS_CSG_REP_AUT_DST CRAD
                  WHERE CST.CSG_SQL_ID = CRAD.CSG_SQL_ID
                  AND CST.EFFDT = CRAD.EFFDT
                  AND CST.CSG_SQL_ID = :1

                  You could name it MY_AE_SQL.

                  Then, in your App Engine PeopleCode, you can change the SQLExec to:
                  SQLExec(SQL.MY_AE_SQL, &CSG_SQL_ID, &sqltext);


                  This will place your SQL in a spot that formats it better, but I don't think it will validate it any more. In my opinion, it makes your code harder to read. Someone coming along behind you has to open multiple pieces of code to see what your program is doing.

                  The more I look at your program, you probably want to validate and protect the SQL that is in &sqltext rather than the SQLExec. If you want it to be dynamic, I don't see anything you can do.

                  You could possibly launch another App Engine as another process. You could use the ProcessRequest API object to schedule the program through the Process Scheduler. You would have to pass your dynamic SQL to the other program through some sort of run control. That way each SQL would run as a separate instance in Process Monitor and would run to Success or No Success individually. I still think this is overly complicated.

                  I hope those thoughts help. It looks like a tough task.

                  Stephen
                  • 6. Re: How to validate SQL syntax in Application Engine Peoplecode
                    939732
                    Hi Phillips,

                    Now it works :-) I just used DoSelect with Reselect type & updated Process Flag as you guided.

                    Thanks a lot for helping me:-)

                    Regards,
                    Susmit