4.8 Write SQL Queries

4.8 Write SQL Queries

  
Hi, I am getting a warning when trying to complete the reports.  When I deploy the application the report tables both appear empty.  I have attached an image with the warning I am recieving.  It seems to suggest that have not linked the table to a data source, but to me it looks as if I have.

I'm not really sure how the attach file functionality works and so I can not attach a file.
See images from IDE here:
http://oi39.tinypic.com/2075fg2.jpg
http://oi42.tinypic.com/oavrzt.jpg

Hi James,
Welcome to the community!

You have defined the binding between the widget TableRecords and the query that gets the data from the database but you didn't define what information, what structure, you have on your widget. You need to define the columns of the  TableRecords and the easiest way to do that is to drag and drop the entity or, in this case, the structure that you defined for the output of the query on to the widget.
Thanks Andre.  That worked.  I had assumed that the table model would have been able to detect the form from the output structure of the query.

hi!

if the field names in de video of the structure don't match the sql field names, how does it work?

thanks.
Hi Ana,

I'm sure what you mean, but let me give you a couple of comments that might help you

The structure attributes names are irrelevant. The important thing is that the output order and data types match the structure you define. So if you have a structure with 2 attributes 'Att1' (Text) and 'Att2' (Integer) then your SQL must output also two columns with data types that match that definition.

If you have changed any attribute name in the entities it may happen that the SQL syntax we provide as resource for this video doesn't match your definition and you will get an error. To solve this you must find in the SQL statemente the attribute and you must fix it with the proper label. Remember that the syntax uses curly brackets '{}' to identifiy an entity and square brackets '[]' to identify the attribute, e.g. {Product}.[Name].

I hope this helps.
I want to select records from a table based on a user selection in a combo box.

How do I get the user selection into the SQL conditions? I have the combo box bound to a local variable, but I cannot see that variable when I try and build the query. In fact, I cannot even see an input variable in the Screen Action I'm using to build the SQL.
Hi Phil,

Simple and Advanced Queries have local scope, you'll only see the input parameters of those queries. Hence you have to create an input parameter and bind it to the local variable you have for the combo box.

By the way, can you detail why you are using an advanced query instead of a simple one?

Cheers
Thanks Andre for you quick reply.

I didn't realize that this was a 2-step bind: user data > screen action > SQL call.

This is a simple SQL call, although I am using advanced calls for things like count distinct. Is there somewhere I can go to see all advanced SQL syntax?
For clarity's sake, after I sent the above reply I figured out it's just one step, as the SQL input parm can see the user's entry.

Thanks again.

Hello,

Is it possible for me to download the old version of the lesson material for this video. I'm training with the 8x version of Service Studio, seeing the old videos and i'm having some problems writing the querys by myself.

Thank you.
Hi Carlos,

Sure thing. See the attached file that includes all the previous resources used throught the online training.
Really good tutorials, thanks.

I misnamed the SubmitDate on the Order in a previous step and decided to rename it to its propername. Now I am getting the following warning as I suppose the system added a new column for the newly named SumbitDate but did not remove the old one.

Is there a way to fix this?
What would have been the correct way to fix a mistake such as this?

Database Integrity Suggestion
 
Inconsistent database table and entity definitions: column 'OSUSR_l2m_Order.SUBMIDATE' exists in database, but there is no corresponding attribute in entity 'Order'.
Hi Keith,

OutSystems Platform does not remove the collumn so that you don't loose your data in the database. What normally happens (for on premise installations) is that you go to the database (or request your DBA to do so) and remove the column and maybe move the data to the newly created column. What you could have done differently would be to create a new attribute with the proper name leaving the old one there a creating some logic to go thru all the records and copying the data from one column to the other, but even if you do this and afterwards delete the attribute with the wrong name you would still get the warning (at least for now, this is a matter that will get some attention from the team in the mean time for sure...)

Cheers
Ok. I'll ignore it. Could I actually trick the system by writing a DML statement is a SQL statement and 'testing' it to DROP the column? Would that work?

André Vieira wrote:
Hi Keith,

OutSystems Platform does not remove the collumn so that you don't loose your data in the database. What normally happens (for on premise installations) is that you go to the database (or request your DBA to do so) and remove the column and maybe move the data to the newly created column. What you could have done differently would be to create a new attribute with the proper name leaving the old one there a creating some logic to go thru all the records and copying the data from one column to the other, but even if you do this and afterwards delete the attribute with the wrong name you would still get the warning (at least for now, this is a matter that will get some attention from the team in the mean time for sure...)

Cheers
 
 
Nope.
Hi, 

I`m trying to connect to a database before write my own SQL.But I couldn`t create the new database connection? Please help look into it, thank you.
 
Hi,

When I test my own SQL, I get error "outsystems.project doesn`t exist".
How to solve this problem? Is this becase database connection issue?


Hi,

