Jump to content

Recommended Posts

Hey Every 1 :bye:

When I was a Beginner I always had a Hard Time to Understand the SQLite

I have made a UDF for SQLite so that Beginners Would Also Be Able to Do the Stuff .

Much Of Description is Given in the UDF.

To Understand Read It.

For Advanced Users it may not be sooo goood But Then Also Have a Look .

Thumbs Up if it Helped.. :)

The UDF is Attached for Download..

N herez the Index

#region    -Functions For User-
;_Insert
;_Update
;_Delete
;_FetchData
;_LoadDatabase
;_UnLoadDatabase
;_Cryptor
#endregion    -Functions For User-

#region  -Internal Functions-
;_DefaultAnalyser()
#endregion  -Internal Functions-

1 more Request

If You Use the Script Please Reply me of any Bugs or any Further Modifications for Betterment... :)

The UDF

v0.3 Database.7z

[Previous Downloads : 388]

Regards

Phoenix XL

Edited by PhoenixXL

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

  • 1 month later...

60 Downloads Yet no Reply

Please give Feedback

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

Thanks for sharing!

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Link to comment
Share on other sites

Nice script! Might I suggest using Tidy to make your code easier to read? Press CTRL+T. in scite. :)

One thing I've always pondered on is how to prevent SQL injection. Any suggestions? Stripping is generally the most common technique, although it is generally frowned upon if you rely on this.

 

 

Link to comment
Share on other sites

Encrypting the data before storing would be a good idea

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

djkman,

that's a dozy. the main thing is to have a function that separates an SQL statement and data into separate parameters.

then encode the data before inserting it into your sql statement to be executed.

PROBLEM SOLVED. i believe python does this as well.

ongoing projects:-firestorm: Largescale P2P Social NetworkCompleted Autoit Programs/Scripts: Variable Pickler | Networked Streaming Audio (in pure autoIT) | firenet p2p web messenger | Proxy Checker | Dynamic Execute() Code Generator | P2P UDF | Graph Theory Proof of Concept - Breadth First search

Link to comment
Share on other sites

Added : The support for finding the number of rows and cells in v0.2

Check the v0.2 in the First post

Edited by PhoenixXL

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

  • Moderators

Added : The support for finding the number of rows and cells in v0.2

Check the v0.2 in the First post

There are easier ways to get the row and column counts than dragging the entire database into memory.

Keep in mind, some databases could be really large.

Row Count #1

;; Warning, see: http://www.sqlite.org/autoinc.html

Func _Fetch_MaxRowID($h_db, $s_table, $s_condition = "")

    Local $f_dbopened = False
    If $h_db <> -1 And $h_db < 1 Then
        $h_db = _SQLite_Open($h_db, $SQLITE_READONLY)
        If $h_db < 1 Then Return SetError(1, 0, 0)
        $f_dbopened = True
    EndIf

    If $s_condition Then $s_condition = " " & $s_condition
    Local $n_retval, $h_query
    $n_retval = _SQLite_Query($h_db, "SELECT MAX(RowID) FROM " & $s_table & $s_condition & ";", $h_query)
    If $n_retval <> $SQLITE_OK Then
        If $f_dbopened Then _SQLite_Close($h_db)
        Return SetError(2, 0, 0)
    EndIf

    Local $a_r
    _SQLite_FetchData($h_query, $a_r)
    _SQLite_QueryFinalize($h_query)

    If $f_dbopened Then _SQLite_Close($h_db)

    If Not IsArray($a_r) Then Return 0

    Return Int($a_r[0])
EndFunc

Row Count #2 ; a bit slower than the previous but safer

Func _Fetch_RowIDCount($h_db, $s_table, $s_condition = "")

    Local $f_dbopened = False
    If $h_db <> -1 And $h_db < 1 Then
        $h_db = _SQLite_Open($h_db, $SQLITE_READONLY)
        If $h_db < 1 Then Return SetError(1, 0, 0)
        $f_dbopened = True
    EndIf

    If $s_condition Then $s_condition = " " & $s_condition
    Local $n_retval, $h_query
    $n_retval = _SQLite_Query($h_db, "SELECT COUNT(RowID) FROM " & $s_table & $s_condition & ";", $h_query)
    If $n_retval <> $SQLITE_OK Then
        If $f_dbopened Then _SQLite_Close($h_db)
        Return SetError(2, 0, 0)
    EndIf

    Local $a_r
    _SQLite_FetchData($h_query, $a_r)
    _SQLite_QueryFinalize($h_query)

    If $f_dbopened Then _SQLite_Close($h_db)

    If Not IsArray($a_r) Then Return 0

    Return Int($a_r[0])
EndFunc

Column count and column name fetch

Func _Fetch_ColumnCount($h_db, $s_table)

    Local $a_names = _Fetch_ColumnNames($h_db, $s_table)
    If @error Then Return SetError(@error, 0, 0)

    Return UBound($a_names)
EndFunc

