Hi,
How to write query and fetch data from SQL server(Since I need to perform group by statement & other aggregate functions) and load it into list control. Whether we need to write query through ‘Embeditor source’ or through any other way we can able to achieve this? If we need to use ‘Embeditor source’ means is there any syntax available to send query to DB & fetch the data from DB to clarion. Please let me know the syntax for that. Thanks in advance.
Some possibilities:
I tend to use Jane’s point 3 for complicated SQL. It’s just way easier.
The SQL View is used just like a Table in Clarion so a simple Browse just works.
Agree completely. Would be interested to hear how others code for an update form off of a browse using a view.
Assuming you want it to be updated, I usually cause the update to use appropriate base table and in the init fetch the appropriate row.
An “Instead of” trigger on the SQL View is also worth checking out. The SQL code in the trigger is executed “Instead of” the insert, or update, or delete request sent by your app.
Frequently used here with Postgres on more complex views. Has not been the easiest to debug in Clarion because of return value limitation.
Hi,
I used Prop:SQL as below for ‘City’ combo control through Embeds.
Now no syntax error is showing and this query is getting passed to SQL server. I found this through the below SQL profiler trace.
Now the values from query is not getting displayed to the app screen. Is there any ‘set’ command I need to use in the embeds source for this combo? Please assist on this.
Please attach if there is any sample screenshot using PROP:SQL command it will help for us simulate the same.
Hi Mike,
Yes I gone through the help page. I used Next command to set the value as below. It’s throwing me the error ‘No Matching prototype available’ below is the screenshot. Please let me know what wrong in this code line. If any sample screenshot available for this please attach that so that I will simulate the same in my code also.
Obviously to understand why you get “No matching prototype” on NEXT(Loc:City) read the “NEXT” help topic.
Seriously, use method #3 from Jane’s post. That is: create a view on the server with all the bells and whistles and aggregates and whatever else. Import that view as a “table” ion the clarion dictionary. Use it in Clarion as you would any other table. If you want to update it, then that’s something else we can help with later.
But I think we’re going to give up on you quickly with your current approach. “I put Customers{PROP:SQL}=‘Select City from Customers’ in some (unspecified) embed point and I don’t see anything.”
Quite a lot of things could be wrong with that:
wrong embed point
first field in customers might not be a text field
customers might not be open; you are not checking for errors
you don’t appear to have a next loop to retrieve the values
you want to display the results in a list box which means transferring items from the buffer to a queue, but you don’t seem to be doing that
So then it turns out you do have a next loop, but it is operating off a local variable. When you have something like customers{PROP:SQL}, not only does CUSTOMERS define the buffer into which the results of the select will be put, CUSTOMERS also defines a connection to the database. Your local variable doesn’t have a database connection, so it can’t be sending anything to the database.
So we’ve now found out one of the things you are doing wrong out of the handful or two of things you could be doing wrong. So…do it the easy way first, because I don’t think we have the stamina to drag you through the mud all the way to the finish line the way you are choosing to do it.
You might want to look at the example app I just posted that shows how to limit droplists using PROP:SQL. I still think creating a view and importing it into your dictionary is more likely to be what you need, but this demonstrates another option.
You can’t use local variable, Loc:City, like a table, this is wrong approach
You have mixed up the use of the QUEUE and the TABLE. You have FREE(Loc:City) which implies you are working with a QUEUE, but then you have a PROP:Sql statement that implies you are working with a table. It would not be very common to use a prefix, “Loc:”, in a table name. So, I suspect the PROP:Sql and NEXT() statements are using the wrong variable.
Just create dictionary and import the tables from SQL server. and then start working on it, the changes you will make will save in the SQL Server database.
