Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

MySQL Library functions


  • Please log in to reply
66 replies to this topic
jcrashmiller
  • Guests
  • Last active:
  • Joined: --
One more request. I can't figure out how to use " " in a query, as your library examples have the whole query surrounded in " ". Is there a way to escape them?

Essentially, I'm trying to set the DATE (or DATE_TIME) to all zeros, and in another script, set it NOW(). The tutorials I've seen say to use double quotes around the value to be set. But, that appears to cut off the query since the whole thing is in double quotes. Thanks for the point in the right direction.

mysql.query(db, "UPDATE official_games_db SET last_played = "0000-00-00" WHERE uninstaller_loc = '" uninstaller1 "'")

I've confirmed the command works when entered into the MySQL console. The double quotes around the zeros seem critical.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
You can use single quotes in substitution of double quotes.

mysql.query(db, "UPDATE official_games_db SET last_played = '0000-00-00' WHERE uninstaller_loc = '" uninstaller1 "'")

I prefer this style for increased legibility, but I do use the above style for short queries.

sql =
(
  UPDATE official_games_db 
       SET last_played = '0000-00-00'
   WHERE uninstaller_loc = '%uninstaller1%'
)

mysql.query(db, sql)


jcrashmiller
  • Guests
  • Last active:
  • Joined: --
Thanks panofish.

This was the last piece of the puzzle for me for my project. For whatever reason, it wasn't working with the uninstaller_loc variable. No errors, just that the system wouldn't update. Since I have a primary key as well, I simply pulled it, associated the right one with the right uninstaller, and then based the query on the id and all worked well.

Thanks, again.

J. CrasH Miller

PS -- Seriously, if it weren't for this library, my project would not have been possible. Thanks. openextreme.wordpress.com if you're curious. It's just a blog. The real site is still under construction and has yet to be launched.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Great! Cool site... I will have to read more detail since I have an arduino I haven't played with yet.

jcrashmiller
  • Guests
  • Last active:
  • Joined: --
OK, so, I'm back. Now it's about database management. I figured out how to drop a table from within AHK. The trouble now is importing it. The code below selects an .sql file, then drops a table, then imports the .sql file in place of the dropped table. However, I can't get the import to work. I've tried typing out the path, using the variable, using single quotes, double quotes, both, neither, the '\' character to escape the '\'s in a Windows file name, etc. Still no joy. Anyone have any thoughts on how to import a .sql file via AHK and this awesome MySQL library? Thanks in advance.

#include C:\Program Files\AutoHotKey\Lib\mysql.ahk
db := mysql.connect("localhost","crash","CAFRough04","openextreme")

FileSelectFile, SelectedFile, 3, , Select the SQL file for importing into the database, SQL files (*.sql)
if SelectedFile =
    MsgBox, You are bailing out without selecting a file.  Fine with me.
else
    {
	query1 = 
	(
	DROP TABLE official_games_db
	)
	mysql.query(db, query1)
	
	query2 = 
	(
	openextreme < %SelectedFile%
	)
	
	mysql.query(db, query2)
	MsgBox, The queries appear to have completed.
	}

;I've tried the line below too.  No joy.  But for all I know, I've got two variables wrong to deal with.	
;mysql openextreme < %SelectedFile%	
	
ExitApp


panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
Try using fileread to read the sql file into a variable and then passing that variable to the query command.

Also, if you haven't been using it yet ... I recommend getting the free heidisql which is fantastic for managing sql databases.

jcrashmiller
  • Guests
  • Last active:
  • Joined: --
Thanks panofish. Unfortunately, while a brilliant idea, the FileRead trick didn't change anything. I like heidisql from what I've seen of it; thanks for introducing me to it. However, the goal was to make this dead simple for end users. Double-Click the script, select the file, and that's it. The script automatically drops the old table and imports this new one in its place. Heidisql and phpMyAdmin are both more complex than I'd like for my end result.

