Data not displaying in a generated table based on a mysql view

[BruceX]

Hi, I have generated a table that is based on a database view. The headings are displayed correctly showing the that the server side is working ok, but the table is not showing rows of test data that I have in the db view. The db view is simple, Select * from ‘db tablename’.
As a comparison I generated another table based on the ‘db tablename’, that worked perfectly.

In the image the top table is based on the db view, the bottom on db table, you can see that the rows are displayed perfectly in the db table version only.

As a check, back in MySQL, the db view displays rows perfectly.

How can I get a generated table in wappler, based on a db view, to show rows.

Note - there are no dynamic sorts or filters on either table

[bpj]

Hi @BruceX,
I use views all the time without issue - they return data in exactly the same way as tables. If it isn’t returning results, check you haven’t got any conditions in the query (in query builder). Perhaps share your query builder settings for us to see.

You could also check to see if it is returning results but the repeat expression for the table has a problem. Has this page been published to a public location? If so, could you share a link?

[BruceX]

Hi Ben THe web page is https://marama-cloud.com/activity_summary.php

The first tab, Total Activities, has 2 tables,
the top one driven from a db view defined as Select * from ActivitySteps.
The second one driven from the ActivitySteps db table itself.

[bpj]

You can diagnose a lot of issues when running SC requests in the developer tools of the browser. In the network tab you can see each of these:

As you’ll see, there’s a problem with two of your queries.

If you enable debugging, you should get more of the detail why they fail:

[BruceX]

Hi Ben

I followed your advice and got exactly the same results

I went thru everything from the defiitions in MySQL, (where the views do return a result) to the Wappler query definition (the view name is recognised with the columns names being listed) to the server connection which is set correctly, (with the sort and dir options showing up, which they only do when the server connect is valid), to the correct setting for the table on the tab. I regenerated the table and got the result, ie column headings but no data.

On investigating this in debug, 500 errors are generated for the view (as seen above).

Diving a little deeper i get to this…

Not much help here. Could the MySQL be timing out when running views to wappler, how do I increase teh timeout Limit?

[pheaxx]

Not fully familiar with MySQL, but the ViewActivitySummary.php is giving the following error:

which seems to related to to many queries and a too low cache setting. This might help or point to the correct direction: https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared

Again, I’m not an expert in MySQL.

[Teodor]

@BruceX

On investigating this in debug, 500 errors are generated for the view (as seen above).

Diving a little deeper i get to this…

Not much help here.

Please follow the exact steps explained here to check the exact error message:

Debugging Server Connect Errors

You can easily debug issues related to server-side processing such as - form not sending email, record not inserting or updating, query not showing data etc. The first thing you need to do is to open the server connect settings: [image] And enable the Debug Mode: [image] Click Save: [image] In your browser, open the Developer tools. If you don’t know how to open them please refer to your browser’s documentation. Then open the Network tab(1) and select XHR(2): [image] Now with the de…

[Teodor]

@pheaxx

Not fully familiar with MySQL, but the ViewActivitySummary.php is giving the following error:

Well that’s a known bug with views in older mysql versions - prior 5.7 on PHP (not related to Wappler).
That’s why we added an option to enable prepared statements in the database connection options:

[BruceX]

My database connection options is set to prepared, and I do not think I get any control over database cache sizes etc where the MySQL is hosted.

I notice that you have Direct Connection turned on. Whenever I have tried that, I do get a timeout when I refresh the schema.

Does Wappler have any control over timeout? or is this a database thing and I need to get help from the Database Host.

Failing progress there, a plan be might be to use the db view sql from within Wappler. I notice that in the Query Definition the sql is not editable, but it is in the underlying php. This is inconvenient answer obviously but to get the views to work, is that a way to go?

[Teodor]

@BruceX

My database connection options is set to prepared, and I do not think I get any control over database cache sizes etc where the MySQL is hosted.

Just set the prepared statements option to true in the database connection…
You do not have to adjust any cache.

[bpj]

@Teodor

Well that’s a known bug with views in older mysql versions - prior 5.7 on PHP

@BruceX,
If this is a relatively new project, why are you using such an old version of MySQL - would you not be better off moving to something newer before you get too far down the road?

[BruceX]

Did that, but 500’s are still generated

btw, the host calls it MySQL, but really it is…

[Teodor]

@BruceX

Did that, but 500’s are still generated

So what’s the exact error message now?

[bpj]

General error: 1615 Prepared statement needs to be re-prepared

[Teodor]

This means the prepared statements option in the database connection is not set to True.

[bpj]

@BruceX, just checking (because we’ve all been there), did you re-publish after changing the prepared statement setting?

[BruceX]

No I didn’t, I just thought if that just before you asked, will do now, and I know to reset the debug mode again as well.

[BruceX]

While publish is uploading, about your comment that my db is an old version, it looks to me to be mariadb ver 10+. Is that old? I am learning a lot from every answer you give me thank you, what is the minimm version that is good for wappler that perhaps I can get my host to upgrade to?

[bpj]

MariaDB is a fork of MySQL and acts mostly the same as MySQL. V10.0 came out in 2012 but several versions have been released since. Which 10.X version are you using?

[BruceX]

Yehaaa, it worked, I seriously wish I could solve these problems myself, but I am 3 weeks into wappler, i really appreciate the help and patience from the team at wappler, really good.