MySQL UDFs

[ProgAndy]

Possbly the one in the attachment is the most recent one since cdkid stopped working on it.

note: This will still be available, but due to my new job, and school hours, am no longer developing this udf.

(But if you want, you could check out my MySQL-UDFs without ODBC, link in my signature)

Edited December 17, 2008 by ProgAndy

[Ealric]

Possbly the one in the attachment is the most recent one since cdkid stopped working on it.

(But if you want, you could check out my MySQL-UDFs without ODBC, link in my signature)

Danke!

[relu08]

#include<mysql.au3>

$sqldomain = "127.0.0.1"
$sqldb = "relu"
$sqluser = "root"
$sqlpass = ""


$sql = _MySQLConnect($sqluser,$sqlpass,$sqldb,$sqldomain)
  If @error Then
        MsgBox(16, "Error", "_MySQLConnect() failed, @error = " & @error)
        Exit
    EndIf

$var = _Query($sql,"SELECT * FROM `"& $dbtable &"` WHERE 1")
With $var
While NOT .EOF
FileWriteLine("c:\test.txt",.Fields($field).value & @CRLF)
.MoveNext
WEnd
EndWith
_MySQLEnd($sql)

I have some trouble connecting to any database on internet.

When i try to connect to localhost database works fine, but when i try to connect to any database on internet i get this error

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "D:\Autoit scripts\sql2.au3"  
C:\Program Files\AutoIt3\Include\mysql.au3 (27) : ==> 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: 1.721

what i miss?

[Altainta]

I needed a program which will check the mysql database and retrive the undone urls. (by the condition Provided = YES/NO)

#Include <mysql.au3>

While 1

$sql = _MySQLConnect("username","password","url","localhost")
$var = _Query($sql,"SELECT * FROM url WHERE provided = 'no' ORDER BY id limit 1")

With $var


$id = .Fields("id").value
$url = .Fields("url").value
msgbox(0,"test",.Fields("url").value )
msgbox(0,"test",.Fields("id").value )

EndWith

;The below will run a program and wait until it is finished
;here i wanted a timer which will kill the test2.exe process if it doesn't respond or hangs and clear the thing than continue loop
runwait("c:\test2.exe " & $id & ' ' & $url)
;set the url entry to yes so next time we will not get that url again
    $result = _Query($sql, "UPDATE `url` SET `provided` = 'yes' WHERE `id` = " & $id & ' LIMIT 1;')
_MySQLEnd($sql)
    sleep(5000)
    wend

Whenever there is no entry left in the database which will match the condition than this same code will give error and exit the loop

C:\testing.au3 (15) : ==> Error in expression.:
$id = .Fields("id").value
$id = .Fields("id").value^ ERROR

this script will run on a pc 24hrs it will check the database for undone urls (every 5 sec) and if found than process them and continue loop.

But because of the above error the Loop get terminated and the program exits...

Please help

Edited January 25, 2009 by Altainta

[MatteoGuallini]

Hi

Someone can tell me how I can modify an existing record in a table using this UDF?

thanks in advance.

[tomc]

Hi all !

I need something to read student's name and birth date in their "results .txt", then fetch their new classroom from a database an finally move their results .txt in the classroom's folder.

Maybe a little complicated for one of my first script ...

here is what I have actually:

#include "FileListToArrayEx.au3"
#include "array.au3"
#include "mysql.au3"
While 1
$results = _FileListToArrayEx(@ScriptDir,'*.txt',1,'',True)
    If NOT @error Then
        For $i = 1 to $results[0]
            
