View topic - [AHK L] DBA 1.3 (OOP-SQL DataBase {SQLite, MySQL, ADO}) • AutoHotkey Community

View unanswered posts | View active topics

Board index » AutoHotkey » Scripts » Custom

All times are UTC [ DST ]

[AHK L] DBA 1.3 (OOP-SQL DataBase {SQLite, MySQL, ADO})

Page 3 of 6

[ 79 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6 Next

Print view | E-mail friend

Previous topic | Next topic

Author

Message

IsNull

Post subject:

Posted: January 3rd, 2012, 10:13 pm

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland

I've added string escaping to the autogenerated insert sql statements, to prevent unexpected sql injections etc.

The Database now has an Method called EscapeString(str), which can easily accessed by:

Code:

;Database db
  
username := "x'; DROP TABLE members; --"
username  := db.EscapeString(username)

_________________
http://securityvision.ch
AHK 2D GAME ENGINE

Top

figure8car

Post subject: Re: [AHK L] DBA 0.8 (OOP-SQL DataBase in AHK {SQLite, MySQL}

Posted: January 25th, 2012, 6:30 pm

Joined: November 21st, 2008, 6:53 am
Posts: 2

My problem here was that I dragged the DBA files directly out of WinRar into my working directory, without creating \lib\ subdirectory. Everything works fine. I'm using AHK 1.1.05.06_L 32 bit Unicode on Windows 7 Home Premium 64. Thanks again for DBA 0.8!

figure8car wrote:

Thanks for all this work. I'm looking forward to using SQLite with AutoHotKey! I'm usingon Windows 7 64. When I try to run this sample code on a database that I'm now using in a Python program, I have to remove the <> symbols in the #Include statements and append the Include file name with .ahk. If I do that, it stops complaining. Also, the database I'm using has a file extenstion of .db instead of .sqlite. It works in Python and with SQLite Manager in FireFox. Does that matter here? The reason I ask is because no error messages occur until I try to read some data, then I get "A non object value was improperly invoked"

Then in the diagnostic window:
045: columnCount := tables.Columns.Count()
091: ListLines

A problem reading the database?

[/img]

Top

IsNull

Post subject:

Posted: January 29th, 2012, 3:08 pm

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland

The user AimLike extracted the 64bit libmySQL.dll and I included it into the DBA library. DBA automatically detects the AHK Version running and chooses the right dll.
Though, out of box support for 64bit SQLite is still missing. Anyway, DBA has been updated to version 0.9. (I'm running out of alpha version numbers :mrgreen:)

If some of you wish to include a very basic ORM, where you can also UPDATE you Objects/Records, I may give it a try.

Quote:

Thanks again for DBA 0.8!

Thank you for the kind words.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE

Top

mparsons

Post subject: Thanks

Posted: January 31st, 2012, 5:27 pm

Joined: January 22nd, 2012, 6:10 pm
Posts: 5

This is an awesome library!

Top

musiek

Post subject:

Posted: March 1st, 2012, 11:05 am

Joined: October 5th, 2011, 11:32 pm
Posts: 66
Location: Poland

If i have situation that ahk have utf-8 coding, but my database have iso 8859-2 and i want send select to it, how can i send query with some polish chars?

Something like that will work:

Code:

Select * from table1

Something like that will not (0 rows):

Code:

Select * from table1 where table1.name = 'święty'

How can I encode string with this query to iso 8859-2 that i will have some effect???

Top

Mash95

Post subject:

Posted: March 4th, 2012, 2:45 pm

Hi,
I have two problems at the moment.
The first is, how can I insert variables in the database?

Code:

   record := {}
   record.Name := "%name%"
   record.Passwort := "%loginpassword%"
   db.Insert(record, "accounts")

that's not working, i do not know, how to write that, that it will save the variables.

The second thing is, how can I check if there is already an account with the same name?
Is there any description of the usage?

Mash

Top

IsNull

Post subject:

Posted: March 5th, 2012, 1:14 pm

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland

Code:

record.Name := "%name%"
record.Passwort := "%loginpassword%"

Your expression syntax is wrong, unquoted literals are considered as variables in a expression. Please re-read the AHK help topic expressions.

Code:

record.Name := name
record.Passwort := loginpassword

Quote:

The second thing is, how can I check if there is already an account with the same name?

Use pure SQL, the Library does not offer any ORM which does check if an Enitity is alread existing in the DB. Use a SQL Where-clause.

Thus, something like that works to check that:

Code:

username :=  "Max"
foundRow := db.QueryRow("Select * from accounts Where Name = "  db.EscapeString(username))

if(IsObject(foundRow ))
  msgbox % "Whoohoo we have a user with the name: " foundRow.Name
else
 msgbox  % "I'm sorry but there is no one called " username 

However, I recommend to use ID keys, best might be GUIDs which can be generated client side for a proper Enitity handling.

@muisek:

Quote:

How can I encode string with this query to iso 8859-2 that i will have some effect???

You may encode the whole SQL String in the proper format and pass it to the Query Method. But there are a lot of pitfalls, for example the diffrent db libraries and their support for those encodings.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE

Top

cubesnyc

Post subject:

Posted: March 5th, 2012, 9:39 pm

I am getting a lot of ERRORLEVEL =5 errors when using sqlite while running several inserts and selects in rapid succession. Could this be a bug in the library, or is it just a function of sqllite?

Top

IsNull

Post subject:

Posted: March 7th, 2012, 1:22 pm

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland

cubesnyc wrote:

I am getting a lot of ERRORLEVEL =5 errors when using sqlite while running several inserts and selects in rapid succession. Could this be a bug in the library, or is it just a function of sqllite?

Code:

#define SQLITE_BUSY 5 /* The database file is locked */

SQLite is single user only, and strictly atomic.

Thus you can have just one Connection which has write permissions and you can not do parallel Querys further you should close any Resultset before using a new one. Most issues using SQLite occur if you do not close Resultsets before opening a new one.

You may just catch those errors and retry if they occur not by design.

You may post your Code part (for ex the loop where you to those querys)

_________________
http://securityvision.ch
AHK 2D GAME ENGINE

Top

musiek

Post subject:

Posted: April 10th, 2012, 11:40 am

Joined: October 5th, 2011, 11:32 pm
Posts: 66
Location: Poland

Is there any option to cache MySql queries??

Top

IsNull

Post subject: Re: [AHK L] DBA 0.9 (OOP-SQL DataBase in AHK {SQLite, MySQL}

Posted: April 10th, 2012, 1:54 pm

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland

Jup, the C-API(which is internally used by this wrapper) supports prepared statements, as you can see here: http://dev.mysql.com/doc/refman/5.1/en/ ... ments.html

However, it is currently not implemented and I decided to stop any development on AHK Scripts as long as the Future of the advanced Features of AutoHotkey is not preserved.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE

Top

fragman

Post subject: Re: [AHK L] DBA 0.9 (OOP-SQL DataBase in AHK {SQLite, MySQL}

Posted: April 10th, 2012, 2:39 pm

Joined: October 13th, 2009, 10:09 pm
Posts: 1389

Very comprehensible. It's quite sad that the future plans for AHK appear like a few steps backwards.

Top

infogulch

Post subject: Re: [AHK L] DBA 0.9 (OOP-SQL DataBase in AHK {SQLite, MySQL}

Posted: April 16th, 2012, 3:51 am

Joined: March 27th, 2008, 2:14 pm
Posts: 700

I just fixed a very obscure and difficult to fix bug.

Somewhere (not sure where exactly), the entire contents of the Row class was completely cleared effectively making new Row() actually call new Collection() instead (since Row extends Collection, the call to __new fell through). However this only happened on the second call... very frustrating. Renaming the class (and references to it) to DBRow fixed it for now.

This should be a lesson in class naming:

Quote:

Never name a class anything that could conceivably be used as a regular variable name.

Use long and/or very specific names for your classes OR use subclasses. Class names like Row, Table, Database, RecordSet etc is just inviting disaster.

Edit: On a second look, it appears I used a local variable named row in one of my functions but since classes are super-global it still referenced the class, so this is technically not a bug in the library itself, but it's still inviting disaster.

Edit 2: On third look, I actually declare that variable local... so nfi what's going on.

_________________
Scripts - License

Top

panofish

Post subject: Re:

Posted: April 16th, 2012, 5:46 am

Joined: February 6th, 2007, 12:30 am
Posts: 142
Location: Michigan

IsNull is already aware of the need to prefix class names. I'm sure he'll get to it when he can.

panofish wrote:

Please prefix the classes.
It takes a while to debug these errors which are caused by using a variable of the same name as a class.

This is an excerpt of my code that was failing.
The get for rec["status"] failed with a "non-object value invoked".
It took me a while to discover that my use of the variable "row" was causing the error, just like my previous use of a variable called "table".

Code:

PickList2:
       
    if IgnoreEvent()
        return
    
    gui_status("","clear.ico")    ; clear statusbar
    
    ;------------------------------------------------
    ; get request id from selected row
    ;------------------------------------------------    
        
    Gui, ListView, LIST2    ; specify which listview 
    
    row := LV_GetNext("")   ; get selected row number
    
    if (row = 0)     ; return if no row selected
        return

    LV_GetText(requestid, row, 1)  ; Get the text from specified column
    
    ;------------------------------------------------
    ; update form fields for selected request id
    ;------------------------------------------------    

    ; use requestid to get all data from request table
          
    sql = 
    (
         select status,
                DATE_FORMAT(opendate, '`%m-`%d-`%Y') as opendate,
                DATE_FORMAT(duedate, '`%m-`%d-`%Y') as duedate,
                DATE_FORMAT(closedate, '`%m-`%d-`%Y') as closedate,
                requesttype,
                concat(modelyear, " ", brand, " ", make) as vehicle,
                description,
                priority
           from request 
          WHERE requestid = %requestid%
    )    

    rec := db.QueryRow(sql) 
    status := rec["status"]
    opendate := rec["opendate"]
    ...

_________________
http://www.panofish.net

Top

infogulch

Post subject: Re: [AHK L] DBA 0.9 (OOP-SQL DataBase in AHK {SQLite, MySQL}

Posted: April 16th, 2012, 6:19 am

Joined: March 27th, 2008, 2:14 pm
Posts: 700

Ah I see. Sorry for posting on a problem that has been reported before.

_________________
Scripts - License

Top

Page 3 of 6

[ 79 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6 Next

Board index » AutoHotkey » Scripts » Custom

All times are UTC [ DST ]

Who is online

Users browsing this forum: hughman, rrhuffy and 2 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum