Sign in to follow this  
Followers 0

MySQL UDFs (without ODBC)


141 posts in this topic




Posted

@ProgAndy

I like anything that smells to SQL !!

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

Rgds

ptrex

Share this post


Link to post
Share on other sites

Posted (edited)

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

UEZ

Edited by UEZ

Share this post


Link to post
Share on other sites

Posted

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 :)

Share this post


Link to post
Share on other sites

Posted

@ProgAndy

Kein Problem. Vielen Dank !!

Regards,

ptrex

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

Thanks :)

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

excellent one

really excellent

i'm using this for my monitoring system ( NDO Nagios database ) and it works perfectly

really thx for sharing !

Share this post


Link to post
Share on other sites

Posted

its a nice one but i would prefer a memory loaded dll, so no filewrites and reads anywere....

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

Code would be to without no?

#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

Share this post


Link to post
Share on other sites

Posted

This works:

#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()

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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:
;===============================================================================
;
; 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.

Share this post


Link to post
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
Sign in to follow this  
Followers 0