DataBase Cursor

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

DataBase Cursor

Post by chipsm » Mon Jun 15, 2015 9:10 pm

I have a routine where I open a database and read all of the table names into a field.
I use this data to select a table that I would like to do a query on. I cannot query the tables. is this because the pointer of cursor of the database is at the bottom and needs to be reset?
Clarence Martin
chipsm@themartinz.com

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

Re: DataBase Cursor

Post by MaxV » Mon Jun 15, 2015 11:33 pm

Hi,
I'd use revdataFromQuery() function:

Code: Select all

put "SELECT * FROM users ; " into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 1:23 pm

Thanks Max,
I actually found the problem.
I was beating my head against a wall that was somewhat impenetrable. I found out that LiveCode cannot process tableNames that have spaces in them. It throws a "revdberr" message.
Does anyone have a work around for this problem? Unfortunately I am not in control of the data source and I need a fix.
Clarence Martin
chipsm@themartinz.com

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: DataBase Cursor

Post by Mikey » Tue Jun 16, 2015 2:18 pm

This isn't an LC issue. This is an SQL parser issue. You "quote" tablenames and fieldnames in SQL by surrounding them with square brackets.

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 2:37 pm

Please elaborate!
Clarence Martin
chipsm@themartinz.com

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: DataBase Cursor

Post by Mikey » Tue Jun 16, 2015 2:50 pm

SELECT [field name with spaces embedded in it] FROM [table name with spaces in it]

This isn't an LC problem. It's a result of the way the SQL parser works (tokens are delimited by spaces).

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 2:56 pm

Thanks. I will try it immediately.
Clarence Martin
chipsm@themartinz.com

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 3:05 pm

Mikey,
This is the code that I used and the same problem is still existing.

put "SELECT * FROM [465 halstead coppercube_10100_tl1]" into tSQL
put revDataFromQuery(tab,return,lDatabaseID,tSQL) into tRecords
put tRecords into field "Field2"

What am I doing wrong?
Clarence Martin
chipsm@themartinz.com

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: DataBase Cursor

Post by Mikey » Tue Jun 16, 2015 3:14 pm

Have you tried putting that query into valentina to play with it? Without looking at the schema it will be hard to tell exactly what's going on.
Are you sure the 465... is a table name and not an item value?

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 3:20 pm

Mikey,
I don't have Valentina.
The 465 is part of the table name.
I actually made a copy of the table without the spaces and the code works (this is before the square brackets). This is how I found out about the spaces in the Table Name and spaces.
So, the square brackets don't seem to be working for me yet.
Clarence Martin
chipsm@themartinz.com

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: DataBase Cursor

Post by Mikey » Tue Jun 16, 2015 3:47 pm

Valentina is free to download and makes it easy to mess with databses, test queries, etc.
Again, without seeing the schema it will be difficult to help much more, but if you google spaces in table names you'll see more about it.

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 3:56 pm

Mikey,
again...thanks for your help.
Clarence Martin
chipsm@themartinz.com

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Tue Jun 16, 2015 4:02 pm

Mikey and all who need it.
Thanks to Mikey for helping me.
During a search I found the answer.
Use the tick symbols which you can usually find on your keyboard to the left from digit 1.
This Works.
Clarence Martin
chipsm@themartinz.com

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

Re: DataBase Cursor

Post by MaxV » Wed Jun 17, 2015 9:46 am

Does the following code work?

Code: Select all

put "SELECT * FROM " & quote & "465 halstead coppercube_10100_tl1" & quote & ";" into tSQL
put revDataFromQuery(tab,return,lDatabaseID,tSQL) into tRecords
put tRecords into field "Field2"
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

chipsm
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 244
Joined: Wed May 11, 2011 7:50 pm
Location: Southern California

Re: DataBase Cursor

Post by chipsm » Wed Jun 17, 2015 2:25 pm

Hi Max,
I tried many iterations of quotes but I don't remember just using the keyword quote. I used "put quote &" and that didn't work.
I'll try that later.
Thanks.
Clarence Martin
chipsm@themartinz.com

Post Reply

Return to “Databases”