Accessing SQLite Views

Solar2D General Questions/Discussion
#1

Hi All,

I’m developing an App that access SQLite database. SQLite has the possibility to have some SQL Queries  called Views but I haven’t find any information about them with Corona SDK:

>>> There is any way to execute SQLite Views from Corona SDK?

As I can’t access the views I tried to work with the SQL. If it is very simple it works well, but I have some little complex SQL like this:

“SELECT T2.AREA, T2.PS_POSID1, T2.NAME, T3.PS_POSID2, T3.NAME AS NAME2,T1.TCODE, TT.TTEXT FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS_POSID1=T1.PS_POSID1 AND T3.PS_POSID2=T1.PS_POSID2 AND TT.TCODE=T1.TCODE) WHERE TT.LANGU = ‘E’ AND T2.LANGU = ‘E’ AND T3.LANGU = ‘E’ AND ( T1.FREQ = ‘A’ OR T1.FREQ = ‘B’ ) ORDER BY T2.AREA, T2.PS_POSID1, T3.PS_POSID2, T1.TCODE”

This SQL works property well on SQLite Manager but when I try to execute on Corona I receive this error:

“Failed to parse error message: no such column T2.AREA”

>>> Why this correct SQL is not working on Corona?

Thanks in advances

#2

Views are not supported to the best of my understanding. You need to run the full complex SQL script against your db. If the query is running in SQLite Manager and not in Corona chances are its a simple formatting / syntax issue. I would start by simplifying it as much as possible and try to get it to work that way. You can then add back order etc one by one.

#3

Yes, The query is running in SQLite manager. There is any help about the changes in formatting / syntax ? I will follow your suggestion. Thanks for your help

#4

If you don’t mind, please post your sqlite file here and I will take a look. Its very hard to decipher your db schema just by looking at that sql script. All the best.

#5

As ksan said you need to simplify. Its the key to sql debugging. 

Set it up in a more readable manner using a tool such as http://sqlformat.appspot.com/

SELECT T2.AREA, T2.PS\_POSID1, T2.NAME, T3.PS\_POSID2, T3.NAME AS NAME2, T1.TCODE, TT.TTEXT FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS\_POSID1=T1.PS\_POSID1 AND T3.PS\_POSID2=T1.PS\_POSID2 AND TT.TCODE=T1.TCODE) WHERE TT.LANGU = 'E' AND T2.LANGU = 'E' AND T3.LANGU = 'E' AND (T1.FREQ = 'A' OR T1.FREQ = 'B') ORDER BY T2.AREA, T2.PS\_POSID1, T3.PS\_POSID2, T1.TCODE

So we want to simplify.

The obvious place to start is selecting * instead of all those columns.

Then get rid of all the ordering

Then get rid of all the WHERE

So for example does this run?

SELECT \* FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS\_POSID1=T1.PS\_POSID1 AND T3.PS\_POSID2=T1.PS\_POSID2 AND TT.TCODE=T1.TCODE)

If not we have a much simpler problem.

Then you could get rid of irrelevant joins.

Does this run?

SELECT \* FROM transactions AS T1 INNER JOIN (app01 AS T2) ON (T2.PS\_POSID1=T1.PS\_POSID1)
#6

Your response helps me a lot but still it is not working. I have tested your two samples and it seems Corona don’t like the name of the tables. With the last of your samples I tried:

“SELECT * FROM transactions INNER JOIN (app01) ON (app01.PS_POSID1=transactions.PS_POSID1)” 

I have simplified more and more cutting the “AS” and writing the original name of the tables but the problem persist:

Failed to parse error message: no such column app01.PS_POSID1

I have read in the forums that Corona prefer the name of the fields (without the name of the table before) but, If I have more than a table, I need a way to especify the table I’m refering, doesn’t it?

Thanks for your help! ;) 

#7

OK I was not aware of that Corona does not like pseudo names.

But are you sure all uppercase and lowercase letters are correct?

#8

Yes, I know it is case sensitive and I’m sure this is not the problem: the same happens on T2.AREA at the beginning and was all UPPERCASE… the problem is that I don’t know how to specify the table and the column because it returns “no such column” in any case. It seems with a different syntax it has to work…

#9

Lets see if we can get someone from Corona to comment. Rob Miracle has a SQL background I think.

