Page 1 of 2
Select Col.name from table - SOLVED
Posted: Thu Jan 18, 2018 12:21 am
by bogs
I am having a bit of trouble working through a db query. The db is sqlite.
This line gives me all the information asked for -
Code: Select all
put "SELECT (Title),(Year),(Format),(Summary) FROM Movies" into tmpSql
and puts it neatly into the datagrid, however if I then add Cast -
Code: Select all
put "SELECT (Title),(Year),(Format),(Cast),(Summary) FROM Movies" into tmpSql
to the column names to retrieve from, it fails with dberr near ")";
Can anyone see what I am apparently missing? Thanks.
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 2:30 am
by quailcreek
First thing I see is you don't need the (
Code: Select all
put "SELECT Title,Year,Format,Cast,Summary FROM Movies" into tmpSql
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 2:42 am
by bogs
I didn't think I did either, but when I entered it as in your code example, it failed completely? Maybe I need to try it from a fresh project.
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 2:43 am
by quailcreek
What was the error and what is the rest of your query statement?
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 3:22 am
by bogs
You asked
Keep in mind, I am migrating this program code from Realbasic to Lc, so this is the first time I'm poking about db's in Lc. The code is neither pretty nor even very nice at this point in my learning.
The error returned when the statement is formatted like your example, whether spaces or no after the commas is 'revdberr,Database Error: near ",": syntax error'
Code: Select all
// code inside button loading the db into a datagrid named 'lstRecords'...
local tmpConnId, tmpSql, tmpRecs
on mouseUp
if field "connID" is empty then put revOpenDatabase ("sqlite", "collections.rsd", , , , ) into field "connID"
// this is only so I can see the connection id at this point...
put field "connID" into tmpConnId
// the statement commented here works, but not if I add (Cast)...
// put "SELECT (Title),(Year),(Format),(Summary) FROM Movies" into tmpSql
/* this statement returns [revdberr,Database Error: near ",": syntax error]
whether there are spaces or not ... */
put "SELECT Title, Year, Format, Cast, Summary FROM Movies" into tmpSql
// from here to the end appears to work...
put revDataFromQuery(tab,return,tmpConnId,tmpSql) into tmpRecs
sort tmpRecs ascending
put "Title" & cr & "Year" & cr & "Format" & cr & "Cast" & cr & "Summary" & cr & "catNumber" into tmpCols
set the dgProp["columns"] of group "lstRecords" to tmpCols
set the dgText of group "lstRecords" to tmpRecs
end mouseUp
Edit* In case your wondering what the values are, I created it in RB with this statement -
Code: Select all
CREATE TABLE Movies (Title string, Year String, Format String, Cast String, Summary String, Cover Binary, catNumber Integer PRIMARY KEY)
I know (or think I know) when I go to retrieve the covers (pictures), that will require 2 statements, using revQueryDatabase and revDatabaseColumnNamed since it is a blob.
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 3:42 am
by quailcreek
You really shouldn't have extra spaces in your statements. Your create table statement has them in front of the column name and I've had that cause me lots of trouble.
This is from one of my apps.
Code: Select all
put "CREATE TABLE MyInfo(MyID INTEGER PRIMARY KEY AUTOINCREMENT,First TEXT,Middle TEXT,Last TEXT,Address TEXT,City TEXT,State TEXT,Zip TEXT,Email TEXT,Cell_Phone TEXT,MemberNum TEXT)" into tTableSQL
This is untested so let's see what happens.
Code: Select all
// code inside button loading the db into a datagrid named 'lstRecords'...
local tmpConnId, tmpSql, tmpRecs
on mouseUp
if field "connID" is empty then put revOpenDatabase ("sqlite", "collections.rsd", , , , ) into field "connID"
// this is only so I can see the connection id at this point...
put field "connID" into tmpConnId
// the statement commented here works, but not if I add (Cast)...
// put "SELECT (Title),(Year),(Format),(Summary) FROM Movies" into tmpSql
/* this statement returns [revdberr,Database Error: near ",": syntax error]
whether there are spaces or not ... */
put "SELECT Title,Year,Format,Cast Summary FROM Movies" into tmpSql
// from here to the end appears to work...
put revDataFromQuery(tab,return,tmpConnId,tmpSql) into tmpRecs
answer the result -- let's see when the error is
sort tmpRecs ascending by item 1 of each
put "Title" & cr & "Year" & cr & "Format" & cr & "Cast" & cr & "Summary" & cr & "catNumber" into tmpCols
set the dgProp["columns"] of group "lstRecords" to tmpCols
put "Title" & tab & "Year" & tab & "Format" & tab & "Cast" & tab & "Summary" & cr into theHeaders
set the dgText [true] of group "lstRecords" to theHeaders & tmpRecs
end mouseUp
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 3:52 am
by bogs
"revdberr,Database Error: near "Summary": syntax error" is what happened here.
Curious, I thought at the very least you needed the comma as a delimiter in sql statements? Cast and Summary are two separate fields in the table.
I should probably add that this is all taking place here in Lc 6.5.2, not 8.x or one of the dp's, so that may make a difference.
Edit* I guess not, I just tried it in 7.1.4 and got the same result with both issues. Weird.
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 3:57 am
by quailcreek
Sorry, that was a typo. You do need the comma between summary and cast.
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 4:14 am
by bogs
quailcreek wrote: ↑Thu Jan 18, 2018 3:42 am
You really shouldn't have extra spaces in your statements. Your create table statement has them in front of the column name and I've had that cause me lots of trouble.
I'll take your word on that, when I wrote this initially in Rb (um, about 10 to 12 years ago), It was the first time I had touched a db of any kind. My wife designed an interface and wanted a program to keep track of her stuff. It has worked for at least 10 years, so I guess I won't complain too much
quailcreek wrote: ↑Thu Jan 18, 2018 3:57 am
Sorry, that was a typo. You do need the comma between summary and cast.
Ah, I see. <correcting back to previous>
The answer was the same error I posted above, in an answer dialog.
Here is a shot of the debugger showing whats in play at the time, maybe I'm just missing whats causing the error.
Edit* I started removing all the column names except for one to test that way, it appears Cast itself is causing the problem. Now I am really confused, since all the other sqlite programs I've looked at it with, including
MaxV's demo in the sampler section, have no problem displaying it
Edit 2* I think I see part of the problem, apparently I didn't filter for extraneous tabs at the beginning of some entries, and my wife entering it occasionally used them <sigh>
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 4:25 pm
by AxWald
Hi,
might be that this happens 'cause "
CAST" is a function name in SQL. You might try:
Code: Select all
put "SELECT `Title`,`Year`,`Format`,`Cast`,`Summary` FROM `Movies`" into tmpSql
Have fun!
Re: Select Col.name from table
Posted: Thu Jan 18, 2018 8:17 pm
by bogs
Well, while I didn't know about CAST (thank you for that), I had tried the line you suggested. This was the result -
*Edit - forgive me, I mistook ' for `. once the change was made using `, it worked to correctly bring up the `Cast` column, Thanks again!
I do still have other things to correct, though, with the technique I am using to populate the grid, but I think before I proceed on transitioning this over, I'm likely going to have to crack open RB again and make some corrections in the code there, to filter out all those tabs and other things that produce artifacts like this (Summary is spilling over into catNumber, etc) -
Re: Select Col.name from table
Posted: Fri Jan 19, 2018 1:56 am
by MaxV
cast is a reserved word, so it works only this way:
Code: Select all
put "SELECT Title, Year, Format, Movies.Cast, Summary FROM Movies" into tmpSql
Re: Select Col.name from table
Posted: Fri Jan 19, 2018 3:45 am
by bogs
That did also work Max, thank you for the alternate method.
I'll be marking this as solved, and want to thank all that contributed, but to sum up for any that pop up this thread in the future.
It is probably safest if you either pull fields from the table using `fieldname` or tablename.fieldname, especially if you might unsuspectingly be using a reserved word for one of your fields
Myself, I think I'll always wrap them with accent grave (`) key whether I think they need it or not. Thank you again
Re: Select Col.name from table - SOLVED
Posted: Tue Jan 23, 2018 12:55 pm
by sphere
Hi Bogs,
just a small hint, in your DB you best use only lowercase for your column and table names. (no obligation)
But depending on the underlying OS where the DB is running this could run into issues if you don't take care with it.
Also you don't need to remember which character was uppercase.
and it makes the statement better readable. Commands UPPERCASE table/column lowercase.
put "SELECT title, year, format, movies, cast, summary FROM movies" into tmpSql
more info here:
https://dev.mysql.com/doc/refman/5.7/en ... ivity.html
Re: Select Col.name from table - SOLVED
Posted: Tue Jan 23, 2018 5:46 pm
by bogs
That is good advice, and if I wind up at some point opening an in mem version of it, importing it all, and re-exporting it back, I'll probably go that route.
When I had created this originally, I made a lot of mistakes I'm sure that will come to light as I move it forward. My thoughts (at the time) was along the lines of using the table names to pop the (in that language) the listBox headers directly (the Rb equivalent of the time to a basic table / datagrid object in Lc).
As I said above, its worked out (and is still working) pretty well 10 years and 3 os upgrades later, so I can't complain too much. My normal convention for naming since that point has been camel case, which would have worked for this I think (intCatNum, txtTitle, imgCover, etc), but again, I wasn't doing that back then.
Thank you for the link, though, that with all the other stuff I've picked up since this programs original conception should produce a better way forward
