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 (without ODBC)

dll libmysql mysql

  • Please log in to reply
139 replies to this topic

#1 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 07 December 2008 - 06:37 PM

*
POPULAR

MySQL UDFs
using libmysql.dll
functions: most functions from MySQL API
all are prefixed with an underscore: _MySql... e.g.: _MySQL_Real_Query(
sometimes parameters are chaged - read function descriptions in include file MySQL.au3

If you do not need the power of these UDFs and you simple want to use basic SQL commands, then have a look at EzMySQL :)

not included:
MySQL_Connect - This function is deprecated. Use _MySQL_Real_Connect instead. MySQL_Create_DB - This function is deprecated. Use mysql_query() to issue an SQL CREATE DATABASE statement instead. MySQL_Drop_DB - This function is deprecated. Use mysql_query() to issue an SQL DROP DATABASE statement instead. MySQL_Escape_String - You should use _mysql_real_escape_string()  instead! MySQL_Kill - This function is deprecated. Use mysql_real_query() to issue an SQL KILL statement instead mysql_library_end - Called by _MySQL_EndLibrary. mysql_library_init - Called by _MySQL_InitLibrary.


I included a fallback-libmysql.dll: yoou can include libMySQLDLL.au3 and set $Use_EmbeddedDLL=True when calling _MySQL_InitLibrary

an example for XAMPP / cdcol is also included in ZIP.
AutoIt         
#cs ----------------------------------------------------------------------------         AutoIt Version: 3.2.8.1 (beta)     Author:      Prog@ndy         Script Function:     MySQL-Plugin Demo Script     #ce ---------------------------------------------------------------------------- #include <array.au3> #include "mysql.au3" ; MYSQL starten, DLL im PATH (enthält auch @ScriptDir), sont Pfad zur DLL angeben. DLL muss libmysql.dll heißen. _MySQL_InitLibrary() If @error Then Exit MsgBox(0, '', "could nit init MySQL") MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info()) $MysqlConn = _MySQL_Init() ;Fehler Demo: MsgBox(0,"Error-demo","Error-Demo") $connected = _MySQL_Real_Connect($MysqlConn,"localhostdfdf","droot","","cdcol") If $connected = 0 Then     $errno = _MySQL_errno($MysqlConn)     MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))     If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Endif ; XAMPP cdcol MsgBox(0, "XAMPP-Cdcol-demo", "XAMPP-Cdcol-demo") $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "cdcol") If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn)) $query = "SELECT * FROM cds" $mysql_bool = _MySQL_Real_Query($MysqlConn, $query) If $mysql_bool = $MYSQL_SUCCESS Then     MsgBox(0, '', "Query OK") Else     $errno = _MySQL_errno($MysqlConn)     MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) EndIf $res = _MySQL_Store_Result($MysqlConn) $fields = _MySQL_Num_Fields($res) $rows = _MySQL_Num_Rows($res) MsgBox(0, "", $rows & "-" & $fields) ; Access2 1 MsgBox(0, '', "Access method 1- manual") Dim $array[$rows][$fields] For $k = 1 To $rows     $mysqlrow = _MySQL_Fetch_Row($res,$fields)     $lenthsStruct = _MySQL_Fetch_Lengths($res)     For $i = 1 To $fields         $length = DllStructGetData($lenthsStruct, 1, $i)         $fieldPtr = DllStructGetData($mysqlrow, 1, $i)         $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)         $array[$k - 1][$i - 1] = $data     Next Next _ArrayDisplay($array) ; Access 2 MsgBox(0, '', "Access method 2 - row for row") _MySQL_Data_Seek($res, 0) ; just reset the pointer to the beginning of the result set Do $row1 = _MySQL_Fetch_Row_StringArray($res) If @error Then ExitLoop _ArrayDisplay($row1) Until @error ; Access 3 MsgBox(0, '', "Access method 3 - read whole result in 2D-Array") $array = _MySQL_Fetch_Result_StringArray($res) _ArrayDisplay($array) ; fieldinfomation MsgBox(0, '', "Access fieldinformation") Dim $arFields[$fields][3] For $i = 0 To $fields - 1     $field = _MySQL_Fetch_Field_Direct($res, $i)     $arFields[$i][0] = _MySQL_Field_ReadValue($field, "name")     $arFields[$i][1] = _MySQL_Field_ReadValue($field, "table")     $arFields[$i][2] = _MySQL_Field_ReadValue($field, "db") Next _ArrayDisplay($arFields) ; free result _MySQL_Free_Result($res) ; Close connection _MySQL_Close($MysqlConn) ; exit MYSQL _MySQL_EndLibrary()


