Nuther sqLite gotcha

[Peter Haworth]

OK, I knew about the SHOW CREATE TABLE command, but thought perhaps MySQL had the equivalent of the SQLite sqlite_master table 'table' entries.

Pete Haworth

On Mar 23, 2011, at 3:59 PM, Bob Sneidar wrote:

> You would use SHOW CREATE TABLE devices (assuming your table was called "devices"). 
> 
> For sqLite I use: 
> 
> put "SELECT name,sql FROM sqlite_master WHERE type='table' " into theSQL
> 
> then I execute that. It's the only way I know of as sqLite does not have a SHOW CREATE TABLE context. 
> 
> Bob
> 
> 
> On Mar 23, 2011, at 3:39 PM, Peter Haworth wrote:
> 
>> Hadn't noticed that.  I'm pretty sure I've created all my SQLite tables through the FireFox SQLite Manager and it looks like it puts quotes around all the table names  (and column names).  Out of interest, where does mySQL store the SQL statements to create tables? (still finding my way around mySQL!)
>> 
>> Pete Haworth
>> 
>> 
>> On Mar 23, 2011, at 3:15 PM, Bob Sneidar wrote:
>> 
>>> Hi all. 
>>> 
>>> If anyone ever wants to get  the SQL for creating an existing table in sqLite by querying the sqLite_master table for table entries, be aware that whoever created the table may or may not have enclosed the table name in quotes! So if you are doing a line offset to find let's say "CREATE TABLE devices" and the person who created the table used quotes you will not find that line! Apparently, sqLite records the exact sequel used to create the table, not the sanitized SQL that would be used to do it, like in mySQL. (but I could be wrong about mySQL being sanitized). 
>>> 
>>> You may say, "Why not just search for the table name and be done with it?" Because there may be a column definition in there with all or part of the column name being the table name you are looking for. 
>>> 
>>> I'm sure this is quite obscure, but it just bit me in the buttocks so I thought I'd put that out there. 
>>> 
>>> Bob
>>> 
>>> 
>>> 
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>> 
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>