Zugg Software :: View topic

Newbie Joined: 20 Jun 2011 Posts: 8

#call %sql(INSERT INTO eq (asd1) values(2))

this is what I type, there's a db that I created with #sqldb eqsql2, eq is a table and asd1 is a field (column) which I created in some sqlite viewer. So how can I add info, it doesn't seem to work and the help file examples are too few.

Posted: Tue Jun 21, 2011 10:48 pm

Make sure to put " quotes around the argument to %sql. That's extra important when using stuff that contains it's own special characters like ()

So try this:

Newbie Joined: 20 Jun 2011 Posts: 8

I type
#sqldb test1
It creates a db called test1 in d:\cmud sessions\sessionname
Then if I type:
#call %sql(test1, “insert into eq (asd1) values (asd2)”)
The error message is: SQL logic error or missing database.

If I type:
#call %sql("insert into eq (asd1) values (asd2)")

It doesn’t output anything.
There is no table eq or field asd1 in it, however when I add those with external viewer, nothing gets added either.

Also, the help file says to use POST method, but I can't find the syntax :(

Sorcerer Joined: 15 Apr 2011 Posts: 809

I have to agree, the documentation for all the sql commands/functions are rather terrible. I believe you can't use insert queries like that, Iliev, only select queries. I'm not sure though, since the documention doesn't say. I think you need to use the %sql function's insert method to insert records into your database, but I'm not positive, since the documentation says nothing about it. Unfortunately, I couldn't figure out how to use that method, since it has absolutely zero documentation, beyond a description. Are we supposed to figure out how to use these (relatively) new features by trial and error?

BTW, if someone else had more luck with their trial and error than I did, and actually knows how to use more than three of the 21 properties/methods of the %sql function, please do post, with examples preferably (I say more than three because there is only one example given, and it only uses three of the properties/methods. In fact, all four sql-related commands/functions have that same, single, very basic example in the documentation...).

Posted: Thu Jun 23, 2011 1:26 am

Sorcerer Joined: 15 Apr 2011 Posts: 809

Well, what do you know, it worked perfectly! Why can't the official documentation be this clear?

Posted: Thu Jun 23, 2011 2:12 am

Edit: Ninja'd... that's what happens when you start a post and forget about it an hour or two later.

The key step you seem to be missing is creating the table 'eq'. The bad SQL logic comes from you trying to insert info into a non-existent table. I created the table and the INSERT worked just fine for me (meaning it popped up no errors).

As for the POST thing, I've no clue what it's referring to, either.

Sorcerer Joined: 15 Apr 2011 Posts: 809

It's referring to the %sql().Post() method. As far as I can tell, it writes data back to the database after changes have been made with other methods, like insert. Unfortunately, since the documentation is missing so much crucial information, like the syntax to use these methods, I never did figure out how to use them properly. But it turns out you can use insert SQL queries, so using pure SQL is probably the easiest way to manipulate your databases, rather than the cmud %sql function's methods, since SQL is well-documented on the internet.

Posted: Thu Jun 23, 2011 2:30 am

I think you can ignore that suggestion of the Post method for the time being. The code assumed that you were doing multiple edits to the table therefore the (erroneous) assumption was that a BEGIN TRANSACTION was hanging about. THE POST/COMMIT would have caused the changes to the table to become actualized. However, since all came about due to the fact that the table didn't exist to begin with, I doubt it is applicable.

Sorcerer Joined: 15 Apr 2011 Posts: 809

Why do you say the table didn't exist? It did, the only problem with Iliev's code was some missing quotes around his string.

Posted: Thu Jun 23, 2011 2:40 am

Edit: Didn't see the table creation with a sql viewer. Not sure then why I can do it without quotes. :p

Newbie Joined: 20 Jun 2011 Posts: 8

I'm not sure what's going on, it works now. Including my own example that I posted last night, and which I tried like 30 times and I'm sure it wasn't working.

The field is a number and my example value of asd should be wrong, but it's not what caused the error.

When no such table called eq, there's a message saying there isn't a table.

When I try adding asd as a value, and without ' ' as Anaristos said, there's a different error message (not the logic / missing db one)

Also, when there's no such field, there's a corresponding error as well.

So I'm not sure what the problem was. :P

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

I've tried to use this syntax but I keep getting an error. Looking at everyones examples I don't see what I am doing differently. Maybe someone will notice something.

Posted: Thu Jun 23, 2011 7:33 pm

Well, I'm not about to try and document all of SQL here. But the INSERT command has the format:

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

I went ahead and tried your way and I still get the same error.

Sorcerer Joined: 15 Apr 2011 Posts: 809

I'm not asking you to document SQL, I actually stated above that it's already well-documented on the internet. I'm just asking you to document cmud's sql commands/functions.

Posted: Fri Jun 24, 2011 8:32 pm

Documentation already exists: #SQLDB %sql %sqldb

Also, anybody who has a forum account can add a comment to any documentation article to add examples or additional help. So if you see something that needs to be clarified, add it and it will eventually be incorporated into the main doc file.

But back to the original problem. Looking at the ZeosLib code, it looks like %sql is made for queries that return an actual dataset (like SELECT queries). For queries that do not return a dataset, open your database using the %sqldb function so that you have the connection object, then use the Execute property. For example:

Sorcerer Joined: 15 Apr 2011 Posts: 809

Sure, the documentation exists, but it's pretty much useless in its current state. The documentation for the %sql function lists a bunch of methods, but no syntax for those methods... as I stated above, are we just supposed to guess the syntax?

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

That worked perfectly. Thank you so much.

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

One more quick thing... I have these two aliases dcquery & dcadd respectively

Sorcerer Joined: 15 Apr 2011 Posts: 809

You're not closing the database at the end of your dcadd alias. Not sure, but I suspect that might be the problem.

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

I'm not exactly sure what the correct syntax is, but I have tried

Sorcerer Joined: 15 Apr 2011 Posts: 809

The #SQLCLOSE command...you do it in your dcquery alias...

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

using #sqlclose gives me an invalid pointer on the dcadd alias

Novice Joined: 17 Sep 2007 Posts: 44 Location: Missouri

I've been throwing some things at this for the last few days and haven't had any luck. If anyone else has any suggestions I'd appreciate them.