Func _Fetch_ColumnNames($h_db, $s_table)

    Local $f_dbopened = False
    If $h_db <> -1 And $h_db < 1 Then
        $h_db = _SQLite_Open($h_db, $SQLITE_READONLY)
        If $h_db < 1 Then Return SetError(1, 0, 0)
        $f_dbopened = True
    EndIf

    Local $n_retval, $h_query
    Local $s_query = "PRAGMA table_info(" & $s_table & ");"
    $n_retval = _SQLite_Query($h_db, $s_query, $h_query)
    If $n_retval <> $SQLITE_OK Then
        If $f_dbopened Then _SQLite_Close($h_db)
        Return SetError(2, 0, 0)
    EndIf

    Local $a_r, $s_names
    While _SQLite_FetchData($h_query, $a_r) = $SQLITE_OK
        $s_names &= $a_r[1] & @CR
    WEnd

    _SQLite_QueryFinalize($h_query)

    If $f_dbopened Then _SQLite_Close($h_db)

    $s_names = StringTrimRight($s_names, 1)
    If Not $s_names Then Return SetError(3, 0, 0)

    Return StringSplit($s_names, @CR, 2)
EndFunc

I did not test them extensively, but they are versions of some things I use.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites

Thnks I will add them to the UDF

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

  • Moderators

Thnks I will add them to the UDF

I was hoping to enlighten more than have it added.

Good luck.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites

  • 2 months later...

Hi! Thanks for what seems to be a really useful UDF.

I'm newbie and I'm trying to learn SQLite.

Anyways this is what I get when trying your UDF:

>Running:(3.3.8.1):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "C:\Users\Sergio\Downloads\Database v0.2.au3"
->id:1 data: parameters:Hello No. of Cells: 3 No of Rows: 1
->id:1 data:0x2095F423F0E80A284B61C7D890E5E951 parameters:Nothing No. of Cells: 3 No of Rows: 1
-> data:Heya

!> Return Value From _FetchData: -1 Error Code:1

+>16:29:34 AutoIT3.exe ended.rc:0
>Exit code: 0 Time: 1.796

What am I missing?

Edited by Zoldex
Link to comment
Share on other sites

Its just Perfect

Looks as if u didnt read the comments in the examples section

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

Yes! You're right.

The truth is this is my first attempt with sqlite and must finish a project within two months and I was so scared about how difficult it could be that I didn't look enough through your script.

Now, after reading your script, at least I learned hot to properly use the syntax of the sqlite command "insert" and i'm a little less scared now...

Before I was using

_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ($variable,'1','Hello');")

Now I understand I must use

_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES (" & $variable & ",'1','Hello');")
Link to comment
Share on other sites

yep

right

hope so you understand the Example :)

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

Yeah I'm slowly understanding the mechanics...

anyways, I got fooled from the parameter definition you gave:

Syntax: _Insert_Update_Delete($sAction,$sData,$sWhere,$Table_Name='Database')

Parameters:
$sAction :1=Insert , 2=Update , 3=Delete

I guessed (after an hour) that should be

Parameters:

$sAction :0=Insert , 1=Update , 2=Delete

Isn't it?

Link to comment
Share on other sites

oops, my mistake :>

thats right

Parameters:

$sAction :0=Insert , 1=Update , 2=Delete

will change them

Thnx for the clarification

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

Another thing, but it could be my mistake, should be the syntax:

Syntax: _Insert_Update_Delete($sAction,$sData,$sWhere,$Table_Name='Database')

...but in the example you've written

; Inserting into column id,parameters the value 1 and Hello
If $_Just_Created Then _Insert_Update_Delete(0, 'id,parameters', "'1','Hello'")

id,parameters looks like a $sWhere and 1,Hello looks as $sData

I'm a bit confused about that

Edited by Zoldex
Link to comment
Share on other sites

Well dont get to the name of the variables its just the Syntax Sequence with respect to the syntax sequence of the _SQ_LITE commands

That is since Values are after the Column name therefore $sWhere and $sData correspond to them respectively

Hope it helps

I will Update the UDF to make things little bit less confusing :)

Edited by PhoenixXL

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
Share on other sites

  • 2 weeks later...

Hi, Can anyone create a UDF for the FireBird?

you should make a new topic regarding your help requests :)

My code:

PredictText: Predict Text of an Edit Control Like Scite. Remote Gmail: Execute your Scripts through Gmail. StringRegExp:Share and learn RegExp.

Run As System: A command line wrapper around PSEXEC.exe to execute your apps scripts as System (LSA). Database: An easier approach for _SQ_LITE beginners.

MathsEx: A UDF for Fractions and LCM, GCF/HCF. FloatingText: An UDF for make your text floating. Clipboard Extendor: A clipboard monitoring tool. 

Custom ScrollBar: Scroll Bar made with GDI+, user can use bitmaps instead. RestrictEdit_SRE: Restrict text in an Edit Control through a Regular Expression.

Link to comment
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
 Share

×
×
  • Create New...