MySQL UDf Downloads:Posted Image (including x86 and x64)</array.au3>

Edited by ProgAndy, 22 March 2012 - 03:17 PM.

  • Neutro, Fire, chandoi and 2 others like this
*GERMAN* Posted Image [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







#2 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 07 December 2008 - 07:15 PM

@ProgAndy

I like anything that smells to SQL !!

Unfortunately you link does not work (for me ?).

Rgds

ptrex

#3 Xand3r

Xand3r

    Aka TheMadman

  • Active Members
  • PipPipPipPipPipPip
  • 454 posts

Posted 07 December 2008 - 09:42 PM

me neither ..
the link is

http://index.php/?option=com_remository&am...einfo&id=22

so there is no possible way for it to work ...
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

#4 UEZ

UEZ

    Never say never

  • MVPs
  • 4,840 posts

Posted 07 December 2008 - 10:09 PM

Here the correct link. :) It is in German. Just click download....

UEZ

Edited by UEZ, 07 December 2008 - 10:25 PM.

 
The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯


#5 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 08 December 2008 - 11:44 AM

Yeah, i modded remository and forgot to add one variable in my link generator, so I corrected it.
The UDF itself is in english, just the description on the downloadpage and the example are German :)
*GERMAN* Posted Image [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

#6 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 09 December 2008 - 07:16 PM

@ProgAndy

Kein Problem. Vielen Dank !!

Regards,

ptrex

#7 Ealric

Ealric

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 520 posts

Posted 18 December 2008 - 04:51 AM

Andy this is perfect mate. I love the non-odbc connection and it seems to perform pretty solidly. I've altered a few things in the test script to make it a little easier for me to follow but I'm able to connect to my DB and work through a lot of it. I'm still looking through your UDFs but I just wanted to say "SOLID WORK"!

I'll give you 5-stars for this effort..

#8 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 18 December 2008 - 05:18 PM

Thanks :)
*GERMAN* Posted Image [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

#9 Ealric

Ealric

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 520 posts

Posted 30 December 2008 - 04:58 PM

Hi Andy,

I still love this series of UDFs you created and it's been making my life a lot easier with a custom program I'm working on. The only issue I've found so far is if you have anything wrong with your query the program crashes completely on both Vista and XP without any warning. You just get a program stopped responding message. It would be nice to have an error return for what caused/created those issues. Usually, I just assume it's the select query and troubleshoot it myself. However, for consistency, it would be nice to have an error message explaining that the query is not formatted properly, etc.

Thanks mate.

#10 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 30 December 2008 - 05:06 PM

can you post an example script? There is already error checking, but the error has to be handled manually:
$mysql_bool = _MySQL_Real_Query($MysqlConn, $query) If $mysql_bool = $MYSQL_SUCCESS Then     MsgBox(0, '', "Query OK") Else     $errno = _MySQL_errno($MysqlConn)     MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) EndIf

