Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

MySQL UDFs


  • Please log in to reply
374 replies to this topic

#241 JonnyThunder

JonnyThunder

    Adventurer

  • Active Members
  • PipPip
  • 107 posts

Posted 21 April 2008 - 08:23 AM

Ello!

I think the UDF is great, but a little overcomplicated. Take the PHP implementation of mySQL for instance - although there are a stack of mySQL commands - you'd only use a handful of them....

mysql_connect
mysql_query
mysql_fetch_array
mysql_close

At a push, you may also use these too...

mysql_insert_id
mysql_num_rows
mysql_affected_rows

Like I said, I like your UDF - but I'd personally limit it to only the commands that are needed to perform operations. For instance, there's no real need for a delete function when this can be acheived with a query command.







#242 barribar

barribar

    Seeker

  • New Members
  • 4 posts

Posted 21 April 2008 - 01:53 PM

well:
0. MAKE SURE THAT YOU HAVE INSTALLED THE ODBC DRIVER
1. make sure that the login data is correct...
2. make sure that the mysql server allows your pc to connect to it
3. make sure your mysql user is allowed remote connections
4. make sure that `mytable` and `user` really exist

that's about it :D

all of this are correct for me
any thing else?
sorry for my bad language
thanx a lot

#243 weaponx

weaponx

    I'm coming for blood, no code of conduct, no law.

  • MVPs
  • 5,366 posts

Posted 24 April 2008 - 07:48 PM

EDIT: Problem is with MySQL server version. Error exists in 4.1.13, it works fine in 5.0.45.

_GetColNames is not working, an empty array is returned with the correct column count and number of elements. I also tried a manual query to get the column names using _MySQLExec. The table exists and the query was tested in MySQL Query Browser. Here is my code:

AutoIt         
#include "mysql.au3" #include <array.au3> ;username, password, db, address $SQLOBJ = _MySQLConnect("user","pass","192.168.1.10","housing_energy") Switch @ERROR     Case 0         ;MsgBox(0,"","Connection successful")     Case 1         MsgBox(0,"","Error","Error opening connection")     Case 2         MsgBox(0,"","MySQL ODBC Driver not installed.") EndSwitch ;Retrieve array of all table names in database $aTables = _GetTblNames($SQLOBJ) If NOT @ERROR Then     ;Dump array to console and show table count     ConsoleWrite("########## TABLES (" & _CountTables($SQLOBJ) & ") ##########" & @CRLF)     For $X = 1 to $aTables[0]         ConsoleWrite($aTables[$X] & @CRLF)     Next Else     MsgBox(0,"Error","Something Went Wrong!") EndIf ConsoleWrite(@CRLF) ;Retrieve array of all column names in table $ret2 = _GetColNames($SQLOBJ, "hwap_dcf4") If NOT @ERROR Then     ;Dump array to console and show column count     ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "hwap_dcf4") & ")##########" & @CRLF)     For $X = 1 to $ret2[0]         ConsoleWrite($ret2[$X] & @CRLF)     Next Else     MsgBox(0,"Error","Something Went Wrong!") EndIf ;Retrieve array of all column names in table $var = _MySQLExec($SQLOBJ, "SHOW COLUMNS FROM hwap_dcf4;") If NOT @ERROR Then     ;Dump column names to console and show column count     ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "hwap_dcf4") & ")##########" & @CRLF)         With $var         While NOT .EOF; is equal to "Do this until we run out of records"             ConsoleWrite(.Fields('Field').value & @CRLF)             .MoveNext;Move to the next row         WEnd     EndWith Else     MsgBox(0,"Error","Something Went Wrong!") EndIf _MySQLEnd($SQLOBJ)


EDIT: I did some digging and it looks like the same problem is shown here:
http://bugs.mysql.com/bug.php?id=27409
The value isn't actually being returned empty, its just binary encoded. One solution says to add this to the connection string:
charset=utf8;

Unfortunately this did not work for me.

Edited by weaponx, 24 April 2008 - 08:34 PM.


#244 aNewLyfe

aNewLyfe

    Adventurer

  • Active Members
  • PipPip
  • 129 posts

Posted 28 May 2008 - 12:04 AM

hi, i have this problem and i dont know much about mysql,

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Host 'My IP' is not allowed to connect to this MySQL server

i wrote user name, pass right dont know what is the problem :S
any suggestion ?

~ Every Living Thing is a Code Snippet of World Application ~

:alien:


#245 Xand3r

Xand3r

    Aka TheMadman

  • Active Members
  • PipPipPipPipPipPip
  • 454 posts

