Jump to content

Recommended Posts

PhoenixXL

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
  • Like 2

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.

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
TheSaint

Thanks for sharing!


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

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

Share this post


Link to post
Share on other sites
DJKMan

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.


My work in AutoIt (Not many yet):

Parse Large Text Files To 2D Array Quickly [With Chunk Size]

 

My artificial intelligence project coded entirely in AutoIt. Meet Alice Assistant: http://facebook.com/ProjectAliceAI

 

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
twitchyliquid64

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

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
SmOke_N

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.

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
SmOke_N

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.

Share this post


Link to post
Share on other sites
Zoldex

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

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
Zoldex

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');")

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
Zoldex

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?

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
Zoldex

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

Share this post


Link to post
Share on other sites
PhoenixXL

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.

Share this post


Link to post
Share on other sites
Hardnut
Hi, Can anyone create a UDF for the FireBird?

Share this post


Link to post
Share on other sites
PhoenixXL

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.

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

  • Similar Content

    • MFrancisca
      By MFrancisca
      Hello! 
      I've been checking the logging UDFs in the wiki, mostly Log4a and Loga and I wanted to ask which one would you recommend for a script that will be executed remotely through PSExec. My main question is where the logs are located in that situation and if I can change that location to a custom one., because I need to retrieve them at the end of execution.
      So in a rough description the process is
      Open PSExec
      Send compiled AutoIT script to remote machine
      Execute script
      Copy logs from the remote to the local machine.
       
      Any opinions? 
    • caramen
      By caramen
      I watched _OL_ItemSend&_OL_ItemFind&_OL_ItemCreate in OutlookEx UDF but hmmm
       
      Since it use the test environnement i cant get the orders of the mail creation
       
      Can someone make me win some time of reading all exemples script and lead me how to do one ?
      That whould be lovely.
      Gnight
    • RamDemon
      By RamDemon
      Hi,
      Did anyone tried to  use Redis  with AutoIT as store DB ?
       Redis <-- more about it
      Mladen
    • Wingens
      By Wingens
      I want to create a program/script wich uses a database to store it's data.
      Now I am puzzeled by wich database server/type I should use.
       
      Can you guys help me?
       
      The program/script I want to create is used for multiple computers, where the insert data en read/modify it afterwords.
      There is going to be adding files to a share and the links as data in de database.
    • TheSaint
      By TheSaint
      Here is a simple program that some of you might appreciate.
      It is a more full fleshed out version of something I worked on a while back as a proof of concept.
      I will just quote the information found in the Program Information dialog.

      Older Screenshot
      INItoSQL DB.zip
      INItoSQL DB v1.1.zip  (see Post #3 for update details)
      INItoSQL DB v1.4.zip  (see Post #4 for update details)
      INItoSQL DB v1.6.zip  (see Post #7 for update details) BIG THANKS to ResNullius for huge speed improvement etc.
      Program requires the sqlite3.dll, not included, but easily enough obtained.
      I have also included the beginnings of a new UDF (SimpleSQL_UDF) I am working, which you can use with the included 'Check conversion.au3' file to check a resulting conversion ... just modify the values for a few variables to suit your situation.
      I've not yet gotten around to coding a testing/checking results script, so I recommend the following open source program, which I have been using for some time. It was here, but is now here at GitHub - DB Browser for SQLite
×