Solved

IAL Data Source Giving SQL Ended Error


Userlevel 2
Badge +6

 

I am trying to connect an IAL definition to a Data Source Designer Element, but when I click the preview button I get an “SQL command not properly ended”.  Here is a screen shot of my IAL Object Developer, below that is a screen shot of my report designer and below that is a screen shot of the error I am getting.

IAL Object Developer
Data Source Designer

 

icon

Best answer by Charith Epitawatta 28 April 2021, 21:15

View original

This topic has been closed for comments

15 replies

Userlevel 7
Badge +31

Hi @rvanden5,

What is your version of IFS and the UPD level? 

Please check the following system parameter and verify that your IAL Owner is in fact IFSINFO.

 

Userlevel 2
Badge +6

Hi Charith,

I am using IFS10, but not sure what the update level is.  Should be the latest since we have just acquired it (hence the learning curve).  I have checked the system parameter that you mentioned and it is not IFSINFO, but when I changed the view to what it is (BELL1INFO)I am still getting the same error.  

Userlevel 7
Badge +31

Hi @rvanden5 

Hi Charith,

I am using IFS10, but not sure what the update level is.  Should be the latest since we have just acquired it (hence the learning curve).  I have checked the system parameter that you mentioned and it is not IFSINFO, but when I changed the view to what it is (BELL1INFO)I am still getting the same error.  

 

It looks like this is a bug. I just quickly created a simple IAL and a datasource in one of our internal environments and I got no errors. There was a similar bug in IFSAPP9 where we got this exact same error when an IAL view is used in a lobby datasource when the IAL Owner is not IFSINFO. 

IFSINFO is the default value given for IAL Owner but on occasion, project teams give a different one such as BELL1INFO in your case. 

To verify what is happening, you can do following.

  1. In IEE while still in Lobby datasource designer window, go to Tools->Options and open the Debug Console. 
  2. Clear the Debug Console if there are any entries and click on preview button in lobby data source window. 
  3. In Debug Console, you would be able to see the select statement that goes through in the “Info” tab. See if there is something odd about this. 

Following is the IAL object and datasource I used. You can create the same IAL and try the above 3 steps with this. Let me know the SQL you get.

 

Userlevel 2
Badge +6

Charith,

Ok, I have created the same IAL as you suggested and used the debugger (for those who don’t see tools, Ctrl+Shift+D will get you there).  Here is what I got for the SQL statement in the debugger:

select * from &AO.bell1app.ronv_test FETCH NEXT 40 ROWS ONLY

and still got the ‘not properly ended’ error in the DS Designer.

Just to be thorough I did the following selects (got the same error for all):

select order_no from ifsapp.customer_order

select order_no from &AO.customer_order

select order_no from Bell1App.customer_order

 

Ron

Userlevel 7
Badge +31

Hi @rvanden5,

Thank you for sharing the output. And yes, this is definitely a bug(The bug ID for IFSAPP9 is 127883). This has been fixed in IFSAPP9 but somehow the fix has not been introduced into IFSAPP10.

The correct SQL should be similar to below:

select * from BELL1INFO.RONV_TEST FETCH NEXT 40 ROWS ONLY

But in your case the SQL is below:

select * from &AO.bell1app.ronv_test FETCH NEXT 40 ROWS ONLY

This has an ‘&AO’ prefix to view name, which refers to the Application Owner(bell1app in your case) and then again a ‘bell1app’ prefix. This is clearly not correct. 

Please open a case with IFS and report this. You may include the link to this post as well in the case. 

Hope this answers your question!

 

 


 

Userlevel 2
Badge +6

Yes, thank you for the help.  I felt like I was running in circles for this.

Ron

Userlevel 2
Badge +6

Charith,

I just realized something, I was using bell1app rather than bell1info.  When I put Bell1info into the view, the sql statement in the debugger looks like this:

select * from bell1info.ronv_test FETCH NEXT 40 ROWS ONLY

which looks correct from you answer, but I am getting this error:

Instead of the other one.

 

Thanks,

RonV

Userlevel 7
Badge +31

Hi @rvanden5,

Not quite clear where you have put BELL1INFO. Could you share screenshots of your IAL Object Developer and the Lobby Datasource configuration?

Thanks!

Userlevel 2
Badge +6

I sure can.  So, the bell1info would go in the view area of the data source designer:  This is what I have for the IAL and the DS Designer and when I click on the preview I get the error I pasted before.  Before instead of Bell1info I was using ifsinfo or bell1app.

Data Source Designer

 

IAL

 

Userlevel 7
Badge +31

Hi @rvanden5 

Have you deployed the IAL object? RMB on IAL Object Developer and deploy it. Since the error is saying the table or view does not exist, I am assuming you have saved the IAL but not deployed it. 

After that see whether you get data.

Userlevel 2
Badge +6

I did not.  Missed that step completely.  When I do I get the following:

when I click ok I get:

 

Userlevel 7
Badge +31

I did not.  Missed that step completely.  When I do I get the following:

when I click ok I get:

 

 

Hi @rvanden5 

I think you need to prefix the view name with App Owner. In your case, I assume it is bell1app. 

So it should be 

select Order_no from bell1app.customer_order

 

Userlevel 2
Badge +6

I see said the blind man as he picked up his hammer and saw.  OK, that is what I was doing wrong.  I did not deploy the IAL and I was using the wrong prefixes.  I am now getting data in my preview.  I did say I was new to IFS correct?  Thank you very much for the help and I hope this interaction will help others.

Thanks,

RonV

Userlevel 7
Badge +31

Hi @rvanden5

Okay glad to know your issue was resolved! :) Also glad that the APP9 issue is not present in APP10. It was the exact same error we have seen in APP9 though, which is why I thought it was a bug at first. 

Hope you're having fun with IFS Apps! 

Userlevel 2
Badge +6

Well it is fun discovering things.  As well as frustrating when the discovery eludes.  Have a good day.

Thanks,

RonV