SQLite Views instead of Tables?
SQLite Views instead of Tables?
Lalato
Posts: 5Questions: 1Answers: 0
Anyone have success using SQLite Views instead of Tables?
I can get tables to work without issue, but when I try to do something with a view, the resulting datatable output is empty. It says it's returning records, but nothing appears on the page. I don't mind using tables, just wondering if anyone had resolved this kind of issue before.
This discussion has been closed.
Answers
What is the JSON that your view code is returning? Assuming that valid JSON is being returned, DataTables does really care where the data comes from.
Allan
Thanks for taking the time to help, Allan. I especially appreciate since I'm a novice.
I'm at my day job right now. I'll check how to do that (validate JSON) when I get a free moment today or maybe when I get home tonight.
If it helps, here's where I've been playing with datatables...
http://lalato.com/dl/dtables/leagues.php
OK. I had a chance to look at the JSON output when using the view. According JSONlit's valid. However, the values returned all NULL. Here's a small sample of the JSON...
{"sEcho":2,"iTotalRecords":"1586","iTime":0.0775589942932,"iTotalDisplayRecords":"1586","aaData":[[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],[null,null,null,null,null,null],
And it's nulls all the way down.
It's returning the correct number of rows (1586), but it doesn't have any data.
Nulls! I thought it was turtles all the way down! ;-)
I guess you are using PHP to get the data from a database? How are you doing that? That looks like where the problem is, not with DataTables.
Allan
Yes, it's pulling from a SQLite database via PHP. When I run the query directly against SQLite it runs just fine. And when I put the same exact data into a table, it displays via datatables just fine. I'm not advanced enough to know what's going on there. Just seems odd that a view would act so weirdly different than a table.
Here's a version with the view... (aka "nulls/turtles all the way down")
http://lalato.com/dl/dtables/leaguesv.php
and a version with the table... (aka "I can haz data")
http://lalato.com/dl/dtables/leagues.php
The view and the table both have the same columns with the same exact data. The only difference in the query is instead of pointing to dl_leagues_t (the table) it points to dl_leagues_v (the view). Adding to the weirdness is that when pulling from the view, it returns the correct number of rows... it's just that those rows are all filled with null values.
You're right that this isn't so much a datatables issue. It works fine with a table... I was just hoping someone might have run into the view issue and had a workaround.
So "serverdatapdo.php" gives two different responses where the only difference is table vs. view.
Could the answer be in your PDO connection params?
Upon further internet searching and tinkering I have stumbled upon a solution. Apparently Views require that you explicitly name the columns. So I named the columns what would appear in my datatable (COUNTRY, REGION, CITY, etc. instead of leaving the original table names... league_country, league_region, league_city, etc.)
That seemed to do the trick, but I still find it strange.
Good to hear you got it sorted out. Not sure why SQLite works like that, but I'm sure they have a reason.
Allan