SQL UDF for AutoIT

[randallc]

OK - Try Again!

Best, Randall

**** Of course, Now, April07, this is redundant; see examples in helpfile for using these commands built-in!

Best, Randall

I've decided to put the required files in a zip; if you want to use Excel, you will now also need Excel.com from the zip in your script directory (no longer "include") - [put ALL these files in a "Search" directory...]

[if you want the exe for independent download of SQLITE.exe file, SQLITEdownloadPage] [do not use 3.3.3 as import has a bug; USE 3.3.5 AS HERE ]

Hi,

Here is my planned set of UDFs for SQLite3.exe.

Only 1 external file to load, and 1 or 2 UDF files

1. SQLite3.exe. external; command line, goes in @scriptdir , no registration needed.

2. SQLiteExe.au3 main functions.

3. SQL_View.au3 if you want to Browse, use GUI , listview, OWC, export to excel, for SQL DB.

EXAMPLES [Example 3 needs APIRW.au3 for binary read/write - see fixed UDF in Zip file above***]

Many functions are called in _SQLGUI_View($sDb,$sDbTable) ,so example 1 shows the GUI;

Usual requirements are to define $s_Prepare, or $s_Header and $s_ValueLine for many calls; see command list and examples.[@ptrex and @blink234 and @piccaso gave me direction; apologies if they don't like the cmd line or the scripting]

Main command, used by all other calls, is @piccasos _SQLiteExe renamed from his _SQLite_SQLiteExe

*** This will usually be required if Prepare statements are concatenated first (for speed) (except Insert)

General commands are ;

_SQLiteExe($sDatabaseFile, $$s_Prepare, $sOutput, $sSQLiteExeFilename , $fDebug)

_SQL_BeginAndCommit( $sDB, $s_Prepare)

Table Commands

_SQL_CopyTable( $sDB, $sDbTable, $sCopyTable)

_SQL_CountRows($sDb,$sDbTable)

_SQL_CreateIndex($sDb1,$sDbTable, $sIndexName,$sColumnName)

_SQL_CreateQueryTable( $sDB, $sDbTable, $sQueryTable,$s_Prepare)

_SQL_CreateTable( $sDB, $sQueryTable,$sHeader)

_SQL_DropTable( $sDb1, $sDbTable)

_SQL_DropIndex( $sDb1, $sIndexName)

_SQL_DumpTableToText ($sDb, $sDbTable,$sFile,$Separator)

_SQL_GetTableHeader( $sDb, $sDbTable, $s_ListHeader, $sHeader)

_SQL_QueryToText ($sDB,$sDbTable,$s_Prepare,$sDumpFile,$Separator)

_SQL_RenameTable( $sDb1, $sDbTable, $sNewTable)

_SQL_TableNames($sDb)

Record commands.

_SQL_InsertAddtoTable($sDB, $sQueryTable,$s_ValuesLine,$sHeader)

_SQL_PrepareToInsert($sQueryTable,$s_ValuesLine,$sHeader)

_SQL_UpDateItem( $sDb1, $sDbTable,$i_RowID,$s_ColumnName,$s_NewEntry)

CSV commands.

_SQL_CreateDbCSV( $sDB1, $sDbTable,$i_HeaderFirstRow,$i_IndexAllCols,$sCSVFile,$Separator)

_SQL_CreateDbCSVSlow( $sDB1, $sDbTable,$i_HeaderFirstRow=0,$i_IndexAllCols=1 ,$sCSVFile="hi.TXT",$Separator="auto")

_SQL_CreateDbCSVTable( $sDB1, $sDbTable, $Separator, $i_NumCols, $s_ExtraCols, $ar_NumCols, $i_NumColsModifier, $s_DialogFile, $ar_Header, $s_Insert, $s_Header)

_SQL_GetCSVHeader( $Separator, $i_NumCols, $line, $ar_NumCols, $i_HeaderFirstRow, $s_Different

_SQL_GetCSVHeaderRow( $Separator, $i_NumCols, $line, $ar_NumCols, $i_HeaderFirstRow, $s

_SQL_OpenCSV( $sDB1, $s_DialogFile,$sCSVFile,$Separator)

DB commands.

_SQL_SaveAsDB( $sDB, $sDbTable)

_SQL_CreateRandomDb($sDB,$sDbTable,$TotalRandomRows)

_SQL_DialogueDB( $sDB, $sDbTable)

[Go easy on me here!; I am just learning SQL..

best Randall]

Edited April 13, 2007 by randallc

[randallc]

Hi,

I have updated with SQLite3.exe v 3.3.4 [i nfirst post]

As well, new function for writing to a file line (replacing) is added; there was a recent topic on this

This solution allows;

1. Opening huge files (eg >80Mb) which fileread method will not run [Edit; filread opens; I presume stringsplit or the array..?? have problem with large file/ large number replacements?].

2. Quicker than filereadline/ writeline if large file with many replacement lines.

3. Option of being even quicker; transform data to SQLite db3, then replaces 2-3 secs even in huge database

best, Randall

;WriteLineSQL.au3 0_2

#Include <misc.au3>

#include "sqliteExe.au3"

#include "SQL_View.au3"

#include <Date.au3>

$_InitialLines="first hello"&@crlf&"first here"&@crlf&"second hello"&@crlf&"second here"

$s_FileName=@ScriptDir&"\ReplaceTryFile.txt"

FileDelete($s_FileName)

$h_Hand=FileOpen($s_FileName,2)

FileWriteLine($h_Hand,$_InitialLines)

fileclose($h_Hand)

;=========================================

$s_Searchstring="Hello"

$s_Replacestring="===================="

;=========================================

$sTXTLOGFile = @ScriptDir&"\ReplaceTryFile.txt"

local $sNewInput,$sDb

global $sDBTable="FileTable",$i_Execute=0,$sTablePrev="OldFile",$sqlTimerQuery,$sDBTablecopy="CopyCR"

global $s_FormatStamp=StringReplace(StringReplace(_DateTimeFormat( _NowCalc(),0),"/","_"),":","_")

$sDb = @ScriptDir&"\ReplaceTryFile1.db3"; so if you have entered a current db3 filename, it is used straight away

$sqlTimerQuery = TimerInit()

;==============================================

$sNewInput&=_SQL_TextFileOpen($sDb,$sDbTable, $sTXTLOGFile,0,$i_Execute) ; else goes to find it, or text file you want to use......

$sNewInput&=_SQL_ReplaceTextLines($sDb, $sDbTable, $sTablePrev,$s_Searchstring,$s_Replacestring,$i_Execute)

if not $i_Execute and not ( $sNewInput=="") then _SQL_BeginAndCommit( $sDB, $sNewInput); lump all SQL commands to run at end (except import and output))

_SQL_OutputText( $sDB, $sDbTable,$sTXTLOGFile)

;====================================================================

ConsoleWrite('@@ Total) : ' & Round(TimerDiff($sqlTimerQuery), 2) & " MSEC." & @LF) ;### Debug Console

RunWait("notepad.exe " & $sTXTLOGFile ,@ScriptDir);, @SW_MAXIMIZE

;====================================================================

_SQLGUI_View($sDb,$sDBTable,100,0)

Exit

Edited February 20, 2006 by randallc

[mikeytown2]

Can i use this to get info out of Microsoft Access?

Or should i research how to do from this thread?

http://www.autoitscript.com/forum/index.ph...topic=12281&hl=

Right now i use DB-Tool 2 to get the info out of the database, but if i could have autoit do it without grabbing the info from the gui i can make one of my programs more stable (i wont have to use blockinput). Thanks for your feedback!

[randallc]

Hi,

I have not set it up to use Access; I'll be interested in your thoughts on the other link; do I need to install Access to find out?...

Randall

[mikeytown2]

To answer you question you don't need MS Access, but you do need an access file to test it. i don't have either one so i cant really do anything.

I was just thinking back on some software that i wrote for someone, they needed it asap so i used a gui (DB-Tool 2) to get the data from Microsoft access. they didn't care that multiple programs where all over the screen since it's running on a computer that no one usually uses. but in the future i would like to know how to do it the right way.

Thats one nice thing about autoit, you can do it the very quick and dirty way, or the right way. It takes object oriented programing to a whole new level where the gui's of other programs are the objects. Thats why when ever someone asks if autoit is OO i always say yep.