*GERMAN* Posted Image [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

#11 arcker

arcker

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 581 posts

Posted 05 January 2009 - 09:42 AM

excellent one
really excellent
i'm using this for my monitoring system ( NDO Nagios database ) and it works perfectly
really thx for sharing !

-- Arck System _ Soon -- Ideas make everything

"La critique est facile, l'art est difficile"

 

Projects :

  • Au3Service : Run your exe as service V3 / Updated 29/07/2013 Get it Here

#12 JRSmile

JRSmile

    MCSE 2012R2

  • Active Members
  • PipPipPipPipPipPip
  • 461 posts

Posted 07 January 2009 - 06:21 PM

its a nice one but i would prefer a memory loaded dll, so no filewrites and reads anywere....
$a=StringSplit("547275737420796F757220546563686E6F6C75737421","")For $b=1 To UBound($a)+(-1*-1*-1)step(2^4/8);&$b+=1*2/40*µ&Asc(4)Assign("c",Eval("c")&Chr(Dec($a[$b]&$a[$b+1])))''Chr("a")&"HI"Next;time_U&r34d,ths,U-may=get$the&c.l.u.e;b3st-regards,JRSmile;MsgBox(0x000000,"",Eval("c"));PiEs:d0nt+*b3.s4d.4ft3r.1st-try:-)

#13 ludics

ludics

    Seeker

  • Active Members
  • 34 posts

Posted 10 January 2009 - 04:42 PM

Since I can make to remove information from the data base to do login, that is to say have the data base “clan” and to verify I have a table “users” and within users I have two key columns “yam” and “password”. Since I can make to remove the information and to compare with the GUI Longin

#14 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 10 January 2009 - 04:48 PM

Do you want to check if the user exists and the password is correct? Then it should work like this:
-connect to clan
-query:
"SELECT * FROM users WHERE yam=`"  & _MySQL_RealEsacepString($yam) & "` AND password=`" & _MySQL_RealEsacepString($pass) & "`"