$sql = _MySQLConnect("root","","tbds","localhost")
$f = FileOpen( $results[$i], 0 )
$nom_f = FileReadLine( $f , 2 )
$naiss_f = FileReadLine( $f , 3 )
;MsgBox(0,"",$nom_f & $naiss_f)
$division = _Query($sql,"SELECT division FROM eleves WHERE nom = " & $nom_f & " AND naiss = " & $naiss_f )
FileClose($f)
_MySQLEnd($sql)
FileMove(@ScriptDir & "\" & $results[$i], "C:\" & $division, 8)
        Next
    EndIf
WEnd

all results .txt are in the script dir and look like:

nom

ANDRIEUX

220796

6°3

compétence 1

non

compétence 2

non

..........

..........

the csv I made the tbds DB table from:

"eleve_ID";"nom";"prenom";"naiss";"sexe";"doubl";"resp";"division";"eleve"

1;"ABELE";"Emmanuelle";150196;"F";"N";"ABELE";205;"ABELE Emmanuelle"

2;"ABELE";"Céline";260293;"F";"N";"ABELE";0;"ABELE Céline"

3;"ABRAS";"Sophie";040596;"F";"N";"ABRAS";108;"ABRAS Sophie"

....................................................................................................

.....

no error message but the script doesn' work... what am I doing wrong??

Thanks!

[DarBro]

Hi new to this. What I am trying to do is compile the obdc, but when I do it gives me an error "Error Adding File c:\driversetup.exe" is there something I should be doing?

[DarBro]

Got it working now. Just trying to get my code to work is there something that I could use to test to make sure everything works.

[tr1px]

By reading all these posts I have noticed that a lot of people are having connection problems. I also noticed that some of those problems never got resolved so hopefully this post can help you guys.

On some hosting companies you have to add an IP to MySQL privilege table so that this IP can run Queries. Another option is to create a new user for the MySQL database and allow access from any host ( % ) in which case for security reasons I would create strict rules because anyone with any scripting knowledge could probably have access.

In lamens terms if you have a MySQL server at 1.1.1.1 on some hosting provider and you are trying to access the database over the Internet from your IP ex. 2.2.2.2 then you need to create a new user and allow the host 2.2.2.2 to access it.

If you are distributing your software and there are multiple IPs then create a new user and give him only INSERT UPDATE and SELECT privileges and allow any host to connect by specifying a % sign.

[BinaryBrother]

Thanks man. Great work!

[johnfive]

Hi,

I've got exactly the same problem as radhoo (on previous page).

When I test it with this code :

CODE

#include "MySQL.au3"

$MySQLServerName = "localhost"

$UserName = "testusername"

$Password = "pass"

$Database = "testtable"

$SQLInstance = _MySQLConnect($UserName,$Password,$Database,$MySQLServerName)

MsgBox(0, "Mysql connect"," sqlinstance :" & $SQLInstance)

$sqlrequete = "INSERT INTO `idt`.`log` (`id` ,`date` ,'description`) VALUES ( NULL ,CURRENT_TIMESTAMP ,'hdhdfsgrsgsrg');"

_Query($SQLInstance, $sqlrequete)

_MySQLEnd($SQLconnect)

And I have this error :

CODE

Line 48 (file"c:\...\MySQL.au3")

Return $oConnectionobj.execute ($sQuery)

Return $oConnectionobj.execute ($sQuery)^ERROR

Error : The requestted action with this object has failed

I've test the instance with phpmyadmin it works fine, and i tried some others instance and it give me the same error.

A message box confirm me the connection to mysql, so no problem on it.

Thank you for future anwser.

[itablade]

Hi,

I got the following problem while inserting into a table. The connect to the databae and a select statement works fine.

_AddRecord($SQLInstance, 'checkit', '1', '2')

C:\Program Files (x86)\AutoIt3\Include\mysql.au3 (106) : ==> The requested action with this object has failed.:

$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")

$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")^ ERROR

[dmob]

Hi,

I got the following problem while inserting into a table. The connect to the databae and a select statement works fine.

_AddRecord($SQLInstance, 'checkit', '1', '2')

C:\Program Files (x86)\AutoIt3\Include\mysql.au3 (106) : ==> The requested action with this object has failed.:

$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")

$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")^ ERROR

Try removing the single quotes around $vValue, which may (I believe, but I may be wrong) fail if $vValue is not a string.

Like this:

CODE

$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")")

[itablade]

still the same problem

[Erik.]

Nice work

I will make an program for my website so i can edit my database

Thanks for your file

[Erik.]

I got this error:

$Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)

I need to download a new version of your UDF but your website is offline.

