SQL Yoga Query

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 2:59 pm

[This post is an archive from the old SQL Yoga forums]

Hi Trevor,

What do you see in the message box if you add this to the handler:

Code: Select all

## print out MD array with first line of each key value.

put printkeys(theRegionDataA)
I don't see nothing, the stack is stoped at line 1313, but only I see the protected stack password field..

I put the code at the end of the button, is correct?

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 2:59 pm

I see this error

stack "libSQLYoga": execution error at line 1610 (Object: object does not have this property) near "invalid array type for query"

But I still thinking that maybe I don't connect to my mysql database correctly. What is the correct flow to connect to the database?

Once time in the preopenstack only? I read that Yoga connect if is necesary to the database, isn't?

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 2:59 pm

Right before you execute sqlquery_retrieveAsArray can you try:

Code: Select all

printKeys(theQueryA)
I want to see what your SQL Query "object" looks like.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 2:59 pm

Hi Trevor,

Empty. Nothing appear.

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:00 pm

SQL Yoga will attempt to connect to the database whenever it needs to query the database. If SQL Yoga has never connected to your database before it hasn't had a chance to read the db schema which means commands like sqlquery_create will return empty. I think that is what is happening in your case.

SQL Yoga should really throw an error in this situation as no table or sql query template exists with the name that you passed in. I've updated the library so it does now, just grab the latest build which is linked to in the first thread of this forum.

I've updated the docs in order to clarify how to set up SQL Yoga the first time you use it in your app as well as what is required for normal use after that.

Incorporating SQL Yoga Into Your Application For the First Time

Initializing SQL Yoga For Use After The First Time

Also, I looked at your connect code again and noticed that you don't check for errors when connecting to the database.

Code: Select all


dbconn_connect

put the result into theError

if theError is not empty then

answer "error connecting:" && theError

end if

Do you see an error message?
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:00 pm

Hi Trevor,

This is my connection code:

Code: Select all

dbobject_set "storage object", the long id of button "DB Storage"

put the result into theError

--dbconn_delete "desarrollo"

put dbobject_getConnection("desarrollo") into theConnA

if theConnA is not an array then

dbconn_create "desarrollo", "mysql"

put the result into theError

if theError is empty then

dbconn_set "host", "www.xxx.xxx"

dbconn_set "username", "admin"

dbconn_set "password", "xxx"

dbconn_set "database name", "store"

dbconn_set "type","mysql"

end if

end if

dbobject_save

put the result into theError

if theError is empty then

dbconn_connect

put the result into theError

if theError is empty then

answer "Connected! ID " & dbconn_get("connection id")

else

answer "Error connecting:" && theError

end if
I can connect so I get a "connection id". No error.

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:01 pm

The code from the button:

Code: Select all

on mouseUp

dbobject_createFromObject the long id of button "DB Storage"

put the result into theError

put sqlquery_create("products") into theQueryA

answer printKeys(theQueryA)

sqlquery_retrieveAsArray theQueryA, theArrayA

put the result into theError

if theError is empty then

## Array can be assigned directly to a data grid

set the dgData of group "dg_test" of me to theArrayA

else

answer "Error populating: " && theError

end if

revspeak "Datagrid loaded"

end mouseUp
I read step by step the manual and I get the same error.

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:01 pm

I get this from the Printkeys:

Code: Select all

table

name: product

database parameters:

conditions:

type: query reference

cursor id: 0

related table joins:

select clause:

scopes:

group by:

connection: desarrollo

distinct:

condition bindings:

full column names:

column delimiter:

database: default

order by:

row delimiter:
Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:01 pm

With your new code in the button you are calling dbobject_createFromObject. You should only call this command once in your program as it resets everything in your database object to the state stored on disk. Let's start by taking that out.

Well we have made some progress as your SQL Query is not being created properly though is the table name really "product" in the array or is it "products"?

I don't think the error you are seeing can be the same one. Line 1610 in version 0.9.3 build 3 wouldn't generate an error as it is a return statement. Can you tell me what the new error is?

One last diagnostic you can run is to show me the entire database object array.

