You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Regarding Errror -"SQL.Execute: SQL object is not open, or is a open on a select."

1 reply [Last post]
RPL's picture
RPL
User offline. Last seen 11 years 46 weeks ago. Offline
Joined: 11/25/2008
Posts: 2

Hi,
I am trying to load a set of data files using Application Engine and CI.
I was successfully able to load 1 file with 500 rows and without any issues.
but while loading the 2nd file I encounterd an error:
"SQL.Execute: SQL object is not open, or is a open on a select. (2,295) AF_ACCT_TRAN.MAIN.GBL.default.1900-01-01.Step01.OnExecute PCPC:5107 Statement:71"

Can someone please help?

Following is my code:

/* ===>
This is a dynamically generated PeopleCode template to be used only as a helper
to the application developer.
You need to replace all references to '[*]' OR default values with  references to
PeopleCode variables and/or a Rec.Fields. */


Local File &fileLog;
Local ApiObject &oSession, &oAfAcctTransCi;
Local ApiObject &oAfAccountCrfCollection, &oAfAccountCrf;


Local File &INPUTFILE;
Local string &Filepath;
Local number &line_num;

Local Rowset &rsInput;
Local Record &rOutput;
Local SQL &SQL1;



Function errorHandler()
   Local ApiObject &oPSMessageCollection, &oPSMessage;
   Local number &i;
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
   
   &oPSMessageCollection = &oSession.PSMessages;
   For &i = 1 To &oPSMessageCollection.Count
      &oPSMessage = &oPSMessageCollection.Item(&i);
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
      &sErrMsgNum = &oPSMessage.MessageNumber;
      &sErrMsgText = &oPSMessage.Text;
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;
   rem ***** Delete the Messages from the collection *****;
   &oPSMessageCollection.DeleteAll();
End-Function;