If its a Corona problem using the AS method then I guess you are down to renaming columns uniquely.

 if you are not 100% you could make a sample DB and runnable code I could help verify if its Corona problem.

#10

Another example of the problem. If I use:

SELECT * FROM transactions AS T1 INNER JOIN (app01 AS T2,app02 AS T3,transacttext AS TT) WHERE TT.LANGU = ‘E’ AND T2.LANGU = ‘E’ AND T3.LANGU = ‘E’ AND (T1.FREQ = ‘A’ OR T1.FREQ = ‘B’)

The problem is: not such column TT.LANGU

All the first part works well: SELECT * FROM transactions AS T1 INNER JOIN (app01 AS T2,app02 AS T3,transacttext AS TT) if I only write this.

The problem is when I use a table (TT), a point(.) and a Column(LANGU)

Any idea is welcome

#11

It happens with the AS method and without the AS method. If I write directly the name of the table I have the same results. Probably renaming all the columns uniquely (as you said) could be the solution but It don´t seems very operative for a large databases. I’ll try to change all the column names…

#12

I think its down do the use of ( and ) in the join.

SELECT * FROM transactions AS T1 INNER JOIN app01 AS T2, app02 AS T3, transacttext AS TT WHERE TT.LANGU = ‘E’ AND T2.LANGU = ‘E’ AND T3.LANGU = ‘E’ AND (T1.FREQ = ‘A’ OR T1.FREQ = ‘B’)

Try that.

#13

It works! :wink: Great!

Now, I’m going to rebuild all the original query with this in mind.

Thanks another time :wink:

#14

phew :slight_smile:

#15

Views are not supported to the best of my understanding. You need to run the full complex SQL script against your db. If the query is running in SQLite Manager and not in Corona chances are its a simple formatting / syntax issue. I would start by simplifying it as much as possible and try to get it to work that way. You can then add back order etc one by one.

#16

Yes, The query is running in SQLite manager. There is any help about the changes in formatting / syntax ? I will follow your suggestion. Thanks for your help

#17

If you don’t mind, please post your sqlite file here and I will take a look. Its very hard to decipher your db schema just by looking at that sql script. All the best.

#18

As ksan said you need to simplify. Its the key to sql debugging. 

Set it up in a more readable manner using a tool such as http://sqlformat.appspot.com/

SELECT T2.AREA, T2.PS\_POSID1, T2.NAME, T3.PS\_POSID2, T3.NAME AS NAME2, T1.TCODE, TT.TTEXT FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS\_POSID1=T1.PS\_POSID1 AND T3.PS\_POSID2=T1.PS\_POSID2 AND TT.TCODE=T1.TCODE) WHERE TT.LANGU = 'E' AND T2.LANGU = 'E' AND T3.LANGU = 'E' AND (T1.FREQ = 'A' OR T1.FREQ = 'B') ORDER BY T2.AREA, T2.PS\_POSID1, T3.PS\_POSID2, T1.TCODE

So we want to simplify.

The obvious place to start is selecting * instead of all those columns.

Then get rid of all the ordering

Then get rid of all the WHERE

So for example does this run?

SELECT \* FROM transactions AS T1 INNER JOIN (app01 AS T2, app02 AS T3, transacttext AS TT) ON (T2.PS\_POSID1=T1.PS\_POSID1 AND T3.PS\_POSID2=T1.PS\_POSID2 AND TT.TCODE=T1.TCODE)

If not we have a much simpler problem.

Then you could get rid of irrelevant joins.

Does this run?

SELECT \* FROM transactions AS T1 INNER JOIN (app01 AS T2) ON (T2.PS\_POSID1=T1.PS\_POSID1)
#19

Your response helps me a lot but still it is not working. I have tested your two samples and it seems Corona don’t like the name of the tables. With the last of your samples I tried:

“SELECT * FROM transactions INNER JOIN (app01) ON (app01.PS_POSID1=transactions.PS_POSID1)” 

I have simplified more and more cutting the “AS” and writing the original name of the tables but the problem persist:

Failed to parse error message: no such column app01.PS_POSID1

I have read in the forums that Corona prefer the name of the fields (without the name of the table before) but, If I have more than a table, I need a way to especify the table I’m refering, doesn’t it?

Thanks for your help! ;) 

#20

OK I was not aware of that Corona does not like pseudo names.

But are you sure all uppercase and lowercase letters are correct?