Jump to content

MySQL UDFs


cdkid
 Share

Recommended Posts

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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to comment
Share on other sites

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!

My Projects: [topic="89413"]GoogleHack Search[/topic], [topic="67095"]Swiss File Knife GUI[/topic], [topic="69072"]Mouse Location Pointer[/topic], [topic="86040"]Standard Deviation Calculator[/topic]

Link to comment
Share on other sites

  • 5 weeks later...

#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?

Link to comment
Share on other sites

  • 2 weeks later...

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 by Altainta
Link to comment
Share on other sites

  • 3 weeks later...

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!

Link to comment
Share on other sites

  • 3 weeks later...
  • 4 weeks later...

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
Share on other sites

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 & ")")
Link to comment
Share on other sites

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.

I little problem, hard to find and fix

Link to comment
Share on other sites

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

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
Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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 by sharkos
Link to comment
Share on other sites

  • 2 weeks later...

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
My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...