SQL and other databases

[Bob Sneidar]

Hi Peter. 

Well I don't have a complete listing because I am finding them out as I go. My current project gets the SQL to create a table from the sqlite_master table, then massages it to create the same table in mySQL. MySQL uses SELECT CREATE TABLE to get this information. 

One gotcha is that sqLite allows enumeration (is that the right word??) in their datetime column (although why I cannot tell). That is you can declare datetime(30) but you cannot do this in mySQL. 

I have also discovered that sqLite allows you to declare a default value for a char type column without using NOT NULL. mySQL requires NOT NULL if you declare a default. You cannot have a default for a TEXT type in mySQL (if the manual is correct). 

I am not sure but I don't think sqLite uses what I call sequel quotes the ` character (shift-tilde). 

Someone a while back posted a link to a web page that described a number of things you could NOT do in adding columns to an existing sqLite table. The consensus was that it was better to simply dump the data and create the new table, and then re-import the data. For instance you can insert a column between existing columns in mySQL. You cannot in sqLite. 

sqLite is NOT multiuser, in the sense that you cannot have 2 read/write connections at one time. Although you CAN have many read only connections, only one can be read/write. 

In your case, going from sqLite to mySQL, I don't think you will have any problems with your actual data queries. Select statements are pretty standard (although I have been told by my web dev friend that MS SQL differs significantly in their query standards. What a surprise!) 

Bob


On Mar 21, 2011, at 9:34 AM, Peter Haworth wrote:

> Bob,
> I'm about to make the leap from sqlite to mySQL so I'm very interested in the differences you mentioned.  I think I've seen some of them in posts on the list here but any chance you could summarise them?  I guess I am expecting some functional improvements in mySQL over sqlite since sqlite advertises itself as a subset of the sql standards but it sounds like you've found some syntactical differences as well.
> 
> Pete Haworth
> 
> On Mar 21, 2011, at 9:19 AM, Bob Sneidar wrote:
> 
>> I have encountered a number of differences in different engines relating to syntax, when to use regular quotes, when to use sequel quotes, what column types can or cannot be enumerated, etc. 
> 
> 
> _______________________________________________
> 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