try
   rem ***** Set the Log File *****;
   &fileLog = GetFile("\u099\E1_ETL_ITF_FILES\AF_ACCT_TRANS_CI.log", "w", "a", %FilePath_Absolute);
   rem &fileLog.WriteLine("Begin");
   rem ***** Get current PeopleSoft Session *****;
   &oSession = %Session;
   
   rem ***** Set the PeopleSoft Session Error Message Mode *****;
   rem ***** 0 - None *****;
   rem ***** 1 - PSMessage Collection only (default) *****;
   rem ***** 2 - Message Box only *****;
   rem ***** 3 - Both collection and message box *****;
   &oSession.PSMessagesMode = 1;
   
   /* Create an instance of the Records */
   &rOutput = CreateRecord(Record.AF_ACCT_TR_REC);
   &rsInput = CreateRowset(Record.AF_ACCT_TR_REC);
   rem &fileLog.WriteLine("before set keys");
   
   
   /* Create an instance of the SQL statement for insert */
   
   &SQL1 = CreateSQL("%Insert(:1)");
   
   &INPUTFILE = GetFile("\u099\E1_ETL_ITF_FILES\testfile.csv", "R", "A", %FilePath_Absolute);
   rem &fileLog.WriteLine("try3");
   
   If &INPUTFILE.IsOpen Then
      If &INPUTFILE.SetFileLayout(FileLayout.AF_ACT_TRANS_FL) Then
         
         &line_num = 0;
         
         /* read in first line to the recordset */
         &rsInput = &INPUTFILE.ReadRowset();
         
         
         While &rsInput <> Null
           
           
           
            rem ***** Get the Component Interface *****;
            &oAfAcctTransCi = &oSession.GetCompIntfc(CompIntfc.AF_ACCT_TRANS_CI);
            If &oAfAcctTransCi = Null Then
               errorHandler();
               throw CreateException(0, 0, "GetCompIntfc failed");
            End-If;
           
            rem ***** Set the Component Interface Mode *****;
            &oAfAcctTransCi.InteractiveMode = True;
            &oAfAcctTransCi.GetHistoryItems = True;
            &oAfAcctTransCi.EditHistoryItems = True;
           
            &line_num = &line_num + 1;
           
           
            &fileLog.WriteLine("&line_num" | &line_num);
           
            /* copy values from file to record */
            &rsInput(1).GetRecord(1).CopyFieldsTo(&rOutput);
           
           
            rem &fileLog.WriteLine("try1");
            rem ***** Set Component Interface Get/Create Keys *****;
            &oAfAcctTransCi.AF_ACCOUNT_OLD = &rOutput.AF_ACCOUNT_OLD.Value;
            rem   &fileLog.WriteLine("AF_ACCOUNT_OLD" | &rsInput.AF_ACCOUNT_OLD.Value);
            &oAfAcctTransCi.AF_TEMPLATE_CD = "JGE";
            rem   &fileLog.WriteLine("AF_TEMPLATE_CD" | &rsInput.AF_TEMPLATE_CD.Value);
            &oAfAcctTransCi.AF_BUSINESS_UNIT = &rOutput.AF_BUSINESS_UNIT.Value;
            rem   &fileLog.WriteLine("AF_BUSINESS_UNIT" | &rsInput.AF_BUSINESS_UNIT.Value);
           
            rem &fileLog.WriteLine("after set keys");
            rem ***** Execute Get *****;
            /*     If Not &oAfAcctTransCi.Get() Then
               rem ***** No rows exist for the specified keys.*****;
               errorHandler();
               throw CreateException(0, 0, "Get failed");
            End-If;
          */
            rem &fileLog.WriteLine("before create keys");
            rem ***** Execute Create ******;
            If Not &oAfAcctTransCi.Create() Then;
               
               rem ***** Unable to Create Component Interface for the Add keys provided. *****;
               errorHandler();
               throw CreateException(0, 0, "Create failed");
            End-If;
           
            rem  &fileLog.WriteLine("after create keys");
           
            rem  &fileLog.WriteLine("try2");
           
           
            rem ***** Begin: Get/Set Component Interface Properties *****;
            rem ***** Get/Set Level 0 Field Properties *****;
            &oAfAcctTransCi.AF_ACCOUNT_OLD = &rOutput.AF_ACCOUNT_OLD.Value;
            &fileLog.WriteLine("AF_ACCOUNT_OLD = " | &rOutput.AF_ACCOUNT_OLD.Value);
            &oAfAcctTransCi.AF_TEMPLATE_CD = "JGE";
            rem &fileLog.WriteLine("AF_TEMPLATE_CD = " | &rOutput.AF_TEMPLATE_CD.Value);
            &oAfAcctTransCi.AF_BUSINESS_UNIT = &rOutput.AF_BUSINESS_UNIT.Value;
            rem &fileLog.WriteLine("AF_BUSINESS_UNIT = " | &rOutput.AF_BUSINESS_UNIT.Value);
           
            rem ***** Set/Get AF_ACCOUNT_CRF Collection Field Properties -- Parent: PS_ROOT Collection *****;
            &oAfAccountCrfCollection = &oAfAcctTransCi.AF_ACCOUNT_CRF;
           
            Local integer &i113;
            For &i113 = 1 To &oAfAccountCrfCollection.Count;
               rem &fileLog.WriteLine("&i113= " | &i113);
               &oAfAccountCrf = &oAfAccountCrfCollection.Item(1);
               
               
               
               &oAfAccountCrf.EFFDT = %Date;
               rem &fileLog.WriteLine("EFFDT = " | &rOutput.EFFDT.Value);
               
               
               
               
               &oAfAccountCrf.ACCOUNT = &rOutput.ACCOUNT.Value;
               &fileLog.WriteLine("ACCOUNT = " | &rOutput.ACCOUNT.Value);
               
               
               &oAfAccountCrf.EFF_STATUS = "A";
               rem &fileLog.WriteLine("EFF_STATUS = " | &rOutput.EFF_STATUS.Value);
               
               
               &oAfAccountCrf.PRODUCT = &rOutput.PRODUCT.Value;
               &fileLog.WriteLine("PRODUCT = " | &rOutput.PRODUCT.Value);
               
               
               &oAfAccountCrf.CHARTFIELD2 = &rOutput.CHARTFIELD2.Value;
               rem &fileLog.WriteLine("CHARTFIELD2 = " | &rOutput.CHARTFIELD2.Value);
               
               
               &oAfAccountCrf.FUND_CODE = &rOutput.FUND_CODE.Value;
               rem &fileLog.WriteLine("FUND_CODE = " | &rOutput.FUND_CODE.Value);
               
               
               &oAfAccountCrf.CHARTFIELD1 = &rOutput.CHARTFIELD1.Value;
               rem &fileLog.WriteLine("CHARTFIELD1 = " | &rOutput.CHARTFIELD1.Value);
               
               
               &oAfAccountCrf.AFFILIATE = &rOutput.AFFILIATE.Value;
               rem &fileLog.WriteLine("AFFILIATE = " | &rOutput.AFFILIATE.Value);
               
               
               &oAfAccountCrf.LEDGER_GROUP = &rOutput.LEDGER_GROUP.Value;
               rem &fileLog.WriteLine("LEDGER_GROUP = " | &rOutput.LEDGER_GROUP.Value);
               
               
               &oAfAccountCrf.DEPTID = &rOutput.DEPTID.Value;
               &fileLog.WriteLine("DEPTID = " | &rOutput.DEPTID.Value);
               
               
               &oAfAccountCrf.PROGRAM_CODE = &rOutput.PROGRAM_CODE.Value;
               rem &fileLog.WriteLine("PROGRAM_CODE = " | &rOutput.PROGRAM_CODE.Value);
               
               
               &oAfAccountCrf.CLASS_FLD = &rOutput.CLASS_FLD.Value;
               &fileLog.WriteLine("CLASS_FLD = " | &rOutput.CLASS_FLD.Value);
            End-For;
           
            &SQL1.Execute(&rOutput);
           
            &rsInput = &INPUTFILE.ReadRowset();
           
           
            rem ***** Execute Save *****;
            If Not &oAfAcctTransCi.Save() Then;
               errorHandler();
               throw CreateException(0, 0, "Save failed");
            End-If;
           
           
            &fileLog.WriteLine("saved");
            /*
            rem ***** Execute Cancel *****;
            If Not &oAfAcctTransCi.Cancel() Then;
               errorHandler();
               throw CreateException(0, 0, "Cancel failed");
            End-If;
            */
           
           
           
         End-While;
         
         
      End-If;
   End-If;
   
   &INPUTFILE.Close();
   
catch Exception &ex
   rem Handle the exception;
   &fileLog.WriteLine(&ex.ToString());
end-try;


rem &fileLog.WriteLine("End");
&fileLog.Close();

Nitin's picture
User offline. Last seen 5 years 25 weeks ago. Offline
Joined: 06/30/2008
Posts: 73
Re: Regarding Errror -"SQL.Execute: SQL object is not open, ...

Hi,
I am assuming that your are doing a looping activity and in loop updating/inserting data using different file but same code.
So here is the problem.. the SQL is not closed after completion of first loop.
Close the sql as you are closing the file.

Thanks
Nitin