 |
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
 Posted: Mon Nov 21, 2011 2:44 am
SQL command/function inconsistencies
|
It seems that if you want to use select statements, you need to use #SQLDB to open a connection, then %sql to run queries on it. If you want to use statements like insert or update, you need to use %sqldb to store a connection in a variable, then #call the execute method on it. Past posts by Zugg seem to confirm this. What do you do if you want to do both? As far as I can tell, you need to open two connections, one with #SQLDB and one with %sqldb. Seems like a lot of extra work for nothing, not to mention extra server load. Is there another way? It seems like the whole sql system could be cleaned up a bit, and the documentation could be clearer too...
|
|
|
|
|
ins0mnia
Novice
Joined: 23 Jan 2011
Posts: 42
Location: United States
|
Posted: Mon Nov 21, 2011 7:37 am
|
Agreed that it's a bit confusing, but you only need to do it once, with either function.
#SQLDB and %sqldb followed by #CALL db.Open, are equivalent. %sqldb just gives you the connection reference needed for using #Call.
%sql and #Call Execute are similar too, but %sql gives you reference to the results. Note that you have to Post after an Insert when using %sql.
The example on the bottom of %sqldb shows a Select query with %sql after making connection with %sqldb, instead of #SQLDB. |
|
|
|
|
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
Posted: Tue Nov 22, 2011 2:35 am
|
You haven't said anything I didn't already know, though. Is it possible to insert with %sql? I'm fairly certain it's not, since it gives an access violation every time I try. In fact, Zugg has said in the past that %sql is only for queries that return results, i.e. selects. On the other hand, is it possible to use select queries using a connection variable created with %sqldb? Again, I don't think it is. In fact, the documentation says that the execute method should only be used for queries that don't return anything, and to use %sql instead for those queries. So again, how do you do both select and insert queries on the same connection?
|
|
|
|
|
MattLofton
GURU
Joined: 23 Dec 2000
Posts: 4834
Location: USA
|
Posted: Tue Nov 22, 2011 4:15 am
|
| Quote: |
On the other hand, is it possible to use select queries using a connection variable created with %sqldb?
|
Both %sql() and .execute() only work on database connection names, not on the variable references. It really doesn't matter HOW the connection was made, except that .execute() has a THIS reference that seamlessly derives the appropriate data from the COM object.
$var = %sql(AardwolfData,"select * from ...")
#call @dbAardwolfData.execute("insert ...")
So, essentially, yes it is possible to use select queries using a connection variable created with %sqldb(). It's probably possible to use .execute() with the db connection name, too, though I didn't try that one. |
|
_________________
EDIT: I didn't like my old signature |
 |
|
|
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
Posted: Tue Nov 22, 2011 5:13 am
|
Ah, you're right. I missed that %sqldb allows you to set the name of the connection to be used with %sql, not just create a connection object to be stored in a variable. Thanks!
|
|
|
|
|
ins0mnia
Novice
Joined: 23 Jan 2011
Posts: 42
Location: United States
|
Posted: Tue Nov 22, 2011 6:53 am
|
According to the help file, it should be possible to do -any- query with %sql.
I haven't tested it in a few months but I think I had access violations also, so something's up with it. Or the file is outdated. |
|
|
|
|
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
Posted: Tue Nov 22, 2011 12:44 pm
|
The documentation for %sqldb says:
| Quote: |
| Execute(sql) : execute the given SQL statement. Should be used for SQL statements that do not return a result. Use the %sql function for queries that return results. |
Of course, that doesn't necessarily mean that the reverse is true (that %sql can't be used for queries that do not return a result), but according to Zugg here:
| Zugg wrote: |
To use SQL commands that do not return any query results, you need to use the Execute method of the actual database connection and not the %sql function. Use %sqldb to open a database connection and store the object in a variable, then use the Execute method. Something like this:
| Code: |
db = %sqldb("MyDb.db")
#call @db.Execute("INSERT INTO table (column1, column2, ...) VALUES (value1, value2...)") |
|
Seeing as I get an access violation when I try to insert with %sql, I think it's safe to say that %sql can only be used with queries that return results, and the documentation could be updated to reflect this. |
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