Posted 29 May 2008 - 06:06 AM

well:
0. MAKE SURE THAT YOU HAVE INSTALLED THE ODBC DRIVER
1. make sure that the login data is correct...
2. make sure that the mysql server allows your pc to connect to it
3. make sure your mysql user is allowed remote connections
4. make sure that `mytable` and `user` really exist

that's about it :)


hi, i have this problem and i dont know much about mysql,

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Host 'My IP' is not allowed to connect to this MySQL server

i wrote user name, pass right dont know what is the problem :S
any suggestion ?

2. make sure that the mysql server allows your pc to connect to it
your host is not allowed to connect to the server...
Only two things are infinite, the universe and human stupidity, and i'm not sure about the former -Alber EinsteinPractice makes perfect! but nobody's perfect so why practice at all?http://forum.ambrozie.ro

#246 aNewLyfe

aNewLyfe

    Adventurer

  • Active Members
  • PipPip
  • 129 posts

Posted 04 June 2008 - 08:50 PM

thanks for the answer madman.
now its working well on web, but another problem, i cant connect to localhost.

I am running Microsoft sql server.
when i try to connect localhost it says :

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '127.0.0.1' (10061)

any help ?

~ Every Living Thing is a Code Snippet of World Application ~

:alien:


#247 Jango

Jango

    Polymath

  • Active Members
  • PipPipPipPip
  • 202 posts

Posted 05 June 2008 - 09:32 AM

thanks for the answer madman.
now its working well on web, but another problem, i cant connect to localhost.

I am running Microsoft sql server.
when i try to connect localhost it says :

### COM Error ! Number: 80020009 ScriptLine: 27 Description:[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '127.0.0.1' (10061)

any help ?

This is NOT for Microsoft SQL Server but for MySQL. If you don't know what is MySQL do a google search like everyone.

By the way if i have author permission (if not i will remove), here is a modified version for Micro$oft SQL Server 2005

AutoIt         
#cs     Function Name:    _SQLConnect     Description:      Initiate a connection to a MySQL database.     Parameter(s):     $username - The username to connect to the database with.     $password - The password to connect to the database with. $Database - Database to connect to.     $server - The server your database is on.     $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}"     Requirement(s):   Autoit 3 with COM support     Return Value(s):  On success returns the connection object for subsequent functions. On failure returns 0 and sets @error     @Error = 1     Error opening connection     @Error = 2     MySQL ODBC Driver not installed.     Author(s):        cdkid     Modified :        jango #ce Func _SQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{SQL Native Client}")     Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)     Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)     If @error or $val = "" Then         SetError(2)         Return 0     EndIf     $ObjConn = ObjCreate("ADODB.Connection")     $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword)     If @error Then         SetError(1)         Return 0     Else         Return $ObjConn     EndIf EndFunc   ;==>_SQLConnect


#248 aNewLyfe

aNewLyfe

    Adventurer

  • Active Members
  • PipPip
  • 129 posts

Posted 05 June 2008 - 02:26 PM

right, right :)
thanks, my bad :)

~ Every Living Thing is a Code Snippet of World Application ~

:alien:


#249 dfHunter

dfHunter

    Seeker

  • Active Members
  • 12 posts

Posted 10 June 2008 - 01:08 PM

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

#250 Xand3r

Xand3r

    Aka TheMadman

  • Active Members
  • PipPipPipPipPipPip
  • 454 posts

Posted 11 June 2008 - 09:24 PM

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?

try to manually get a socket to the server and see what the server tells you :)
Only two things are infinite, the universe and human stupidity, and i'm not sure about the former -Alber EinsteinPractice makes perfect! but nobody's perfect so why practice at all?http://forum.ambrozie.ro

#251 Jango

Jango

    Polymath

  • Active Members
  • PipPipPipPip
  • 202 posts

Posted 12 June 2008 - 09:43 AM

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?



Use Objevent (see AutoIt Help file) there is an example

#252 Jango

Jango

    Polymath

  • Active Members
  • PipPipPipPip
  • 202 posts

Posted 12 June 2008 - 09:44 AM

if database is offline, script, when try to connect to it, crashes and stops, how can i make it to continue work?



Use ObjEvent (see AutoIt Help file) there is an example

#253 m0nk3yI3unz

m0nk3yI3unz

    Wayfarer

  • Active Members
  • Pip
  • 93 posts

Posted 02 July 2008 - 07:12 PM

hey cdkid - Again, thanks for these (not sure if ive posted in this thread before).

