 |
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
 Posted: Fri Jun 17, 2011 1:45 pm
Cmud & MySQL
|
I was wondering if someone wouldn't mind taking a little time and help me get started with this. I have tinkered with it on my own for a few hours and haven't made much progress. So I have a remote mysql server and I'm using
| Code: |
| #SQLDB mydb dbname mysql hostname.com 3306 username passwd |
and when I do, I don't receive any sort of message whether or not it has connected. I've put in fake hostnames and tried to connect and I don't receive any sort of error. But anyhow it seems to connect me because I have been successful in adding data to my remote database using...
| Code: |
| %sql (myavdb, "INSERT INTO gear VALUES ('hit gear','something','something else','wield','5hr 5dr')") |
This throws and error "Can not open a ResultSet" but still adds the data.
I am not able to return data from my database using select. When I query the database and #SHOW it just says <comobject>
Like I said, any help just getting started will be appreciated. Once I figure out the correct syntax etc I should be able to take over.
Thanks again |
|
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Fri Jun 17, 2011 2:25 pm
|
How are you "using" that line? If you are just executing that line as you have written it, the problem is that you are starting a command line with a %function. What it is doing is executing the function, and then trying to execute the result as a command. What you need to do is either a #CALL:
or set the result to a value:
| Code: |
| $sqlresult = %sql... |
Basically, never start a command line with a function. Depending on what you intend to do, you should instead start it with #CALL, #SEND, #EXEC, #SAY, or something else like that. |
|
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Fri Jun 17, 2011 2:57 pm
|
Thanks for the reply.
Sorry I was using #exec.
| Code: |
| #exec %sql(myavdb, "INSERT INTO gear VALUES ('hit gear','something','something else','wield','5hr 5dr')") |
Using #exec #call etc all give me the error about not being able to open a Resultset.
Also, how would I go about getting values. I tried
| Code: |
| #show %sql(myavdb, "Select * from gear where type = 'wield'") |
That just shows <COMobject>
Thanks |
|
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Fri Jun 17, 2011 6:56 pm
|
Well, #EXEC means "interpret the following, and then execute the result as a command". That's not what you want to do either. That's why I said that the command you want to use depends on what you are trying to do. #CALL should execute the function but do nothing with the result. Are you certain you got that error from #CALL? You didn't get it from #SHOW, so it looks to me like #CALL should not have given an error either.
For getting values, you need to set the result to a variable:
| Code: |
| $sqlresult = %sql(...) |
The variable becomes (as you have seen) a COMobject. A COMobject has a set of properties and methods that can be executed on it, using a dot notation--see the help page for %sql() to see what properties and methods are defined for that particular return value: http://forums.zuggsoft.com/modules/mx_kb/kb.php?mode=doc&k=3033
To display the value of the second row, third column, for instance, you could do:
| Code: |
$sqlresult = %sql(...) // executes the query and stores the result in a variable
#CALL $sqlresult.First // sets the current row of the variable to the first row
#CALL $sqlresult.Next // sets the current row of the variable to the next (ie. 2nd) row
#SHOW $sqlresult.Item(3) // displays the value of the third column of the current row
|
[edit]Actually, that gets you the value of the fourth column, because it is zero-based[/edit] |
|
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Fri Jun 17, 2011 8:09 pm
|
Wanted to say thanks, The retrieving data is working great. However I still receive the error when I try to insert data. I double checked and made sure I was using #call
| Code: |
| #call %sql(myavdb, "INSERT INTO gear VALUES ('hit gear','something','something else','wield','5hr 5dr')") |
The message reads:
| Quote: |
Error parsing command
Can not open a ResultSet
#call %sql(myavdb, "INSERT INTO gear VALUES ('hit gear','something','something else','wield','5hr 5dr')") |
Thanks again for the help |
|
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Sun Jun 19, 2011 4:39 pm
|
Hm. I haven't played with editing using the %sql functions, though I'm about to start a project involving that. I _think_ you need to make the edits in the COMobject, using .Item or .Edit methods, and then use the .Post method to get that back into the database. But I haven't worked with that to see how it is done yet.
|
|
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
|
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Mon Jun 27, 2011 4:56 pm
|
one more quick thing(hopefully quick)
I want to do a select based off a variable. The code works if I replace @type with an actual value.
| Code: |
| row = %sql(myavdb, "SELECT * FROM gear where type= '@type'") |
but that doesn't work. It doesn't seem to be expanding the variable. so I tried
| Code: |
| #show %concat("%sql",~(myavdb"," ~"SELECT * FROM gear where type= ',@type,'~"~)) |
it looks great and expands the variable but
| Code: |
| row = %concat("%sql",~(myavdb"," ~"SELECT * FROM gear where type= ',@type,'~"~)) |
does not work. |
|
|
|
|
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
Posted: Mon Jun 27, 2011 5:01 pm
|
The problem is that your code:
| Code: |
| %concat("%sql",~(myavdb"," ~"SELECT * FROM gear where type= ',@type,'~"~)) |
just returns a string. Sure, when you #show it it looks great, but assigning it to the row variable just assigns that string to the variable, it doesn't execute the %sql function. This should work:
| Code: |
| row = %sql(myavdb, %concat("SELECT * FROM gear where type= '",@type,"'")) |
|
|
|
|
|
|
|
|
|
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
|
|