Could you please post the UDF on this forum for us?

Hope you will response soon.

[Chewie71]

Small update to the original code. We wanted to take advantage of the "ON DUPLICATE KEY UPDATE" feature...

http://dev.mysql.com/doc/refman/5.1/en/ins...-duplicate.html

So we modified the _AddRecord function (all the lines with the $update variable).

Matt

expandcollapse popup

Func _AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "")
    If IsObj($oConnectionObj) Then
        $query = "INSERT INTO " & $sTable & " ("
        
        If IsArray($vRow) Then
            For $i = 0 To UBound($vRow, 1) - 1
                If $i > 0 And $i <> UBound($vRow, 1) - 1 Then
                    $query = $query & "," & $vRow[$i] & ""
                ElseIf $i = UBound($vRow, 1) - 1 And $vRow[$i] <> "" Then
                    $query = $query & "," & $vRow[$i] & ") VALUES("
                ElseIf $i = 0 Then
                    $query = $query & "" & $vRow[$i] & ""
                ElseIf $vRow[$i] = "" Then
                    $query = $query & ") VALUES("
                EndIf
            Next
        EndIf
        If Not IsArray($vRow) And Not IsArray($vValue) And Not IsInt($vValue) Then
            $oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")
            return 1
        ElseIf IsInt($vValue) And Not IsArray($vRow) And Not IsArray($vValue) Then
            $oconnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")")
            return 1
        EndIf
        $update = " ON DUPLICATE KEY UPDATE"
        If IsArray($vValue) Then
            For $i = 0 To UBound($vValue, 1) - 1
                If $i > 0 And $i <> UBound($vValue, 1) - 1 And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "'"
                    $update = $update & "," & $vRow[$i] & "='" & $vValue[$i] & "'"
                ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "')"
                    $update = $update & "," & $vRow[$i] & "='" & $vValue[$i] & "';"
                ElseIf $i = 0 And Not IsInt($vValue[$i]) Then
                    $query = $query & "'" & $vValue[$i] & "'"
                    $update = $update & " " & $vRow[$i] & "='" & $vValue[$i] & "'"
                ElseIf $vValue[$i] = "" Then
                    $query = $query & ")"
                    $update = $update & ";"
                ElseIf IsInt($vValue[$i]) And $vValue[$i] <> "" Then
                    $query = $query & "," & $vValue[$i]
                    $update = $update & "," $vRow[$i] & "=" & $vValue[$i]
                EndIf
            Next
        EndIf
        If StringRight($query, 1) <> ")" Then
            $query = $query & ")"
            
        EndIf
        $query = $query & $update
        $oconnectionobj.execute ($query)
    EndIf
    If Not IsObj($oConnectionObj) Then
        SetError(2)
        Return 0
    EndIf
    If @error And IsObj($oConnectionObj) Then
        Return 0
        SetError(1)
    Else
        Return 1
    EndIf
    
EndFunc  ;==>_AddRecord

[Ixidior]

hi,

can i use resulsets or data tables or anything?? what i need to do is for example.

TB_CARS

NAME Q

FORD 4

CRYSLER 2

TOYOTA 10

MERCEDES 1

and i make this SQL statement. "SELECT NAME FROM CARS WHERE Q>4"

Can i store the resulset in an Array or anything else??

[sharkos]

Cant get it to work on Win2008 64bit

@error = 2

I have ODBC 3.51 (32bit) installed.

In HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers i have only 2 keys:

SQL Native Client = Installed

SQL Server = Installed

Ive tried to reinstall witch no effect.

Edited May 27, 2009 by sharkos

[goldenix]

How do you insert a values into 2 or more columns at the same time?

How do you update columns/rows ?

Hire is a sample code, how do you do it?

$Tag_ID     = _Query_("ID_column","SELECT * FROM tag_table WHERE Tag_Title = 'Yellow'")

Func _Query_($fields,$Query)

    $Query = _Query($sql,$Query)
      
        While NOT $Query.EOF
            
           $var = $Query.Fields($fields).value   
           
           Return $var 
           
        $Query.MoveNext
        WEnd
EndFunc