Quick question: I downloaded the ODBC 5.1.4 drivers a few minutes ago, but Im not sure how to implement them into your code. I noticed you can change the driver name in the connect sequence, but im not sure what to put.

Ill keep trying with different things, but in the event I dont find it, I need some help getting it to work :)


Thanks for everything!

-Monkeh

EDIT: WOW that was easy to find, actually - I just went into the connect function, went into the registry path that was in it, and found the driver info - it was:
{MySQL ODBC 5.1 Driver}

THANKS!!!

Edited by m0nk3yI3unz, 02 July 2008 - 07:33 PM.

Monkeh.

#254 weaponx

weaponx

    I'm coming for blood, no code of conduct, no law.

  • MVPs
  • 5,366 posts

Posted 02 July 2008 - 07:15 PM

Look at the function call:
Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 3306, $sDriver = "{MySQL ODBC 3.51 Driver}")

Just change $sDriver to the new version name.

#255 Mattraks

Mattraks

    Adventurer

  • Banned (NOT IN USE)
  • 117 posts

Posted 21 July 2008 - 08:21 PM

I always get this when i try to run it

C:\Program Files\AutoIt3\Include\MySQL.au3 (30) : ==> The requested action with this object has failed.: $ObjConn.open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort) $ObjConn.open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort)^ ERROR >Exit code: 1   Time: 2.728


Edit: I tried with the one posted and on the svn site and it always shows that

Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 3306, $sDriver = "{MySQL ODBC 3.51 Driver}")     Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)     Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)     If @error Or $val = "" Then         SetError(2)         Return 0     EndIf     $ObjConn = ObjCreate("ADODB.Connection")     $ObjConn.open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort)     If @error Then         SetError(1)         Return 0     Else         Return $ObjConn     EndIf EndFunc   ;==>_MySQLConnectƒo݊÷ Ù8Z¶Ëayû§rب©Ý¶¬ŠÈhÂ&¦q©eŠx"µ«­¢+Ù}5åME1 ½¹¹•Ð ™ÅÕ½ÐíÕ͕ɹ…µ”™ÅÕ½Ð찀™ÅÕ½ÐíÁ…ÍÍݽɐ™ÅÕ½Ð찀™ÅÕ½ÐìÄÈܸÀ¸À¸Ä™ÅÕ½Ð찀™ÅÕ½Ð흅µ”™ÅÕ½Ðì¤


And it still gives the error. Not sure why. Anyone want to help?

Edited by Mattraks, 21 July 2008 - 08:25 PM.


#256 Mattraks

Mattraks

    Adventurer

  • Banned (NOT IN USE)
  • 117 posts

Posted 21 July 2008 - 09:42 PM

Bump

Anyone?

#257 SiteMaze

SiteMaze

    Seeker

  • Active Members
  • 41 posts

Posted 30 July 2008 - 10:32 AM

Try the inet adress without http://.


I am inserting thousands of records to the database and need fast insertion.

One technique is to use Prepared Statements so that the mysql server can cache the sql queries.

Have anyone tried to use AutoIT with prepared statements?

#258 blenkhn

blenkhn

    Seeker

  • Active Members
  • 16 posts

Posted 13 August 2008 - 01:06 AM

I have been looking at your mysql udf and it looks great.

Is there a way to read the results of the query into a 2d array? Is there a code snippet or link I could see?

#259 theimer

theimer

    Seeker

  • New Members
  • 4 posts

Posted 21 September 2008 - 08:34 PM

Hello,
is there any way how to prevent of interupting of autoit script after error during _MySQLConnect?
I not use local MySQL DB, but DB on internet. I can test ping for DB server, but server can be ok, but MySQL servie can be down and in this case I get error (The requested action with this object has failed) and autoit script is terminated. I need to catch error and prevent terminating of autoit script.

Can anybody help me?

Thanks for any ideas
Libor

#260 mianz

mianz

    Seeker

  • Active Members
  • 25 posts

Posted 26 September 2008 - 09:43 AM

i'm using the udf from kan2.sytes.net/publicsvn/mysql and mysql-connector-odbc-noinstall-5.1.5-win32

i have change the mysql.au3

this line for version 5.1
-> Func _MySQLConnect($sUsername, $sPassword, $sServer, $sDatabase, $iPort = 3306, $sDriver = "{MySQL ODBC 5.1 Driver}")

i have this error.. can some1 help?

C:\Program Files\AutoIt3\Include\mysql.au3 (30) : ==> The requested action with this object has failed.:
$ObjConn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort)
$ObjConn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort)^ ERROR




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users