Beyond this, the next step would be to make it completely automatic (finding the file on its own by looking in a very specified place for it), dropping the table and importing the new table. User could then put this on a schedule to run automatically 1x per day or every 6 hours or whatever.

So, with all that said, while I am ready to resign myself to the idea of using phpMyAdmin or Heidisql for updating the database with new entries, if there is another way, a quick and easy way, like with AHK, I'd love to hear about it. Unfortunately, I have yet to find it.

Thanks,

jcrashmiller

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
The heidisql suggestion was for admin purposes.
Can you post your sql source?

jcrashmiller
  • Guests
  • Last active:
  • Joined: --
openextreme < %SelectedFile%

That's all I'm trying to run at this point, as the DROP command works as does selecting the file. I just want to import an sql table file into the database 'openextreme.'

I asked about this at StackOverflow, as a simple, three-part statement shouldn't have any syntax errors, but it apparently does. I've tried running it from the command line, from phpMyAdmin, and from AutoHotKey and it gives me syntax error every time.

I've also tried using the absolute path in place of the variable name, still no joy.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
I am not familiar with that syntax.

You should try heidisql... it can generate the proper syntax for exporting, importing, backing up and such for an sql database and tables.
Then you could run that same sql through autohotkey and it should work without any modification.

Paradox
  • Members
  • 24 posts
  • Last active: Mar 05 2013 04:15 PM
  • Joined: 05 Feb 2010

I keep external scripts in their own subdirectories (mysql, in this case) and found that the libmysql.dll file has to be in the main directory to be copied to the Application Data directory.

 

I also see that you think everyone uses the default port... I don't... so, having the port as another argument would be a good idea...



Martinirenes
  • Members
  • 26 posts
  • Last active: Jul 18 2016 07:35 PM
  • Joined: 03 Aug 2012

am getting mysql error:2003 that i dont understand, id there some easy installation script?



DJAnonimo
  • Members
  • 171 posts
  • Last active: Apr 30 2013 01:20 AM
  • Joined: 10 Sep 2006

You mysql.ahk still do not work with UNICODE...

 

No way to modify for UNICODE too ?

Really a simple usage, i want to use it in my script :/

 

 

---------------------------
MySQL Error: 2005
---------------------------
湕湫睯祍兓⁌敳癲牥栠獯⁴氧‧〨)
 
 
---------------------------
OK   
---------------------------


deanantum
  • Members
  • 7 posts
  • Last active: Nov 11 2013 10:05 PM
  • Joined: 28 Sep 2012

I am having issues connecting to the DB from a remote system.  If I am on the system that runs the database, I was able to use the IP address, hostname just fine, shown below, but if I use a different computer remotely accessing the database, the IP address or the host will not work.  The server and the remote computer is in a corp network.  I do have the admin access to the DB.  Any tips on why it will not work?  

 

- I do have the mysql.AHK in the local directory.

- OS Windows 7

- AHK Version 1.1.09.04

 

db := mysql.connect("hostname","root","password","test")

db := mysql.connect("IP_Address","root","password","test")

 

******

 

#include mysql.ahk
 
F9::
{
ReadRequest()
}
return
;**********************************************************************************************************
ReadRequest(){
 
Msgbox, triggered Database
 
mysql := new mysql ; instantiates mysql object
 
db := mysql.connect("hostname","root","password","test")   ; host,user,password,database
 
if db
      msgbox, mySQL worked
if mysql.error
      msgbox, no mySQL
 
sql = 
(
SELECT Name
FROM test
WHERE Name = "Hans"
)
 
firstname := mysql.query(db, sql)
 
Msgbox, First Name: %firstname%
}
return


tank
  • Administrators
  • 4345 posts
  • AutoHotkey Foundation
  • Last active: May 02 2019 09:16 PM
  • Joined: 21 Dec 2007

can you connect by IP from any other software?

you might make sure that root user has other than @localhost


Never lose.
WIN or LEARN.