-fetch all data (see examples)
-disconnect
*GERMAN* Posted Image [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

#15 ludics

ludics

    Seeker

  • Active Members
  • 34 posts

Posted 10 January 2009 - 05:09 PM

Code would be to without no?

Plain Text         
#include <array.au3> #include "mysql.au3" _MySQL_InitLibrary() If @error Then Exit MsgBox(0, '', "") $MysqlConn = _MySQL_Init() $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "webclan") If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn)) $query = "SELECT * FROM users WHERE yam=`"  & _MySQL_RealEsacepString($yam) & "` AND password=`" & _MySQL_RealEsacepString($pass) & "`" _MySQL_Real_Query($MysqlConn, $query) ;------------------------------------------------------ ;------------------------------------------------------ $res = _MySQL_Store_Result($MysqlConn) $fields = _MySQL_Num_Fields($res) ; Abfrage freigeben _MySQL_Free_Result($res) ; Verbindung beenden _MySQL_Close($MysqlConn) ; MYSQL beenden _MySQL_EndLibrary()


But it gives error me in query, and encounter the code not to confirm the user and the password agrees

#16 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 10 January 2009 - 05:26 PM

This works:
Plain Text         
#include <array.au3> #include "mysql.au3" _MySQL_InitLibrary() If @error Then Exit MsgBox(0, '', "") $MysqlConn = _MySQL_Init() $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "webclan") If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn)) $yam = "testuser" $pass = "pass" $query = "SELECT * FROM users WHERE yam='"  & _MySQL_Real_Escape_String($MysqlConn,$yam) & "' AND password='" & _MySQL_Real_Escape_String($MysqlConn,$pass) & "'" MsgBox(0, '', $query) If _MySQL_Real_Query($MysqlConn, $query) = $MYSQL_ERROR Then     MsgBox(0, 'Error', _MySQL_Error($MysqlConn)) Else ;------------------------------------------------------ ;------------------------------------------------------ $res = _MySQL_Store_Result($MysqlConn) $users = _MySQL_Num_Rows($res) MsgBox(0, '', "Found " & $users & " user(s) with this yam and PW") ;~; Abfrage freigeben _MySQL_Free_Result($res) EndIf ;~; Verbindung beenden _MySQL_Close($MysqlConn) ;~; MYSQL beenden _MySQL_EndLibrary()

*GERMAN* Posted Image [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

#17 ludics

ludics

    Seeker

  • Active Members
  • 34 posts

Posted 10 January 2009 - 08:37 PM

It is worth this script or it is worth to me and it works correctly, but or of the problems that now consider to me it is that if for example ban to a user, and I do not allow login him in the program, would have to check the user, the password and if this ban, with script that we have now I imagine that only adding the table “ban” and putting if this ban = 1 if not this = 0 or this, but to be able to compare it would need two querys, the question is that if or I am login and I want to enter with another one query after login to verify in the same row of the user but in a different table, it would be possible to be done ?

Edited by ludics, 10 January 2009 - 08:39 PM.


#18 jacQues

jacQues

    Wayfarer

  • Active Members
  • Pip
  • 63 posts

Posted 28 January 2009 - 06:43 PM

Question about Fetch_Row()

When a VarChar field is empty or has a NULL value, the return value is Int(0) instead of the expected String("").

Ideally, an empty field would return String("") and a NULL value would return Default. Is this possible?

jacQues

#19 jacQues

jacQues

    Wayfarer

  • Active Members
  • Pip
  • 63 posts

Posted 28 January 2009 - 06:51 PM

It is worth this script or it is worth to me and it works correctly, but or of the problems that now consider to me it is that if for example ban to a user, and I do not allow login him in the program, would have to check the user, the password and if this ban, with script that we have now I imagine that only adding the table “ban” and putting if this ban = 1 if not this = 0 or this, but to be able to compare it would need two querys, the question is that if or I am login and I want to enter with another one query after login to verify in the same row of the user but in a different table, it would be possible to be done ?

That would be a SQL question, not a question about this MySQL library. Try http://dev.mysql.com for documentation, examples and other resources.

Quick answer, using a single query, something like this: (just example code, by no means working code)

$query = "SELECT * FROM users JOIN banned ON banned.yam=users.yam WHERE users.yam='"&_MySQL_Real_Escape_String($MysqlConn,$yam)&"' AND password='"&_MySQL_Real_Escape_String($MysqlConn,$pass)&"' AND banned.isbanned='0'"

Such code would simply deny access if the person is banned, regardless of password correctness.

jacQues

#20 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 28 January 2009 - 07:22 PM

Question about Fetch_Row()

When a VarChar field is empty or has a NULL value, the return value is Int(0) instead of the expected String("").

Ideally, an empty field would return String("") and a NULL value would return Default. Is this possible?

jacQues

Try this:
AutoIt         
;=============================================================================== ; ; Function Name:   _MySQL_Fetch_Row_StringArray ; Description::    Fetches one row to an array as strings ; Parameter(s):    $result         - MySQL Resut pointer returned from _MySQL_Real_Query ;                  $numberOfFields - [optional] The count of fields in the result set. (default: uses _MySQL_Num_Fields) ; Requirement(s):  libmysql.dll ; Return Value(s): Array with Strings. On error 0 (ZERO) ; Author(s):       Prog@ndy ; ;=============================================================================== ; Func _MySQL_Fetch_Row_StringArray($result, $fields = Default)     If $fields = Default Then $fields = _MySQL_Num_Fields($result)     If $fields <= 0 Then Return SetError(1, 0, 0)     Local $RowArr[$fields]         Local $mysqlrow = _MySQL_Fetch_Row($result, $fields)     If Not IsDllStruct($mysqlrow) Then Return SetError(1, 0, 0)         Local $lenthsStruct = _MySQL_Fetch_Lengths($result)     Local $length, $fieldPtr     For $i = 1 To $fields         $length = DllStructGetData($lenthsStruct, 1, $i)         $fieldPtr = DllStructGetData($mysqlrow, 1, $i)         Switch $length=0             Case True                 $RowArr[$i - 1] = ""             Case Else                 $RowArr[$i - 1] = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)         EndIf     Next     Return $RowArr EndFunc   ;==>_MySQL_Fetch_Row_StringArray

You are right, empty string should be empty string, but if field is NULL, you should be able to recognize it.... Have to think about it, then i will update the download.
*GERMAN* Posted Image [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





Also tagged with one or more of these keywords: dll, libmysql, mysql

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users