Query splitting issue for escaped single quotes in Execute Sql Script

[oferdan]

Hi,

there is issue with splitting of SQL in KW: Execute Sql Script

Robot code:

DatabaseLibrary.Execute Sql Script    ${EXECDIR}\\sample_query.sql

sample_query.sql:

Insert into HS_SDN (ENT_NUM,SDN_NAME,SDN_TYPE,PROGRAM,TITLE,CALL_SIGN,VESS_TYPE,TONNAGE,GRT,VESS_FLAG,VESS_OWNER,REMARKS,ORIGIN,OPERATOR,MOD_DATE,STATUS,OLD_STATUS,MARK,CONFORMS,RAWTEXT,COMMENTS,REFERENCE,ADDINFO1,ADDINFO2,ADDINFO3,ADDINFO4,ADDINFO5,ADDINFO6,ADDINFO7,ADDINFO8,ADDINFO9,ADDINFO10,CATEGORY,SUBCATEGORY,POSITION,AGE,PLACEOFBIRTH,DECEASED,AGEDATE,SSN,DOB,HS_DOB) values (91114250,'''ABD AL-SALAM,''ABD AL-MALIK MUHAMMAD YUSUF ''UTHMAN',null,null,null,null,null,null,null,null,null,null,'DIFFMATCHTYPES','HOTADMIN',to_date('09-APR-20','DD-MON-RR'),'NEW',null,null,null,null,null,'DIFFMATCHTYPES-FACOBATA',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

Insert into HS_ADD (PK_COUNTER,ADD_NUM,ENT_NUM,ADDRESS,CITY,COUNTRY,ADD_REMARKS,ORIGIN,OPERATOR,MOD_DATE,STATUS,OLD_STATUS,MARK,CONFORMS,RAWTEXT,REFERENCE,ADD_REFERENCE) values (0,91120729,91114250,null,'DUBAI','UNITED ARAB EMIRATES',null,'DIFFMATCHTYPES','HOTADMIN',to_date('09-APR-20','DD-MON-RR'),'EXISTING',null,null,null,null,'DIFFMATCHTYPES-A A TRADING FZCO','DIFFMATCHTYPES-A A TRADING FZCO-');

Insert into HS_ALLPROGRAMS (PROGRAM,ORIGIN) values ('DIFFMATCHTYPES','DIFFMATCHTYPES');

Insert into HS_ASSOCIATEDSDN (ENT_NUM,PROGRAM) values (91114250,'DIFFMATCHTYPES');

Insert into HSDICTIONARY (ENTRYSEQNO,ENT_NUM,VERSION,SDN_TYPE,NAME,ADDRESS1,CITY,COUNTRY,LASTUPDATE,UPDATEBY,COMMENTS,ADDINFO1,ADDINFO2,ADDINFO3,ORIGIN,ALT_NUM,ADD_NUM,REMARKS,ADDINFO4,ADDINFO5,ADDINFO6,ADDINFO7,ADDINFO8,ADDINFO9,ADDINFO10,TITLE,CATEGORY,SUBCATEGORY) values ((SELECT COALESCE(MAX(ENTRYSEQNO), 0) from HSDICTIONARY) +1,91114250,611,null,'''ABD AL-SALAM,''ABD AL-MALIK MUHAMMAD YUSUF ''UTHMAN',null,null,null,'09-04-2020 19:18:54','HOTADMIN',null,null,null,null,'DIFFMATCHTYPES',0,0,null,null,null,null,null,null,null,null,null,null,null);

Problem:

• it is not splitting first 2 queries properly - even there is a ; in the end - it is combining them into one single query throwing: ORA-00933: SQL command not properly ended
• Issue is with insertion where using escaped single quote: '''ABD AL-SALAM,''ABD AL-MALIK MUHAMMAD YUSUF ''UTHMAN'
• • aka leading' + escaped ''ABD AL-SALAM,''ABD AL-MALIK MUHAMMAD YUSUF ''UTHMAN + trailing '
• Expected inserted string is: 'ABD AL-SALAM,'ABD AL-MALIK MUHAMMAD YUSUF 'UTHMAN

Note:

• it is working fine in SQL Developer and old DatabaseLibrary version 1.2.4

[oferdan]

Same problem with different order of inserts

but if I replace

'''ABD AL-SALAM,''ABD AL-MALIK MUHAMMAD YUSUF ''UTHMAN'

with just

'test'

it is parsed and inserted properly.

But what is confusing is why is it not working with split=False? for original and for modified query with 'test' only

Edit: Oracle's cursor.execute() (via drivers like cx_Oracle or oracledb) does not support executing multiple SQL statements in a single call.

[oferdan]

FYI I tried using sqlparse instead as advised by Copilot 🙃 and it works fine.
You have an issue for sqlparse here already #205
I'll try to look into original parser code.

[amochin]

Hi @oferdan and thanks for submitting the issue!
I think I found the error in the code line 349 of query.py
Could you please try just to delete or comment out this single line and run your tests again?

[oferdan]

hi @amochin I've commented code line 349 of query.py and it is working now for all my data examples.

[amochin]

Thanks for the feedback! The version 2.1.4 just released includes the fix.