Developing and testing 4GL queries in Cloud
Hi,
I wanted to ask about the possibility of creating and testing SQL(4GL) queries in Cloud Infor LN. I know, thah in OnPremise version of Infor LN was tool called Easy SQL, but I know that it is missing in cloud. I don't know how to prepare query for a script or report, check it in editor such as OnPremise Easy SQL - what data they return, what they look like, what type fields are, whether the tables are joined correctly and quickly fix it.
I understand that we don't have access directly to database because it's now multitenant, but I think that this is not an argument for not having such functionality. We can add extensions to sessions, tables, create reports and other objects that use such queries. We even have access to LN Studio, where we have access to various aspects of creating new functionalities. It's not about creating tables, indexes, fields, but about querying data.
How can I develop/test queries in a cloud environment?
I would like to be able to use this prepared query in a script, assign values to variables and use it in the 4GL script.
Comments
-
hello @Artur,
Until the moment I not found any solution to query data in the database in the same way as on-premises.
Only solution I found for LN Cloud is to use the Data Fabric application, but it is not the same.
Looking in Data Fabric - Infor Developer Community
0 -
Maybe helps you:
0 -
Hello Fabiano, I've already had this idea, but I try to find better. This method is not convenient because the table names are different, language is different and I have no guarantee that all tables and fields are loaded into the data lake, so you need to prepare loading to data lake.
In data fabric you have query pplatform like T-SQL MS SQL Server and in 4GL you can't use this functions and statements, so you cannot process set of data to have some result and then use this query in 4GL script.
Do you know if I can use query from data fabric in 4gl script without huge changes? What part of language is common and will work in 4GL?
1 -
@Artur , I dont know how can you do this, never see some solution like that. :(
0 -
Actually Old style SQL queries are supposed to be in some way substituted by Generated Report + report extensions on the report you generate on the main table. It is not the same but you can play with that.
Otherwise you need to create a webservice where you. Accept a query from an outside app and you return data based on the query you've request but in that case you need to be careful on how you fetch a certain amount of record to be sure you are not publishing too much data in a single response otherwise you risk to break the quota. This is not so easybut can give good results.
Hope this could help
1 -
Hi Artur,
You can create a tx session with an input field where you type your query, then a "process" button to parse and execute the query using sql.* 4GL functions and put the result in a downloadable csv file.
A bit of developpment, but it works.
1 -
@Litwick , good idea!
He can use the LN extensibility to do that.
0 -
Could you give me a tip how to read text from field and execute it as query to LN. I am also wondering about that how to declare variables for example some array for results and how to inject this to scipt so 4gl will know to which variable (cell in array) it save value and then to row in the file.
0 -
Hi @Fabiano Silva, I use DF now and it helps a lot. Of course there are inconvinients but this get some orientation about data. Compass in web has limitation to 100 rows. Infor wrote me that they will increase it, but for modeling is ok, only for some more dificult validation it is te limit (but also Postman could be used).
0 -
@Artur , do you tried to use Dbeaver ? There arent no limitation to 100 rows.
0 -
@Fabiano Silva do you mean Community or PRO version. How you connect to datalake, JDBC or dedicated driver/vendor ?
0 -
@Artur ,
1 - Download the Compass JDBC Driver
2 - Create a credential (type backend service)
3 - Download the credentials
4 - Move the JDBC driver and Credentials to the same folder.
5 - Download the DBeaver community and install
6 - Configuring Driver manager
Fill fields (Class name is a case sensitive)
Add file .jar and folder (ref. step 4)
7 - Create a new Connection
8 - Create a New SQL query
I hope help you!
1
