Hello,
I am using Advanced SQL query element to insert data to Oracle. The query has input parameters that are passed with some functions shown in the screen shot but it does not insert anything to Oracle. I want to see what is the exact sql statement that was passed to Oracle with the parameters values, because there is something wrong we are able to figure out.
The query was working fine, but suddenly it is stopped working. We used the same data to insert from backend, the data has no issues, but when we insert through outsystems it does not work. So if we can see the executed query it can help us determine what is wrong.
I would appreciate your prompt response.
Thanks,
Hi Maitha Khanji,
Could you please tell me what error you are getting? So, we can help you.
Parveen
Hi Praveen,
We are not getting any error. That's why we don't have any clue what is wrong.
Regards,
Maitha
Hi,
Is there any possibility of sending an OML? So I can understand much.
Also, have you tried to pass values in test Inputs?
I've attached the OML. Yes I tested the SQL query with test inputs, it returns no errors.
Hi Maitha,
Please note that the "Test SQL" won't work for UPDATE, INSERT, DELETE Query, it will not "manipulate" the data, we can only use "Test SQL" against SELECT to see top returned values. To test these UPDATE, INSERT, DELETE statements, you should create a Test UI screen and call the Action that contain this Advance Query. One more note, The Advanced SQL that manipulating the Data using (UPDATE, INSERT, DELETE) should be put inside the same module that you define the Entity, if you put in other module, that module may not have permission to manipulate the Data that it not owned.
Have you checked the service center logs? or checked through Debug?
yes there are no error logs in service center
Did you check, the length of the data you inserted and the length of your attribute datatype?
there is no issue in the length of the data, the length constraints of the text (varchar) columns in oracle are enough. If i can see the executed sql query it will help me debug and troubleshoot the issue properly.
Hello Maitha,To check the executed SQL, this documentation will help you. Apart from that, if you want to see the input values, just add log message server action and pass all the value over there, whatever you want to see( and debug is also option)Thanks and Regards,Akshay Deshpande
Hi Akshay,
This can be used by DBAs, but what i want is to check the sql statement that was executed in Service Studio while debugging for example or in Service Center logs. Similar to REST APIs for example.
Thanks and regards,
Is there a reason why you are using custom SQL and not the Create action?
because outsystems insert the date 1/1/1900 if i don't specify values for dates fields, but in the advanced query i can insert oracle "NULL" values
Hi Maitha
On the fields start and end date you are converting from text to date type, but on the planPeriod and budgetYear it looks that you are using date functions on a text field. The source of all those 4 fields are the same.
Regards
I removed TextToDate for the date fields but it is still not inserting. Using the "Create" action inserts records but it adds 1/1/1900 dates for empty date fields.
Kindly advise.
if i am using Service Studio debugger, does the query get committed to the data after the breakpoint, or it is committed when the whole action is completed ?
what I would do (try) is leave as show, but on the planPeriod and budgetYear first convert into a date type and then next retrieve the values that you want, that way you are sure that field is a date type field and not text type.
The format of the planPeriod is month/year/day ?
Is there any log file to look into? Server/DB log ?
the PLAN_PERIOD and BUDGET_YEARin the oracle table have varchar2 data type .
the plan period should be month/year (example "7/2023" )
I am reading these values from selectedWellMonList.Current.StartDate which has "Date Time" type.
so you must convert from date type to date type and not what you are doing text to date
yes i already mentioned in previous reply the i removed the "TextToDate"
Okay sorry for that
on other thing, on the planPeriod and budgetYear I would do like this
IntegerToText(month(field)) + "/" + Integer_to_Text(year(field))
Best regads
I did, but it is still the same. something strange in the advanced query, sometimes it works sometimes not, but the built-in "Create" action always works.
I created a "before insert trigger" on the oracle table, but now we are getting oracle errors, because we are the actual table is on a different schema that is reading from a synonym through different schema that the schema we are using is reading from through synonym. Schema1==Synonym==>Schema2 ===Synonym===>Schema3 (this is the user we are reading from).
I hope that someone can advise what's wrong with the advanced sql query. I suggest adding a feature for logging the exact sql statement being passed from outsystems to oracle to be able to debug, because this way it is like black box we don't know what is happening.
from this side it looks that the problem maybe on the authorization side.
To insert you will have to have the right permissions to do that.
I used the built-in Create action and i created a "before insert trigger" to remove the 1900 dates but now it is causing other issues, it is setting any long integer value to 0! which is very wrong. how to solve this issue ?