Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

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


  • Please log in to reply
183 replies to this topic
IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
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:
;Database db
  
username := "x'; DROP TABLE members; --"
username  := db.EscapeString(username)


figure8car
  • Members
  • 2 posts
  • Last active: Jan 26 2012 06:36 AM
  • Joined: 21 Nov 2008
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!

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]



IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
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.

Thanks again for DBA 0.8!

Thank you for the kind words.

mparsons
  • Members
  • 5 posts
  • Last active: Feb 11 2012 08:27 PM
  • Joined: 22 Jan 2012
This is an awesome library!

musiek
  • Members
  • 67 posts
  • Last active: Jan 23 2013 02:39 PM
  • Joined: 05 Oct 2011
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:
Select * from table1

Something like that will not (0 rows):
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???

Mash95
  • Guests
  • Last active:
  • Joined: --
Hi,
I have two problems at the moment.
The first is, how can I insert variables in the database?
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

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
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.

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

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:

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:

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.

cubesnyc
  • Guests
  • Last active:
  • Joined: --
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?

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007

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?


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

musiek
  • Members
  • 67 posts
  • Last active: Jan 23 2013 02:39 PM
  • Joined: 05 Oct 2011
Is there any option to cache MySql queries??

IsNull
  • Moderators
  • 990 posts
  • Last active: May 15 2014 11:56 AM
  • Joined: 10 May 2007
Jup, the C-API(which is internally used by this wrapper) supports prepared statements, as you can see here: <!-- m -->http://dev.mysql.com... ... ments.html<!-- m -->

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.

fragman
  • Members
  • 1591 posts
  • Last active: Nov 12 2012 08:51 PM
  • Joined: 13 Oct 2009
Very comprehensible. It's quite sad that the future plans for AHK appear like a few steps backwards.

infogulch
  • Moderators
  • 717 posts
  • Last active: Jul 31 2014 08:27 PM
  • Joined: 27 Mar 2008
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:

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.

panofish
  • Members
  • 179 posts
  • Last active: Apr 24 2014 03:24 PM
  • Joined: 05 Feb 2007
IsNull is already aware of the need to prefix class names. I'm sure he'll get to it when he can.

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

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



infogulch
  • Moderators
  • 717 posts
  • Last active: Jul 31 2014 08:27 PM
  • Joined: 27 Mar 2008
Ah I see. Sorry for posting on a problem that has been reported before. :)