When I create a connetion to a database, I need to filled in the schma.I don`t know what I should fill in. I can connect to the database without it.
Hi eric,

It seems you do not have permissions to create the database connection. Have you overcomed this problem?

After you import the tables from your database you can use them in the development environment just like you would use any other entity you define there. Check this topic, it seems to do what you want. I hope this helps you!

Cheers
Hello 

i Created SQL Query in my application Specialy in Preapation,in this query i calculated the SUM(Total) as you made at this vedeo but i don't want to use the function Group BY because i want to repeat and display the total of "Piece de Rechange" of each 'ENGIN' Like this

Example:
AUDI1    24/05/2015   Total_Piec 1000§
AUDI1    25/05/2015   Total_Piec  200§

 
Ilham,

I'm sorry but I don't understand your quesion. Can you give a little bit more context...
André Vieira wrote:
Ilham,

I'm sorry but I don't understand your quesion. Can you give a little bit more context...
 Hi Andre

as you'll see in this attachement i want to calculate the total of this total price and to put it on this table records,,so i folowed the all of the steps as you made in this vedeo and create this advanced query :
SELECT
 {Engins}.[Marque], sum({Entretien_Item_VC}.[Prix_Total])
FROM
    {Entretien_Item_VC}
    inner join {Entretien_Voiture_Camion} on {Entretien_Voiture_Camion}.[Id] = {Entretien_Item_VC}.[EntretienID]
    inner join {Engins} on {Engins}.[Id] = {Entretien_Voiture_Camion}.[EnginID]
WHERE
{Engins}.[Id] = {Entretien_Voiture_Camion}.[EnginID] and {Entretien_Voiture_Camion}.[Id] = {Entretien_Item_VC}.[EntretienID]
GROUP BY
{Engins}.[Marque]
ORDER BY
    sum({Entretien_Item_VC}.[Prix_Total]) DESC
 in the TestOutput :
AUDI15     1150
Dacia         450
But what i want is to calculate the total 'Intrants' and to dsiplay it on the table record and i don't want to create other Table record and to display this Strucure on it..

i hope to understand me please i need to find a solution of this issus 
cheers
I aleready diplay the total from the Structure on My Table Record "Intrant" and it's ok but the probleme is when i change the 'Engin' and i want to calculate the new 'Intrant' i found the precedent 'Total' on my Table Record for more explications you see my many to many relationsheep in this attachement

 
Hi,

when I connect to an enternal database, 
in this step: To publish the extension module, click the  1-Click Publish button.
I met issue.
But I don`t know how to set up it, please have a look on it. Thanks
Hi Eric,

The missing configurations depend on what is the stack you are building the extension for, .NET or Java. In this post I believe you'll find all the necessary information.
André Vieira wrote:
Hi Eric,

The missing configurations depend on what is the stack you are building the extension for, .NET or Java. In this post I believe you'll find all the necessary information.
 Thanks for pointing out the IS configuration issue. I followed the steps in the link and made some progress. Now I got a complier error (see screen shot below)
Found that the tools.jar file is in jdk1.8.0_45\lib folder. Do you know why the complier is looking for this file in a wrong directory?
 
Eric,

Are you using eclipse as a Java IDE? Does the project build correctly in eclipse and using ant?
André Vieira wrote:
Eric,

Are you using eclipse as a Java IDE? Does the project build correctly in eclipse and using ant?
 Yes,and how to test the project build correstly? I thought we jsut need it to connect to the database.
 
Can you share a screenshot of your settings in Integratio Studio for Java?
André Vieira wrote:
Can you share a screenshot of your settings in Integratio Studio for Java?
 sure
 
I remember something on that post about integration studio configurations that you had to close it and reopen it so that environment variables are properly read. Did you try this?
André Vieira wrote:
I remember something on that post about integration studio configurations that you had to close it and reopen it so that environment variables are properly read. Did you try this?
 Yes, I have closed it.
and the detail error message is :
J2EE Compilation.
 
Unable to locate tools.jar. Expected to find it in C:\Program Files\Java\jre1.8.0_31\lib\tools.jar
Buildfile: C:\Users\wange12\AppData\Local\Temp\Integration Studio\YLe_6XvRgEmg8ZlgNcXBgQ\Source\J2EE\build.xml
 
init:
 
compile:
   [javac] C:\Users\wange12\AppData\Local\Temp\Integration Studio\YLe_6XvRgEmg8ZlgNcXBgQ\Source\J2EE\build.xml:34: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
 
 
BUILD FAILED
C:\Users\wange12\AppData\Local\Temp\Integration Studio\YLe_6XvRgEmg8ZlgNcXBgQ\Source\J2EE\build.xml:34: Unable to find a javac compiler;
com.sun.tools.javac.Main is not on the classpath.
Perhaps JAVA_HOME does not point to the JDK.
It is currently set to "C:\Program Files\Java\jre1.8.0_31"
 
Total time: 0 seconds
 
eric,

The message indicates that the JAVA_HOME is point to JRE and not to JDK...
Try configuring JAVA_HOME and ANT_HOME as user variables.

Ah it just caught my attention. You should use JDK 1.6 not 1.8! Check the system requirements document.
André Vieira wrote:
eric,

The message indicates that the JAVA_HOME is point to JRE and not to JDK...
Try configuring JAVA_HOME and ANT_HOME as user variables.

Ah it just caught my attention. You should use JDK 1.6 not 1.8! Check the system requirements document.
 Thank you so much! it works
 
Hi

How can i make Serch keyWord from this Table record because it contain the Structure Data?? so please help

cheers
Hi,
 
When I use the external Mysql database, I`ve publiced it to Eric_Build, and import to my project, I met the issue as below.
Please help to look into it , thank you!
Hi eric,

When you published the extension Eric_Build you were prompt to set the logical database in service center. Did you do that? Check here the complete procedure.
By the way, why are you using a SQL node and not an Aggregate?
André Vieira wrote:
Hi eric,

When you published the extension Eric_Build you were prompt to set the logical database in service center. Did you do that? Check here the complete procedure.
By the way, why are you using a SQL node and not an Aggregate?
 Thanks a lot, it works , I do miss that step.
It`s just a test case, I`ll use more complex sql later.
 
Hi,

I meet error when I write "Where Stream LIKE @stream + '%'
"AND {project}.[project_name] in (@VNXe,@VNX) "
how to write this two in the SQL?
Hi eric,

Can you please post the error and the complete SQL statement?
Thanks