on-rev queries

[Trevor DeVore]

On Feb 2, 2010, at 7:53 PM, Bob Sneidar wrote:

> Okay, for anyone who is interested, the trick to adding and dropping  
> tables (or executing any odd sql) when using sqlYoga is that you  
> have to tell sqlYoga that the schema has changed.

The db schema that SQL Yoga caches shouldn't affect running random SQL  
commands. The schema is used when you work with SQL Query and SQL  
Record objects.

> The easiest way to do that is reset the schema with dbschema_reset,  
> then reconnect to the database. JUST reconnecting will NOT reload  
> the schema causing you all kinds of headaches in the future.
>
>    dbschema_reset thePriDB
>    dbconn_disconnect thePriDB
>    dbconn_Connect thePriDB

You should use dbobject_reloadSchema for this.

http://www.bluemangolearning.com/revolution/docs/sql_yoga/api_docs/Documents/stack_libSQLYoga_command_dbobject_reloadSchema.htm

You can find more info about the schema in the lesson "Introduction to  
the Database Object and Database Schema":

http://revolution.screenstepslive.com/spaces/revolution/manuals/sqlyoga/lessons/6870-Introduction-to-the-Database-Object-and-Database-Schema

> Also, any queries you use with sqlYoga will AUTOMATICALLY reconnect  
> to the database.

SQL Yoga looks for the MySQL has gone away error and tries to  
reconnect to the database automatically before returning an error.

> BUT if you are using the REV IDE to execute what I will call  
> "foreign" queries, that is queries not run through sqlYoga, AND your  
> connection has timed out, you will have problems, SO...
> I now use dbconn_connect before executing "foreign" queries.
>
>    put "DROP TABLE 'myhostname'.'mytablename'" into theSQL --  
> foreign query
>    dbconn_Connect thePriDB -- make sure we are currently connected
>    put dbconn_get("connection id", theConnection, theDBObject) into  
> theID -- get the current dbid
>    revExecuteSQL theID, theSQL -- shoot the wild ass (just checking  
> to see if you are reading the comments)

As I mentioned in the previous email you can use dbconn_executeSQL  
here which is easier.

>    -- now reload the schema
>    dbschema_reset thePriDB -- wipes the database object clean
>    dbconn_disconnect thePriDB -- may not need to do this...
>    dbconn_Connect thePriDB  -- this will reload the schema
>    dbobject_save thePriDB -- ALWAYS SAVE YOUR OBJECTS!

Your code here can be:

dbobject_reloadSchema thePriDB
dbobject_save thePriDB -- ALWAYS SAVE YOUR OBJECTS!

Note that SQL Yoga is going to query your database again to get all of  
the table information. This means you should do this after finishing  
all alterations to your database.

-- 
Trevor DeVore
Blue Mango Learning Systems
ScreenSteps: http://www.screensteps.com
Releasable Revolution Resources for Developers: http://revolution.bluemangolearning.com