Code: Select all

put dbobject_getArray() into theA

put printkeys(theA)

At this point you could also try starting over with your database object. Just issue "dbobject_delete" in the message box. You can then go through the instructions in the manual and set everything back up. Let's see what the error is first though before we go down that road.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:01 pm

Hi Trevor,

I thinking that every time that I need query I need load the dbobject and connect it. In your sample you perform a dbconnect before it. Now I delete it and the query don't report any error but the data aren't loaded into the datagrid.

I see the values if I perfom a printkeys but the datagrid not load it. Only insert blank rows.

set the dgData of group "dg_test" to theArrayA

The correct name for the table is "product".

Where I can specify the columns that I want retrieve?

Your last diagnostic show me the full data for the tables, fields, etc.. it's more big, what you want to see. To paste it.

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:01 pm

By default the query will select all columns in your table. You can see the query that is executed as follows:

Code: Select all

put sqlquery_get(theQueryA, "query")
If you want to set the fields you can set the "select clause" property using sqlquery_set:

Code: Select all

sqlquery_set theQueryA, "select clause", "field1, field2, field3"
Here is a link to the docs for sqlquery_set:

http://www.bluemangolearning.com/revolu ... ry_set.htm

If you are seeing empty rows appear in your data grid then most likely you don't have columns in the data grid that match the columns being retrieved from the database. You can always see what the array that sqlquery_retrieveAsArray creates using printkeys:

Code: Select all

printkeys(theArrayA)
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:02 pm

Hi Trevor,

Yes, now is working!

Questions:

1. I lost the connection with the mySQL and I get a message box with the problem, I click again and the query connect correctly. How can change this message? For example translate to spanish. If you want I can help to translate or revise the translations.

2. Using arrays ever the datagrid must have the exactly column names to mach it? Some use like dgText to fill auto the datagrid?

3. While testing with Yoga, I loaded the sample application. After load it my database object don't work more until I initialize again. Then sample application doesn't work.

How can I do to work with many dbobjects?

4. Its posible to convert html tags to wiew as html text in the datagrid and save it as html tags from the datagrid?

--- value in the database

<div>Just when you thought iMac had everything, now there&rsquo;s even more.

---

I want to see as normal text, change it and save to the database with html tags to view correctly from the web. I remember read something about but I don't remember where. Any idea are wellcome.

Salut,

Josep
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: SQL Yoga Query

Post by trevordevore » Fri Oct 11, 2013 3:03 pm

Yes, now is working! "
Excellent!
1. I lost the connection with the mySQL and I get a message box with the problem, I click again and the query connect correctly. How can change this message? For example translate to spanish. If you want I can help to translate or revise the translations."
I have no plans on translating the error messages reported by SQL Yoga as they are not intended to be displayed to the user. They are intended to inform the developer of the error. If you would like to display errors to your users then you should provide your own suitable message with translations.
2. Using arrays ever the datagrid must have the exactly column names to mach it? Some use like dgText to fill auto the datagrid?"
A data grid always requires a column name that matches keys in the internal array. When you set the dgData to an array you are explicitly setting the internal array. When setting the dgText and you don't provide column names then the data grid is creating those column names and internal array keys for you (col 1, col 2, etc.). Using dgData is faster as no translation has to be done. I would recommend setting up your data grids ahead of time with the proper column names and labels and then setting the dgData.
3. While testing with Yoga, I loaded the sample application. After load it my database object don't work more until I initialize again. Then sample application doesn't work.
How can I do to work with many dbobjects?"
SQL Yoga does support multiple database objects. All SQL Yoga calls assume you are using the database object named "default" which saves you from having to type "default" in every time you make an API call. Since the sample application uses "default" and your application uses "default" there was a conflict.

I will update the sample application so that it uses a different name. For now just open the sample app in a separate instance of Revolution.
4. Its posible to convert html tags to wiew as html text in the datagrid and save it as html tags from the datagrid?"
The data grid manual discusses this.
What If I Need to Work With htmlText, rtfText or unicodeText?
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”