Select Col.name from table - SOLVED

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Select Col.name from table - SOLVED

Post by bogs » Thu Jan 18, 2018 12:21 am

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.
Last edited by bogs on Fri Jan 19, 2018 3:46 am, edited 1 time in total.
Image

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Select Col.name from table

Post by quailcreek » Thu Jan 18, 2018 2:30 am

First thing I see is you don't need the (

Code: Select all

put "SELECT Title,Year,Format,Cast,Summary FROM Movies" into tmpSql
Tom
MacBook Pro OS Mojave 10.14

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Thu Jan 18, 2018 2:42 am

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.
Image

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Select Col.name from table

Post by quailcreek » Thu Jan 18, 2018 2:43 am

What was the error and what is the rest of your query statement?
Tom
MacBook Pro OS Mojave 10.14

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Thu Jan 18, 2018 3:22 am

You asked :P
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.
Image

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Select Col.name from table

Post by quailcreek » 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.

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
Tom
MacBook Pro OS Mojave 10.14

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Thu Jan 18, 2018 3:52 am

"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.
tableFields.png
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.
Last edited by bogs on Thu Jan 18, 2018 3:58 am, edited 1 time in total.
Image

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Select Col.name from table

Post by quailcreek » Thu Jan 18, 2018 3:57 am

Sorry, that was a typo. You do need the comma between summary and cast.
Tom
MacBook Pro OS Mojave 10.14

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Thu Jan 18, 2018 4:14 am

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 :wink:
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.
Image

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 :cry:

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>
Image
Image

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Select Col.name from table

Post by AxWald » Thu Jan 18, 2018 4:25 pm

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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Thu Jan 18, 2018 8:17 pm

Well, while I didn't know about CAST (thank you for that), I had tried the line you suggested. This was the result -
Image

*Edit - forgive me, I mistook ' for `. once the change was made using `, it worked to correctly bring up the `Cast` column, Thanks again! :D

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) -
Image
Image

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Select Col.name from table

Post by MaxV » Fri Jan 19, 2018 1:56 am

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
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table

Post by bogs » Fri Jan 19, 2018 3:45 am

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 :wink:

Myself, I think I'll always wrap them with accent grave (`) key whether I think they need it or not. Thank you again :D
Image

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Select Col.name from table - SOLVED

Post by sphere » Tue Jan 23, 2018 12:55 pm

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

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: Select Col.name from table - SOLVED

Post by bogs » Tue Jan 23, 2018 5:46 pm

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 :D
Image

Post Reply

Return to “Databases”