Sign in to follow this  
Followers 0
tsue

mysql ez simplification

4 posts in this topic

hello, im trying to simplify this code

Case $button10


            Local $sMySqlStatement = ""
            $aOk = _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & ";")
            Local $rows = UBound($aOk)
            If GUICtrlRead($Input12) > $rows Then
                MsgBox(0, "error", "exede rows bd: " & $rows & "leido: " & GUICtrlRead($Input12) )
            Else
                $aOk = _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & " WHERE RowID = " & "'" & GUICtrlRead($Input12) & "';")
                $error = @error
                If Not IsArray($aOk) Then
                    MsgBox(0, $sMySqlStatement & " error", $error)
                Else
                    GUICtrlSetData($Input10, $aOk[1][1])
                    GUICtrlSetData($Input11, $aOk[1][2])
                    GUICtrlSetData($Input12, $aOk[1][3])
                EndIf
            EndIf

            Local $rows = UBound($aOk)
            Local $cols = UBound($aOk, 2)
            Local $dims = UBound($aOk, 0)
            MsgBox(0, "The " & $dims & "-dimensional array has", _
                    $rows & " rows, " & $cols & " columns")
            _ArrayDisplay($aOk, "2d array")

so i dont have to use too much code in the error reporting, i want it to look something like this,

Case $button10
            
            If Not _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & " WHERE RowID = " & "'" & GUICtrlRead($Input12) & "';") Then
                MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
                
            EndIf

but this keeps getting me errors

Share this post


Link to post
Share on other sites



Few remarks:

  • Which error(s) do you get and where?
  • In your second code box, SELECT failing will display a misleading "Error inserting data..."
  • I'm no regular MySQL user but I suspect that "... where rowid = '123456' ... " isn't going to do what you think it should. IFAIK rowid is an integer but you're comparing it to a string.

 

Other generic SQL remarks:

  • SELECT * from ... is a kind of gamble over the future. Should you have to reorganize your schema and change column order, then your application will fall flat on its nose. Don't be lazy and enumerate columns explicitely wanted.
  • Sanitize or, at the very least, escape user input. Maybe you'll be the only user of this code but in a more general setup, expect the user input to be potentially malicious or simply containing a single quote!

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

 

Few remarks:

  • Which error(s) do you get and where?
  • In your second code box, SELECT failing will display a misleading "Error inserting data..."
  • I'm no regular MySQL user but I suspect that "... where rowid = '123456' ... " isn't going to do what you think it should. IFAIK rowid is an integer but you're comparing it to a string.

 

Other generic SQL remarks:

  • SELECT * from ... is a kind of gamble over the future. Should you have to reorganize your schema and change column order, then your application will fall flat on its nose. Don't be lazy and enumerate columns explicitely wanted.
  • Sanitize or, at the very least, escape user input. Maybe you'll be the only user of this code but in a more general setup, expect the user input to be potentially malicious or simply containing a single quote!

 

mmm i have this problem: when an error occurs the program crashes and it exit, is there a way yust to return the error like msgbox(0, "error", "somthing"), and avoid exiting(crashing) the program

thanks for your other tips i still dont understand them very well but ill keep learning 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

By just looking at a small exerpt of the actual code and _EzMySql_* functions (UDF?) not being my playground, it's difficult to tell what causes the crashes you experience.

Regarding select * from... : * will return columns in the order where the DDL (the create table, then possibly alter table statements) put them. If a change in the column order happens after your application is widespread, the column retrieved won't match the program expectations anymore. Retrieving columns explicitely overcomes this potential pitfall.

About unescaped literals, the problem here is the well-known SQL injection attack:

Your SQL code: "update customers set address = '" & $variable1 & "' where customerID = " & $rowid & ";"

Malvolent user input for $variable1: "'; drop table customers; drop table items; drop table invoices; drop table suppliers; -- fuck you!"

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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