Code For Automatically converting POST-QUERY Triggers into Procedures
Code For Automatically converting POST-QUERY Triggers into Procedures
Titleimage
Posted by Patrick Hamou on 2016:04:19 16:30:11
Overview
This code provides a solution to the issues raised in Bug:1702454 and BUG:1396180 to do with changes in Cursor handling between Forms 4.5 and 6i. In Forms 6i,cursors that are declared in triggers are automatically closed when the trigger ends. This contrasts with Forms 4.5 where the cursors are merely cancelled and not closed. This closing of the cursor has both an upside and a downside. On the upside, database resource usage is reduced as cursors are not left around in the SGA waiting to be re-used. On the downside, however, this means that if the cursor is needed again, it has to be re-parsed and re-opened before it can be executed again. This has a corresponding time cost. So in some circumstances, such as the use of cursors in POST-QUERY triggers when the trigger will fire for each row that comes back from the database, performance can considerably degrade after upgrade to Forms 6i (even though database resource usage will have dropped in compensation). Fortunately there is a simple way to revert the behaviour to the 4.5 scenario of simply canceling the cursors, rather than closing them. In Forms 6i, if you code cursors into PL/SQL program units, rather than into triggers, then the cursor will not be closed when the Program Unit completes. This allows you to move the cursor to a PL/SQL procedure which you then proceed to call from the original POST-QUERY trigger, restoring the performance profile of the 4.5 application. Of course if you have a large number of such triggers to process, this could be a long job. The code in this article is Forms API code which will automate this migration of code for you.
Program Notes
The supplied code is designed to be compiled on 32 bit Windows using Microsoft Visual C++ version 5.0 or 6.0 The program could be adapted for use on UNIX if required, some changes to string handling would be required.
The C file should be compiled and linked with the ifd2f60.lib file which can be found, along with all of the required header files in the %ORACLE_HOME%/forms60/API directory.
For more details on creating Forms API programs, see http://www.oracle.com/technetwork/developer-tools/forms/documentation/274326-134740.pdf
The program as supplied takes four command line arguments, the first two of which are required.
The first argument is the name of the Form to process. The second argument is the database connect string required to compile the Form. The Third argument is a string containing any or all of the letters "FBIP" which define where the program should look for POST-QUERY triggers:
F = Search at Form Level
B = Search at Block Level
I = Search at Item Level Trigger
P = Search in Property Classes
The default is FBIP meaning do all of the above. The final argument is a test flag, supply Y as the final argument and the program will check and report on candidate triggers to change but not actually make the change.
The program writes output to a file with the same name as the form, but with a TXT extension.
During processing if a POST-QUERY is encountered with a cursor or select statement in it a new program unit called <OBJECTNAME>_POST_QUERY which will be called from the origional POST-QUERY trigger.
NOTE: If you have name clashes between objects of the same name (e.g. a BLOCK and an ITEM) then the conversion will fail. You will have to alter this code to generate unique names.
References
Bug 1702454 - PERFORMANCE DEGRADATION DUE TO ADDITIONAL NETWORK TRAFFIC AFTER UPGRADE TO 6.0
Bug 1396180 - PERFORMANCE DEGRADATION DUE TO ADDITIONAL NETWORK TRAFFIC AFTER UPGRADE TO 6.0
Caution
The sample program in this article is provided for educational purposes only and is NOT supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
You should always make backup copies of any Forms modules before running API programs against them.
Program
/***************************************************************************************/
/* */
/* NAME */
/* RewritePostQuery */
/* */
/* DESCRIPTION */
/* Looping through each Form's trigger to detect SELECT and CURSOR clause */
/* Creating a Program Unit for each trigger dectected with the trigger's code */
/* Transforming CURSOR structure into SELECT ...INTO structure */
/* Calling Program Unit in trigger's code */
/* */
/* Command line syntax is: */
/* <program name> <filename> <user/password@connectString> [FBIP] */
/* F = Searching into Form Level Trigger */
/* B = Searching into Block Level Trigger */
/* I = Searching into Item Level Trigger */
/* P = Searching into Property Class */
/* => Can be combined */
/* [Y|N] */
/* Y = Report on candidate triggers but don't make the change */
/* N = (default) make the change
/* */
/* Public FUNCTION(S) */
/* enableComponents => */
/* detectSelect => */
/* detectCursor => */
/* set_puName => */
/* createProgramUnit => */
/* IsV2Style => */
/* */
/* PRIVATE FUNCTION(S) */
/* append_BeginEnd => */
/* strcasestr => */
/* strncasecmp => */
/* */
/* MODIFIED */
/* mdurouss 10/14/2001 - Creation */
/* mdurouss 11/02/2001 - Test only Block's trigger POST-QUERY */
/* - Don't transform Trigger's code in Uppercase */
/* => Add strcasestr & strncasecmp functions */
/* mdurouss 11/04/2001 - Correction %NOTFOUND & %FOUND treatment */
/* carriage return in case of comments in Select & Into */
/* - Correction CursorFetchList & CursorSelectList to hold */
/* - Correction searching strings "INTO " or "INTO\n" */
/* - Correction size of Arrays for Cursor Lists from 10 to 20*/
/* - Treatment of cursor opened more than one time */
/* regarding cursor name declared more than one time but */
/* for different SELECT statement */
/* mdurouss 11/09/2001 - Correction searching strings "CURSOR " or "CURSOR\t" */
/* "SELECT " or "SELECT\n"or "SELECT\t","FROM " or "FROM\n"*/
/* "FROM\t","WHERE " or "WHERE\n" or "WHERE\t" */
/* - Correction for commented cursors , adding comment around*/
/* the new block BEGIN .... END; */
/* - Writing output into a file */
/* drmills 02/21/2002 - Redirect initial output to stderr to prevent exception */
/* when no arguments are supplied. Cleaned up */
/***************************************************************************************/
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <d2ferr.h>
#include <d2fctx.h>
#include <d2ffmd.h>
#include <d2fblk.h>
#include <d2fitm.h>
#include <d2falt.h>
#include <d2ftrg.h>
#include <d2frdb.h>
#include <d2fdef.h>
#include <d2fpgu.h>
#include <d2fppc.h>
#define BUFSIZE 128
#define SUCCESS 0
#define FAIL 1
#define PUNAMEMAXLENGTH 28
int set_puName(char * name, char *sourceObject, char *triggerType);
int append_BeginEnd(char *puCodeIn, char *progUnitNameIn, char *triggerCodeIn);
int createProgramUnit(char *progUnitName,
d2fctx *pd2fctx,
d2ffmd *pd2ffmd,
d2ftrg *pd2ftrg,
char *triggerCode);
int enableComponents(char *componentFlag);
int IsV2Style(d2fctx *pd2fctx,d2ftrg *pd2ftrg);
int detectSelect(char *triggerCode);
int detectCursor(char *triggerCode);
char * strcasestr (char * s1, char * s2);
int strncasecmp (char * a, char * b, int n);
int FORM = FALSE;
int BLOCK = FALSE;
int ITEM = FALSE;
int PROPERTYCLASS = FALSE;
int PU_uniqueCounter = 1;
FILE *output = stderr;
int main (int argc, char * argv[])
{
d2fctx *pd2fctx;
d2ffmd *pd2ffmd;
d2ftrg *pd2ftrg;
d2fblk *pd2fblk;
d2fitm *pd2fitm;
d2fctxa d2fctx_attr;
d2fstatus status;
d2fppc *pd2fppc;
char buf[BUFSIZE];
char *pd2ffmd_name=(char*)0;
int item_count = 0;
int cSize=0;
char *triggerCode;
char *name, *triggerName;
char *progUnitName;
int flagSelects = FALSE;
text* output_name = (char*)0;
fprintf(output,"%s\n",argv[0]);
switch (argc)
{
case (3) :
enableComponents("FBIP");
break;
case (4) :
enableComponents(argv[3]);
break;
case (5) :
if ( strcmp(argv[4], "Y")==0 || strcmp(argv[4], "y")==0 )
{
flagSelects = TRUE;
enableComponents(argv[3]);
}
else
{
enableComponents(argv[3]);
}
break;
default :
fprintf(output,"\n\tNot enough or too many arguments !\n");
fprintf(output,"Usage : %s <form.fmb> <un/pw[@db]> [FBIP] [Y|y]\n\n", argv[0]);
exit(1);
}
strncpy(buf, argv[1], BUFSIZE);
pd2ffmd_name = strtok(buf, ".");
/* Create text file for output */
output_name = calloc(1,strlen(argv[1]));
strcpy(output_name,pd2ffmd_name);
strcat(output_name,".txt");
output = fopen(output_name,"w");
/* Initialize the attribute mask */
d2fctx_attr.mask_d2fctxa = 0;
/* Create the API context */
status = d2fctxcr_Create(&pd2fctx, &d2fctx_attr);
if ( (strcmp(argv[2], "NO")== 0) || (strcmp(argv[2], "no")==0) )
{
fprintf(output,"Connection not attempted.\n");
}
else
{
if( SUCCESS != d2fctxcn_Connect( pd2fctx, argv[ 2 ], ( dvoid * ) NULL ))
{
fprintf(output,"%s: Error connection failed for %s...\n", argv[ 0 ], argv[ 2 ] );
exit(1);
}
}
if ( flagSelects )
{
fprintf(output,"Checking trigger code for the word select (case-insensitive)\n");
}
/* Load the pd2ffmd */
status = d2ffmdld_Load(pd2fctx, &pd2ffmd, pd2ffmd_name, FALSE);
if (status==SUCCESS)
{
/*** Process Form Level Trigger ***/
if (FORM == TRUE)
{
for(status = d2ffmdg_trigger(pd2fctx,pd2ffmd,&pd2ftrg); pd2ftrg != NULL;
status = d2ftrgg_next(pd2fctx,pd2ftrg,&pd2ftrg))
{
if (IsV2Style(pd2fctx,pd2ftrg) == FALSE)
{
d2ffmdg_name(pd2fctx, pd2ffmd, &name);
d2ftrgg_name(pd2fctx, pd2ftrg, &triggerName);
if (d2ftrgis_IsSubclassed(pd2fctx,pd2ftrg) == D2FS_NO)
{
d2ftrgg_trg_txt(pd2fctx, pd2ftrg, &triggerCode);
if ( flagSelects )
{
if ( detectSelect(triggerCode) )
{
fprintf(output,"%s:%s trigger code contains the words \"select\" and \"from\"\n",
name, triggerName);
}
}
else
{
if ( detectSelect(triggerCode) )
{
//Create a program unit
progUnitName = malloc(strlen(name) + strlen(triggerName) + 1);
set_puName(progUnitName, name, triggerName);
createProgramUnit(progUnitName, pd2fctx, pd2ffmd, pd2ftrg, triggerCode);
}
}
//free(progUnitName);
//Done processing form level triggers !
}
else
{
fprintf(output,"Form level trigger %s.%s is subclassed...ignoring.\n",
name, triggerName);
}
}
}
} // FORM == TRUE
// Loop round all blocks
for(status = d2ffmdg_block(pd2fctx,pd2ffmd,&pd2fblk);
pd2fblk != NULL;
status = d2fblkg_next(pd2fctx,pd2fblk,&pd2fblk))
{
d2fblkg_name(pd2fctx, pd2fblk, &name);
fprintf(output,"\n\tProcessing block : %s\n", name);
if (BLOCK == TRUE)
{
/* Process Block Level Trigger */
for(status = d2fblkg_trigger(pd2fctx,pd2fblk,&pd2ftrg); pd2ftrg != NULL;
status = d2ftrgg_next(pd2fctx,pd2ftrg,&pd2ftrg))
{
if (IsV2Style(pd2fctx,pd2ftrg) == FALSE)
{
d2fblkg_name(pd2fctx, pd2fblk, &name);
d2ftrgg_name(pd2fctx, pd2ftrg, &triggerName);
if (!strcmp(triggerName,"POST-QUERY"))
{
if (d2ftrgis_IsSubclassed(pd2fctx,pd2ftrg) == D2FS_NO)
{
d2ftrgg_trg_txt(pd2fctx, pd2ftrg, &triggerCode);
if ( flagSelects )
{
if ( detectSelect(triggerCode) )
{
fprintf(output,"%s:%s trigger code contains the word 'select'\n",
name, triggerName);
}
}
else
{
if ( detectSelect(triggerCode) )
{
// Create a program unit containing the trigger code.
progUnitName = malloc(strlen(name) + strlen(triggerName) + 1);
set_puName(progUnitName, name, triggerName);
createProgramUnit(progUnitName, pd2fctx, pd2ffmd, pd2ftrg, triggerCode);
}
}
//free(progUnitName);
//Done processing form block triggers !
}
else
{
fprintf(output,"\tBlock level trigger %s.%s is subclassed...ignoring.\n",
name, triggerName);
}
}
}
}
} // BLOCK == TRUE
if (ITEM == TRUE)
{
// Loop through all items
for(status = d2fblkg_item(pd2fctx,pd2fblk,&pd2fitm);
pd2fitm != NULL;
status = d2fitmg_next(pd2fctx,pd2fitm,&pd2fitm))
{
d2fitmg_name(pd2fctx, pd2fitm, &name);
fprintf(output,"\t\tProcessing item : %s\n", name);
// Process Item Level Triggers
for(status = d2fitmg_trigger(pd2fctx,pd2fitm,&pd2ftrg);
pd2ftrg != NULL;
status = d2ftrgg_next(pd2fctx,pd2ftrg,&pd2ftrg))
{
if (IsV2Style(pd2fctx,pd2ftrg) == FALSE)
{
d2fitmg_name(pd2fctx, pd2fitm, &name);
d2ftrgg_name(pd2fctx, pd2ftrg, &triggerName);
if (d2ftrgis_IsSubclassed(pd2fctx,pd2ftrg) == D2FS_NO)
{
d2ftrgg_trg_txt(pd2fctx, pd2ftrg, &triggerCode);
if ( flagSelects )
{
if ( detectSelect(triggerCode) )
{
fprintf(output,"%s:%s trigger code contains the word 'select'\n",
name, triggerName);
}
}
else
{
if ( detectSelect(triggerCode) )
{
// Create a program unit containing the trigger code.
progUnitName = malloc(strlen(name) + strlen(triggerName) + 1);
set_puName(progUnitName, name, triggerName);
createProgramUnit(progUnitName, pd2fctx, pd2ffmd, pd2ftrg, triggerCode);
}
}
//free(progUnitName);
//Done processing form block triggers !
}
else
{
fprintf(output,"\t\t\tItem level trigger %s.%s is subclassed...ignoring.\n",
name, triggerName);
}
}
}
}
} // ITEM == TRUE
}
// Finally process property classes...
// Loop round all property classes
if (PROPERTYCLASS == TRUE)
{
for(status = d2ffmdg_prop_class(pd2fctx,pd2ffmd,&pd2fppc);
pd2fppc != NULL;
status = d2fppcg_next(pd2fctx,pd2fppc,&pd2fppc))
{
d2fppcg_name(pd2fctx, pd2fppc, &name);
fprintf(output,"\n\tProcessing property class : %s\n", name);
//loop round all triggers in property class...
for(status = d2fppcg_trigger(pd2fctx,pd2fppc,&pd2ftrg); pd2ftrg != NULL;
status = d2ftrgg_next(pd2fctx,pd2ftrg,&pd2ftrg))
{
if (IsV2Style(pd2fctx,pd2ftrg) == FALSE)
{
d2fppcg_name(pd2fctx, pd2fppc, &name);
d2ftrgg_name(pd2fctx, pd2ftrg, &triggerName);
if (d2ftrgis_IsSubclassed(pd2fctx,pd2ftrg) == D2FS_NO)
{
d2ftrgg_trg_txt(pd2fctx, pd2ftrg, &triggerCode);
if ( flagSelects )
{
if ( detectSelect(triggerCode) )
{
fprintf(output,"%s:%s trigger code contains the word 'select'\n",
name, triggerName);
}
}
else
{
// Create a program unit containing the trigger code.
progUnitName = malloc(strlen(name) + strlen(triggerName) + 1);
set_puName(progUnitName, name, triggerName);
createProgramUnit(progUnitName, pd2fctx, pd2ffmd, pd2ftrg, triggerCode);
}
//free(progUnitName);
//Done processing form block triggers !
}
else
{
fprintf(output,"\t\t\tProperty class trigger %s.%s is subclassed...ignoring.\n",
name, triggerName);
}
}
}
}
} // PROPERTYCLASS == TRUE
// Save out the pd2ffmd
if (status = d2ffmdsv_Save(pd2fctx, pd2ffmd, (text *)0, FALSE) != SUCCESS)
{
fprintf(output,"Unable to save form %s\n", pd2ffmd_name);
exit(1);
}
// Generate the pd2ffmds executable (fmx)
if (status = d2ffmdcf_CompileFile(pd2fctx, pd2ffmd ) != SUCCESS)
{
fprintf(output,"Unable to generate form %s\n", pd2ffmd_name);
exit(1);
}
/* Destroy the API Context */
if (status = d2fctxde_Destroy(pd2fctx) != SUCCESS)
{
fprintf(output,"Unable to destroy context for form %s\n", pd2ffmd_name);
exit(1);
}
fprintf(output,"\nFinished");
fprintf(stderr,"Finished\n");
return 0;
} //status == SUCCESS
else
{
fprintf(output,"Unable to load form\n");
exit(0);
}
}
///////////////////////////////////////////////////////////////////////////////////
int set_puName(char * name, char *sourceObject, char *triggerType)
{
int i;
char tempName[PUNAMEMAXLENGTH-4];
for (i=0; (size_t)i < strlen(triggerType); i++)
{
if (triggerType[i] == '-')
triggerType[i] = '_';
}
strcpy(name, sourceObject);
strcat(name, "_");
strcat(name, triggerType);
if (strlen(name) > PUNAMEMAXLENGTH)
{
name[PUNAMEMAXLENGTH - 4] = '\0';
strcpy(tempName, name);
sprintf(name, "%s%04d\0", tempName, PU_uniqueCounter);
PU_uniqueCounter++;
}
return SUCCESS;
}
////////////////////////////////////////////////////////////////////////////////////
int append_BeginEnd(char *puCodeIn, char *progUnitNameIn, char *triggerCodeIn)
{
strcpy(puCodeIn, "PROCEDURE ");
strcat(puCodeIn, progUnitNameIn);
strcat(puCodeIn, " IS\nBEGIN\n");
strcat(puCodeIn, triggerCodeIn);
strcat(puCodeIn, "\nEND;");
return (SUCCESS);
}
////////////////////////////////////////////////////////////////////////////////////
int createProgramUnit(char *progUnitName,
d2fctx *pd2fctx,
d2ffmd *pd2ffmd,
d2ftrg *pd2ftrg,
char *triggerCode)
{
d2fpgu *pd2fpgu;
int status;
char *puCode;
int i=0, j=0, k=0, l=-1, m=0, n=0, ncur=0, o=0, p=0, found=0, ind=0, cns=0 , co=0;
int lg_puCodeDest = 0;
const int NBCUR = 20;
text *pdest_exe;
char *puCodeDest;
char *puCodeFinal;
char fetchName[100];
char *cursorName[20];
char *cursorFetchList[20];
char *cursorFetchFound[20];
char *cursorCommented[20];
char *cursorSelectList[20];
char *cursorFromClause[20];
fprintf(output,"Generating program unit %s\n", progUnitName);
if (status = d2fpgucr_Create(pd2fctx, pd2ffmd, &pd2fpgu, (text *)progUnitName)!=SUCCESS)
{
fprintf(output,"Unable to create program unit $s\n", progUnitName);
exit(1);
}
// set the type of the PU
if (status=d2fpgusn_SetNumProp(pd2fctx, pd2fpgu, D2FP_PGU_TYP, D2FC_PGTY_PROCEDURE) !=SUCCESS)
{
fprintf(output,"Unable to set PU type for program unit $s\n", progUnitName);
exit(1);
}
//Put the extra stuff around the code...
puCode=(char *)calloc(1,(strlen(progUnitName) + strlen(triggerCode) +30));
memset(puCode, '\0', (strlen(progUnitName) + strlen(triggerCode) +30));
append_BeginEnd(puCode, progUnitName, triggerCode);
puCodeDest=calloc(1,(strlen(progUnitName) + strlen(triggerCode) + 100));
memset(puCodeDest, '\0', (strlen(progUnitName) + strlen(triggerCode) + 100));
puCodeFinal=(char *)calloc(1,(strlen(progUnitName) + strlen(triggerCode) + 100));
memset(puCodeFinal, '\0', (strlen(progUnitName) + strlen(triggerCode) + 100));
// Set the PU text
if (detectCursor(puCode))
{
for (i=0; i<30; i++)
fetchName[i] = '\0';
for (i=0; i<NBCUR; i++)
{
cursorFetchList[i]=calloc(1,2000);
memset(cursorFetchList[i], '\0',2000);
cursorFetchFound[i]=calloc(1,10);
memset(cursorFetchFound[i], '\0',10);
cursorCommented[i]=calloc(1,10);
memset(cursorCommented[i], '\0',10);
cursorSelectList[i]=calloc(1,2000);
memset(cursorSelectList[i], '\0',2000);
cursorFromClause[i]=calloc(1,2000);
memset(cursorFromClause[i], '\0',2000);
cursorName[i]=calloc(1,30);
memset(cursorName[i], '\0',30);
}
pdest_exe = strcasestr( puCode, "CURSOR" );
if (pdest_exe != NULL)
{
for(i=0,j=0;i<(int)(strlen(puCode));i++)
{
if (((!strncasecmp(&puCode[i],"CURSOR ",strlen("CURSOR "))) ||
(!strncasecmp(&puCode[i],"CURSOR\t",strlen("CURSOR\t")))) &&
(strncmp(&puCode[i-1],"_",strlen("_"))))
{
// Checking if cursor is commented
found = 0;
for (co=i-1; co >=0; co--)
{
if ((puCode[co] == '*') && (puCode[co-1] == '/'))
{
found = 1;
break;
}
else if ((puCode[co] != '\n') &&
(puCode[co] != '\t') &&
(puCode[co] != ' '))
{
found = 0;
break;
}
}
l++;
m=0;
i += 7;
for(k=i; k<(int)strlen(puCode); k++)
{
if(!strncasecmp(&puCode[k]," IS",strlen(" IS")))
{
cursorName[l][m]='\0';
if (found == 1)
cursorCommented[l][0]='*';
else
cursorCommented[l][0] = ' ';
i+=3;
break;
}
else
{
if ((puCode[k] != ' ') && (puCode[k] != '\t') )
{
cursorName[l][m++]=puCode[k];
}
i++;
}
}
found=0;
for(k=i; k<(int)strlen(puCode); k++)
{
if((!strncasecmp(&puCode[k],"SELECT ",strlen("SELECT "))) ||
(!strncasecmp(&puCode[k],"SELECT\n",strlen("SELECT\n"))) ||
(!strncasecmp(&puCode[k],"SELECT\t",strlen("SELECT\t"))))
{
for (p=k; p>=0; p--)
{
if (puCode[p]=='\n')
break;
if (!strncasecmp(&puCode[p],"'",1))
{
found=1;
}
}
if (found==0)
{
m=0;
for(n=k; n<(int)strlen(puCode); n++)
{
if((!strncasecmp(&puCode[n],"FROM ",strlen("FROM "))) ||
(!strncasecmp(&puCode[n],"FROM\n",strlen("FROM\n"))) ||
(!strncasecmp(&puCode[n],"FROM\t",strlen("FROM\t"))))
{
cursorSelectList[l][m]='\0';
i+=5;
m=0;
for(o=n; o<(int)strlen(puCode); o++)
{
if(!strncasecmp(&puCode[o],";",1))
{
cursorFromClause[l][m]='\0';
i = o + 1;
break;
}
else
{
cursorFromClause[l][m++]=puCode[o];
}
}
break;
}
else
{
cursorSelectList[l][m++]=puCode[n];
i++;
}
}
break;
}
else
found=0;
}
}
}
}
for(i=0,j=0;i<(int)(strlen(puCode));i++)
{
if ((!strncasecmp(&puCode[i],"FETCH ",strlen("FETCH "))) ||
(!strncasecmp(&puCode[i],"FETCH\n",strlen("FETCH\n"))) ||
(!strncasecmp(&puCode[i],"FETCH\t",strlen("FETCH\t"))))
{
m=0;
i += 6;
for(k=i; k<(int)strlen(puCode); k++)
{
if((!strncasecmp(&puCode[k],"INTO ",strlen("INTO "))) ||
(!strncasecmp(&puCode[k],"INTO\n",strlen("INTO\n"))) ||
(!strncasecmp(&puCode[k],"INTO\t",strlen("INTO\t"))))
{
fetchName[m]='\0';
break;
}
else
{
if (puCode[k] != ' ')
fetchName[m++]=puCode[k];
}
}
found = 0;
for (n=0; n<NBCUR; n++)
{
if (cursorName[n][0] == '\0')
break;
else if (cursorFetchFound[n][0]!='*')
{
if (!strncasecmp(cursorName[n],fetchName,strlen(cursorName[n])))
{
found = 1;
ncur = n;
cursorFetchFound[ncur][0]='*';
break;
}
}
}
if (found == 0)
{
for (n=0; n<NBCUR; n++)
{
if (cursorFetchFound[n][0]=='*')
{
if (!strncasecmp(cursorName[n],fetchName,strlen(cursorName[n])))
{
ncur = n;
break;
}
}
}
}
else
found = 0;
m=0;
for(l=k+5; l<(int)strlen(puCode); l++)
{
if(!strncasecmp(&puCode[l],";",strlen(";")))
{
cursorFetchList[ncur][m]='\0';
break;
}
else
{
cursorFetchList[ncur][m++]=puCode[l];
}
}
}
}
for (p=0;p<NBCUR;p++)
{
cursorFetchFound[p][0]=' ';
}
m=0;
for(i=0,j=0;i<(int)(strlen(puCode));i++)
{
if (((!strncasecmp(&puCode[i],"CURSOR ",strlen("CURSOR "))) ||
(!strncasecmp(&puCode[i],"CURSOR\t",strlen("CURSOR\t")))) &&
(strncmp(&puCode[i-1],"_",strlen("_"))))
{
i += 7;
for (k=i; k<(int)strlen(puCode);k++)
{
if(!strncasecmp(&puCode[k]," IS",strlen(" IS")))
{
i += 3;
fetchName[m]='\0';
m=0;
break;
}
else
if (puCode[k] != ' ' )
fetchName[m++]=puCode[k];
i++;
}
for (k=i; k<(int)strlen(puCode);k++)
{
if( puCode[k] == ';')
{
i +=1;
break;
}
else
i++;
}
}
else if ((!strncasecmp(&puCode[i],"OPEN ",strlen("OPEN "))) ||
(!strncasecmp(&puCode[i],"OPEN\n",strlen("OPEN\n"))) ||
(!strncasecmp(&puCode[i],"OPEN\t",strlen("OPEN\t"))))
{
i += 5;
for (k=i; k<(int)strlen(puCode);k++)
{
if(!strncasecmp(&puCode[k],";",strlen(";")))
{
i +=1;
break;
}
else
i++;
}
}
else if ((!strncasecmp(&puCode[i],"CLOSE ",strlen("CLOSE "))) ||
(!strncasecmp(&puCode[i],"CLOSE\n",strlen("CLOSE\n"))) ||
(!strncasecmp(&puCode[i],"CLOSE\t",strlen("CLOSE\t"))))
{
i += 6;
for (k=i; k<(int)strlen(puCode);k++)
{
if(!strncasecmp(&puCode[k],";",strlen(";")))
{
i +=1;
puCodeDest[j++]='\n';
break;
}
else
i++;
}
}
else if ((!strncasecmp(&puCode[i],"FETCH ",strlen("FETCH "))) ||
(!strncasecmp(&puCode[i],"FETCH\n",strlen("FETCH\n"))) ||
(!strncasecmp(&puCode[i],"FETCH\t",strlen("FETCH\t"))))
{
i += 6;
m=0;
for (k=i; k<(int)strlen(puCode);k++)
{
if((!strncasecmp(&puCode[k],"INTO ",strlen("INTO "))) ||
(!strncasecmp(&puCode[k],"INTO\n",strlen("INTO\n"))) ||
(!strncasecmp(&puCode[k],"INTO\t",strlen("INTO\t"))))
{
fetchName[m]='\0';
m=0;
break;
}
else
if (puCode[k] != ' ')
fetchName[m++]=puCode[k];
}
for (k=i; k<(int)strlen(puCode);k++)
{
if(!strncasecmp(&puCode[k],";",strlen(";")))
{
found = 0;
for (n=0; n<NBCUR; n++)
{
if (cursorName[n][0] == '\0')
break;
else if (cursorFetchFound[n][0]!='*')
{
if (!strncasecmp(cursorName[n],fetchName,strlen(cursorName[n])))
{
found = 1;
ncur = n;
cursorFetchFound[ncur][0]='*';
break;
}
}
}
if (found == 0)
{
for (n=0; n<NBCUR; n++)
{
if (cursorFetchFound[n][0]=='*')
{
if (!strncasecmp(cursorName[n],fetchName,strlen(cursorName[n])))
{
ncur = n;
break;
}
}
}
}
else
found = 0;
// MD - 26/10/2001
if (cursorCommented[ncur][0] == '*')
{
puCodeDest[j++] = '\n';
puCodeDest[j++] = '/';
puCodeDest[j++] = '*';
puCodeDest[j++] = '\n';
}
puCodeDest[j++] = 'B';
puCodeDest[j++] = 'E';
puCodeDest[j++] = 'G';
puCodeDest[j++] = 'I';
puCodeDest[j++] = 'N';
puCodeDest[j++] = '\n';
// MD - 26/10/2001
for (n=0; n<(int)strlen(cursorSelectList[ncur]);n++)
{
puCodeDest[j++]=cursorSelectList[ncur][n];
}
puCodeDest[j++]='I';
puCodeDest[j++]='N';
puCodeDest[j++]='T';
puCodeDest[j++]='O';
puCodeDest[j++]=' ';
for (n=0; n<(int)strlen(cursorFetchList[ncur]);n++)
{
puCodeDest[j++]=cursorFetchList[ncur][n];
}
puCodeDest[j++]='\n';
for (n=0; n<(int)strlen(cursorFromClause[ncur]);n++)
{
puCodeDest[j++]=cursorFromClause[ncur][n];
}
puCodeDest[j++]=';';
puCodeDest[j++]='\n';
// MD - 26/10/2001
strncat(puCodeDest,"EXCEPTION\nWHEN OTHERS THEN null;\n",33);
strncat(puCodeDest,"END;\n",5);
if (cursorCommented[ncur][0] == '*')
{
strncat(puCodeDest,"*/\n",3);
j += 41;
}
else
{
j += 38;
}
break;
}
else
{
i++;
}
}
}
else
{
puCodeDest[j++]=puCode[i];
}
}
for (p=0; p<NBCUR; p++)
{
cursorFetchFound[p][0]='\0';
}
puCodeDest[j] = '\0';
lg_puCodeDest = strlen(puCodeDest);
if ((strcasestr( puCodeDest, "%NOTFOUND" ) != NULL) ||
(strcasestr( puCodeDest, "%FOUND" ) != NULL))
{
for(i=0,ind=-1;i<lg_puCodeDest;i++)
{
if (puCodeDest[i] == '\0')
break;
if ((!strncasecmp(&puCodeDest[i],"%NOTFOUND",strlen("%NOTFOUND"))) ||
(!strncasecmp(&puCodeDest[i],"%FOUND",strlen("%FOUND"))))
{
for (l=i-1,m=0; l>=0; l--)
{
if ((puCodeDest[l] == ' ') || (puCodeDest[l] == '('))
{
for (n=0; n<m-1; n++)
ind--;
if (!strncasecmp(&puCodeDest[i],"%NOTFOUND",strlen("%NOTFOUND")))
{
puCodeFinal[ind++] = 'S';
puCodeFinal[ind++] = 'Q';
puCodeFinal[ind++] = 'L';
puCodeFinal[ind++] = '%';
puCodeFinal[ind++] = 'N';
puCodeFinal[ind++] = 'O';
puCodeFinal[ind++] = 'T';
puCodeFinal[ind++] = 'F';
puCodeFinal[ind++] = 'O';
puCodeFinal[ind++] = 'U';
puCodeFinal[ind++] = 'N';
puCodeFinal[ind] = 'D';
i += strlen("%NOTFOUND") - 1 ;
}
else if (!strncasecmp(&puCodeDest[i],"%FOUND",strlen("%FOUND")))
{
puCodeFinal[ind++] = 'S';
puCodeFinal[ind++] = 'Q';
puCodeFinal[ind++] = 'L';
puCodeFinal[ind++] = '%';
puCodeFinal[ind++] = 'F';
puCodeFinal[ind++] = 'O';
puCodeFinal[ind++] = 'U';
puCodeFinal[ind++] = 'N';
puCodeFinal[ind] = 'D';
i += strlen("%FOUND") - 1 ;
}
break;
}
else
m++;
}
}
else
{
ind++;
puCodeFinal[ind] = puCodeDest[i];
}
}
puCodeFinal[++ind] = '\0';
}
else
strncpy(puCodeFinal,puCodeDest,lg_puCodeDest);
}
/*
free(fetchName);
for (i=0; i<NBCUR; i++)
{
free(cursorFetchList[i]);
free(cursorSelectList[i]);
free(cursorFromClause[i]);
free(cursorName[i]);
}
*/
}
else
strcpy(puCodeFinal,puCode);
d2fpgus_pgu_txt(pd2fctx, pd2fpgu, puCodeFinal);
// Now change the trigger code to call the program unit...
strcpy(triggerCode, progUnitName);
strcat(triggerCode, ";");
d2ftrgs_trg_txt(pd2fctx, pd2ftrg, triggerCode);
free(puCode);
//free(puCodeDest);
free(puCodeFinal);
//free(puCode);
return (SUCCESS);
}
///////////////////////////////////////////////////////////////////////////////////////
int enableComponents(char *componentFlag)
{
if (strchr(componentFlag, 'F') || strchr(componentFlag, 'f'))
FORM = TRUE;
if (strchr(componentFlag, 'B') || strchr(componentFlag, 'b'))
BLOCK = TRUE;
if (strchr(componentFlag, 'I') || strchr(componentFlag, 'i'))
ITEM = TRUE;
if (strchr(componentFlag, 'P') || strchr(componentFlag, 'p'))
PROPERTYCLASS = TRUE;
return(SUCCESS);
}
///////////////////////////////////////////////////////////////////////////////////////
int IsV2Style(d2fctx *pd2fctx,d2ftrg *pd2ftrg)
{
/*
** Trigger Style (D2FP_TRG_STY)
** [TS]
#define D2FC_TRST_PLSQL 0
#define D2FC_TRST_V2 1
*/
unsigned int style;
d2ftrgg_trg_sty(pd2fctx, pd2ftrg, &style);
if (style == D2FC_TRST_PLSQL)
{
return (FALSE); // NO it is NOT a V2 style trigger
}
else
{
fprintf(output,"Found V2 style Trigger, ignoring trigger...\n", style);
return (TRUE);
}
}
/////////////////////////////////////////////////////////////////////
int detectSelect(char *triggerCode)
{
// MD 02-NOV-2001
if ( (strcasestr(triggerCode, "SELECT")!=NULL) &&
(strcasestr(triggerCode, "FROM" )!=NULL) )
{
return (TRUE);
}
else
{
return (FALSE);
}
}
/////////////////////////////////////////////////////////////////////
int detectCursor(char *triggerCode)
{
// MD 02-NOV-2001
if ( (strcasestr(triggerCode, "CURSOR")!=NULL) &&
(strcasestr(triggerCode, "SELECT")!=NULL) &&
(strcasestr(triggerCode, "FROM" )!=NULL) )
{
return (TRUE);
}
else
{
return (FALSE);
}
}
/* With count limit
** ----------------
*/
int strncasecmp (char * a, char * b, int n)
{
char *p =a;
char *q =b;
int diff;
for(p=a, q=b;; p++, q++) {
if (p == a+n)
return 0; /* Match up to n characters */
if (!(*p && *q))
return *p - *q;
diff = tolower(*p) - tolower(*q);
if (diff)
return diff;
}
/*NOTREACHED*/
}
/*
** strcasestr(s1,s2) -- like strstr(s1,s2) but case-insensitive.
*/
char * strcasestr (char * s1, char * s2)
{
char * ptr = s1;
if (!s1 || !s2 || !*s2) return s1;
while (*ptr) {
if (toupper(*ptr) == toupper(*s2)) {
char * cur1 = ptr + 1;
char * cur2 = s2 + 1;
while (*cur1 && *cur2 && toupper(*cur1) == toupper(*cur2)) {
cur1++;
cur2++;
}
if (!*cur2) return ptr;
}
ptr++;
}
return NULL;
}
Posted by Patrick Hamou on 2016:04:19 16:30:11