Search the Community

Showing results for tags 'database'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • Chat
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • Forum
  • AutoIt

Calendars

  • Community Calendar

Found 14 results

  1. Good morning guys I'd like to know if there is a way to convert a PDF in CSV or, eventually, in TXT, in order to read from it, like a database... I have a PDF and I think ( I dind't search a lot on the forum ) with AutoIt, but I'd like work with Excel styles... Does anyone know a good program which convert PDF to CSV? PS: the PDF file is 5 MB, and it contains 439 pages... Thanks everyone for the help
  2. Using the MSSQL.udf I asked this question once, but haven't had a chance to reply to my old post so posting a new one instead of bouncing the old post. I'm trying to retrieve data from my database in Microsoft SQL Server 2014. When I tried the code below; empty message box return and no error approaches. $Connection = _MSSQL_Con($ServerAddress, $ServerUsername, $ServerPassword, $ServerDBName) $Testquery = _MSSQL_Query($Connection, "SELECT User_ID From Banker where Manager like '%Jason%'") msgbox(0, "Test", $Testquery) exit
  3. For the SQLite developers... New version 1.0.0.6 This is a Report Generator for SQLite Database. The script supports up to two dynamic parameters per report, see the 'SQLite Reports.ini' file for more details. Single Date, period, string or number input are possible I provide a full functional example using the Chinook Demo Database (http://chinookdatabase.codeplex.com), so everyone can test it. All required files are contained in the zip file. You can download the zipfile in the link hereunder. I created 20 different reports, from which 4 are system reports and one is not linked to any table. Features: Up to 2 dynamic Parameters via input dialog box per reportMulti-line fields will display in a separate Window when you click on the cell containing multiline data (which cannot be displayed in a listview, if you click on a normal cell, the window will disapearDouble click on a row and a new Window will display the row vertically, usefull for rows with many columns.Export to ExcelFast, even with several thousands of rows (see Track report)SQL that do not deliver data (no rows, no columns) will display a popup message instead (for example using the VACUUM command)Of course you have to know SQL but I guess that everyone who programs SQLite will, right? The SQL scripts are stored in the ini file, where 3 sections ‘Titles, SQL and Parameters’ contain all the elements required for a report. For simplicity, I decided to store the SQL script in the ini file as a single line. The drawback is that the SQL is not easy to read or to maintain, but look at the example ' Invoice with details' where I join 8 tables. It's absolutely functional. Read the [sql readme] section for more details about dynamic parameters. I think its usage is pretty obvious. Know issues: When compiled as 64bit, double click does not function in the main menu, I don't know why but I cannot get this fixed. If anyone knows, I would be pleased to get some help on this. All the other functions, including the double click in the ListView do work. The script will use SQLite3.dll or SQLite3_x64.dll if compiled as a x64 App. The script is a simplified version of my multi-platform Database Report Generator using Active-X DB connection. This SQLite report script (1600 lines) has very basic features compared to the latter (for example it fully integrates the ExcelChart UDF developed by water) but it has 13000 lines and is not provided has open source (sorry L, an early version was released on this forum in 2009 but for Oracle only) I hope this one will be useful for you, I had some fun with it, took me 3 evenings to do the job. Enjoy. GreenCan Updates 1.0.0.6: Bug fixes (thanks jpm)Better GUI windows managementExcel export optimized_COMError implementedFull package: http://users.telenet.be/GreenCan/AutoIt/SQLite_Reports_1.0.0.6.zip (If you downloaded the full package of the previous version, you don't have to download it again) SQLite Reports 1.0.0.6.au3 SQLite Reports.ini : [General] ;Database=Chinook_Sqlite.sqlite Database=Chinook_Sqlite_AutoIncrementPKs.sqlite DateFormat=YYYY-MM-DD [Titles] Report=Album Report=sqlite_sequence (shows number of records for each table) Report=Artist Report=Customer Report=All Employees Report=Employees birthday - period Report=Employees birthday - Name contains Report=Genre Report=Invoice Report=Invoice - amount between Report=Track Report=MediaType Report=Invoice with details - one invoice selection Report=Playlist Report=PlaylistTrack Report=Non-Database - Date formats examples Report=System - Tables and fields (sqlite_master) Report=System - List Tables of DataBase (sqlite_master) Report=System - SQLite version Report=System - Database cleanup (Vacuum) [SQL] Report=SELECT AlbumId, Title, ArtistId FROM Album Report=SELECT name,seq FROM sqlite_sequence Report=SELECT ArtistId, Name FROM Artist ORDER BY Name Report=SELECT CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId FROM Customer ORDER BY CustomerId Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE BirthDate between '%d1%' AND '%d2%' Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE LastName || ' ' || FirstName like '%%1%%' Report=SELECT GenreId, Name FROM Genre Report=SELECT InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice Report=SELECT InvoiceId, CustomerId, Date(InvoiceDate), BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice WHERE Total between %1% AND %2% Report=SELECT TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice FROM Track Report=SELECT MediaTypeId, Name FROM MediaType Report=SELECT Invoice.InvoiceId, Date(Invoice.InvoiceDate) as "Invoice Date", Customer.FirstName, Customer.LastName, Invoice.BillingAddress, Invoice.BillingCity, Invoice.BillingState, Invoice.BillingCountry, Invoice.BillingPostalCode, Track.Name as "Track Name", Album.Title as "Album", MediaType.Name as "Media", Genre.Name as "Genre", Artist.Name as "Artist", Track.Composer, InvoiceLine.UnitPrice, InvoiceLine.Quantity, Invoice.Total FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId INNER JOIN Customer ON Invoice.CustomerId = Customer.CustomerId INNER JOIN Track ON InvoiceLine.TrackId = Track.TrackId INNER JOIN Album ON Track.AlbumId = Album.AlbumId INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId INNER JOIN Genre ON Track.GenreId = Genre.GenreId INNER JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId WHERE Invoice.InvoiceId = %1% Report=SELECT * FROM Playlist Report=SELECT * FROM PlaylistTrack Report=SELECT date('now') as "today", date('now','start of month','+1 month','-1 day') as "last day current month", datetime(1092941466, 'unixepoch') as "unix timestamp", datetime(1092941466, 'unixepoch', 'localtime') as "unix timestamp, compensate for, local timezone", strftime('%s','now') as "current unix timestamp", julianday('now') - julianday('1776-07-04') as "days since US Independence", strftime('%s','now') - strftime('%s','2004-01-01 02:34:56') as "Time elapsed", date('now','start of year','+9 months','weekday 2') as "first Tuesday in October", (julianday('now') - 2440587.5)*86400.0 Report=select * from sqlite_master Report=SELECT name FROM sqlite_master WHERE type='table' Report=SELECT sqlite_version() as "sqlite version" --, sqlite_source_id() as "sqlite source_id" Report=VACUUM [Parameters] Report= Report= Report= Report= Report= Report= Report=Name contains|string will match any position in FirstName or LastName Report= Report= Report=Total between|For fraction use a dot (eg: 5.95),and|For fraction use a dot (eg: 5.95) Report= Report= Report=Invoice ID| Report= Report= Report= Report= Report= Report= Report=
  4. New release. 19 June 2013 (1.0.0.4) There is a relation with this topic SQLite ListView and BLOB demo but I decided to start a completely new Topic because the approach is considerably different from the two previous examples which were only kick-offs to this demo. This example shows how binary objects can be recognized natively in a database BLOB field without having to link to other fields that may contain information of the data object. In the demo I used 2 approaches for native recognition 1. For multi-type binary objects, the file name is added in the header of the BLOB Multi-type object can be images or any other kind of file. Because of the object header data, there is no need to identify the object in the binary code 2. Objects without header data, this works only for images, an algorithm will identify the type of image. The demo shows what happens whit objects which are not identifiable, see example 5. Credits to: 1. trancexx: GIFAnimation.au3 '?do=embed' frameborder='0' data-embedContent>> 2. smashly: _ImageResize() Resizes and converts different graphicformats 3. rover: Customize Draw of Listview rows Optimizations of WS_NOTIFY I also thank rover for giving a second method to resolve the image space issue. I implemented the one proposed by KaFu, because very simple to implement 4. KaFu: Solved the Listview issue with image space in Columns one. 5. jchd: For some hints and background info on SQLite 6. Yashied: WinAPIEx.au3 '?do=embed' frameborder='0' data-embedContent>> new release. Version 1.0.0.4 What's new: - added fully generic Add, Edit, Add/Copy, Delete and Find buttons. With fully generic I mean, you don't have to bother about the table content, GUI field inputs will populate accoring to the table definition. - Added Field validation, also according to how the columns were defined in the table. (see GUI dynamic input validation for more information) tested a thousand times... on W7 and WXP 32/64 For a working example you have to download 2 files (see links in between the horizontal lines: SQLite GreenCan_demo BLOB in Listview 1.0.0.4.zip GreenCan_demo2.zip (if you already did, don't mind downloading it again) Note: For the Edit GUI, you will notice that sometimes one field is not editable, marked as (*PK) in the description. The field is a 'Primary Key autoincrement'. When appending the row, the PK will automatically increment, therefore it is not allowed to edit the field. Other fields mared (*) cannot be empty, you can only save the row if these fields contain data. In the case (*PK) is editable, you have to put unique data for the Primary key. If you do a copy/add without changing the field content, you will get a not unique Error. Special case: I don't allow empty primary key (NULL), while SQLite does, but it's pretty useless anyhow because you can only have 1 NULL in a Primary key. I have also included a very small non BLOB database, for example 0, to show that the generic edit/add works also here. SQLite GreenCan_demo BLOB in Listview 1.0.0.4.zip and don't forget to download this zip file to complete the required files for the demo http://users.telenet.be/GreenCan/AutoIt/GreenCan_demo2.zip I let you explore the demo and please give me feedback. GreenCan
  5. Hi, I'm trying to connect to Firebird database via AutoIt. Something that was supposed to be a cakewalk so far ended with no succes, as usual hoping for your help. I'm using the following Firebird Direct Access for Visual Basic DLL: http://sourceforge.net/projects/fbdll4vb/files/. And the code below comes from Stephen Podhajecki (Eltorro) Firebird UDF (only parts I need at the moment): The code above gives me this: The specific kind of server\database I need to connect to is belong to following application if that matters: http://www.projetex.com/, it is using Firebird 2.5. While ODBC\123 comes from following settings in the Server Administrator utility: I've installed a VM with above mentioned application, server and databse running on it so I have admin rights to access it for testing. Nevertheless when I tried to use a gsec (a command prompt utility that comes with the app) to display a list of users it only gave me this: gsec -user Admin -password admin -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -display use gsec -? to get help Your user name and password are not defined. Ask your database administrator to setp up a Firebird login. unable to open database. So I changed it to ODBC, and there was some progress: gsec -user ODBC -password 123 -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -display invalid request BLR at offset 37 table USERS is not defined I tried to add a user mjolnir\123 and this time there was no progress: gsec -user ODBC -password 123 -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -add mjolnir -pw 123 An error occured while attempting to add the user. invalid request BLR at offset 51 table USERS is not defined It seems I'm not quite understand how the damn thing works. The Server Administrator utility have plenty of users including the admin, all of the have access to read the database and could do this via Projetex client, neither cann't do this nor via gsec, not programmaticlly via AutoIt code above. And then it says smth about "table USERS" which is not defined apparently, looks like database users is not the same thing as server users and I'm doing it wrong.
  6. Hi all, This is an UDF for working with Access(2007 above) databases. I would like to say thanks to @spudw2k for his wonderful ADODB udf. That is my inspiration. And i borrowed three functions from him. The difference of this UDF is that it is using SQL statements as parameters. So if you know the SQL, then you can easily work with this UDF. Functions in this UDF 1. _Start_Connection 2. _Close_Connection 3. _Create_Table 4. _Delete_Table 5. _Alter_Table 6. _Delete_FromTable 7. _Insert_Data - You can use this to update or delete data 8. _Get_Records This is an example. This example uses an access database named Test. It is packed with the zip file #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.0 Author: kcvinu Date : sep 2015 Script Function: Examples of Access_UDF Template AutoIt script. #ce ---------------------------------------------------------------------------- #include "Access_UDF.au3" #include <Array.au3> ; only for displaying data Example1() Func Example1() Local $MsgBox1 = MsgBox(4, "Access UDF Examples", " Starting Connection example, Ready ?") If $MsgBox1 = 6 Then ; We are starting a connection Local $Connection = _Start_Connection(@ScriptDir & "\Test.accdb;") ConsoleWrite($Connection & " Started" & @CRLF) ; Look for the function status Else Exit EndIf Local $MsgBox2 = MsgBox(4, "Access UDF Examples", " Starting Create Table example, Ready ?") If $MsgBox2 = 6 Then ; We need to create a new table Local $CreateTable = _Create_Table("CREATE TABLE TestTable(Column1 Text, Column2 Text);") ConsoleWrite($CreateTable & " Table Created" & @CRLF) Else Exit EndIf Local $MsgBox3 = MsgBox(4, "Access UDF Examples", " Starting Alter Table example, Ready ?") If $MsgBox3 = 6 Then ; We need to add a column to that table Local $AlterTable = _Alter_Table("ALTER TABLE TestTable ADD Column3 int;") ConsoleWrite($AlterTable & " Table Alterd" & @CRLF) Else Exit EndIf Local $MsgBox4 = MsgBox(4, "Access UDF Examples", " Starting Insert data example, Ready ?") If $MsgBox4 = 6 Then ; Insert some data Local $InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');") ConsoleWrite($InsertData & " Data inserted" & @CRLF) Else Exit EndIf Local $MsgBox5 = MsgBox(4, "Access UDF Examples", " Starting Update table example, Ready ?") If $MsgBox5 = 6 Then ; Let us update the table Local $UpdateData = _Insert_Data("UPDATE Table1 SET Col1 = 'Autoit Is Great', Col2 = 'Coding is Really Fun' WHERE Col3 = 'AutoIt Rocks';") ConsoleWrite($UpdateData & " Table Updated" & @CRLF) Else Exit EndIf Local $MsgBox6 = MsgBox(4, "Access UDF Examples", " Starting Get records example, Ready ?") If $MsgBox6 = 6 Then ; Now, collect some data Local $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;") _ArrayDisplay($GetData) Else Exit EndIf Local $MsgBox7 = MsgBox(4, "Access UDF Examples", " Starting Delete from Table example, Ready ?") If $MsgBox7 = 6 Then ; Let us delete the whole data from that table, but not the table Local $DeleteAll = _Delete_FromTable("DELETE FROM Table1;") ConsoleWrite($DeleteAll & " All data deleted from Table" & @CRLF) Else Exit EndIf Local $MsgBox8 = MsgBox(4, "Access UDF Examples", " Starting Delete the entire table example, Ready ?") If $MsgBox8 = 6 Then ; Now, we are going to delete the entire table Local $DeleteTable = _Delete_Table("DROP TABLE TestTable;") ConsoleWrite($DeleteTable & " Table deleted" & @CRLF) Else Exit EndIf ; Last but not least, close the connection. _Close_Connection() ConsoleWrite("Examples Over...." & @CRLF) EndFunc ;==>Example1 Here is the files. Access UDF.rar Access UDF.zip
  7. This is an example how to access/query different kind of Databases with AutoIt. The demo contains also test Databases, so the script functions for most of the provided database connections. Databases covered here: Oracle (no demo database for this one) MS SQL Server (no demo database for this one) SQLite (requires installation of the SQLite ODBC driver software available here: http://www.ch-werner.de/sqliteodbc/ ) Of course SQLite is natively available in AutoIt but in some cases it might be preferable to use the ODBC approach accessing the database via the same scripted method, for example if you use different Databases in the same script, it is much easier to code. Excel DBase DBF MS Access CSV Text driver (semicolon delimited and comma delimited) The example script is based on Kinshima’s Oracle SQL example . I like the Dim by pack method, much better than redim one row at the time, which too slow. All the databases have the same data content (‘Timeline_of_space_exploration’) to keep the examples similar for every database. The result of each SQL will be displayed in Arraydisplay, togheter with a window containing the ADO-less connection string (yellow) and SQL. #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Res_Comment=Comprehensive DSN-Less Database connection demo #AutoIt3Wrapper_Res_Description=DSN-Less Database connection demo #AutoIt3Wrapper_Res_Fileversion=1.0.0.0 #AutoIt3Wrapper_Res_LegalCopyright=GreenCan #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <array.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <ButtonConstants.au3> Opt('MustDeclareVars', 1) Global $oODBC_Connection = ObjCreate("ADODB.Connection") Global $oODBC_RecordSet = ObjCreate("ADODB.Recordset") Global $bODBC_conn = False Global $oErrADODB = ObjEvent("AutoIt.Error", "_ErrADODB") Global $bConn_error = False Global $sConnectionString, $sSQL Global Const $iDark_Blue = 0x2601D3 Global Const $iLight_Green = 0xEAFFE8 Global Const $iYellow = 0xFFFF99 ; setup GUI to display SQL Global $hGUI_View_SQL = GUICreate("SQL", 500, 500, 10, 10, $WS_CAPTION);, $WS_EX_TOPMOST) Global $hConnString = GUICtrlCreateEdit("", 5, 3, 490, 38, BitOR($ES_AUTOHSCROLL, $ES_READONLY, $ES_WANTRETURN, $WS_HSCROLL)) GUICtrlSetColor(-1, $iDark_Blue) GUICtrlSetBkColor(-1, $iYellow) Global $ViewSQL = GUICtrlCreateEdit("", 5, 45, 490, 450, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_READONLY, $ES_WANTRETURN, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlSetColor(-1, $iDark_Blue) GUICtrlSetBkColor(-1, $iLight_Green) GUISetState() #========== Demo ============= Global $sDatabaseLocation = @ScriptDir & "\TestDatabases" #========== Oracle ============= ;~ #cs ;~ Not demo-able except if you have an Oracle instance ;~ #ce $sConnectionString = 'DRIVER={Oracle in OraClient11g_home1};DBQ=myDatabase.world;uid=myUserID;pwd=myPassword;' $sSQL = 'SELECT sysdate AS "Sysdate Raw", to_char(sysdate, ''Dy DD/MM/YY HH24:MI'') AS "Now" FROM dual' Format_SQL($sSQL, $sConnectionString) MsgBox(0,"Oracle SQL","You require to have access to an Oracle instance and modify the connection string before proceeding without error." & @CRLF & "The current Connection string will generate a COM error.") Query($sConnectionString, $sSQL, 500, "SQL Oracle") #========== MS SQL Server ============= MsgBox(0,"MS SQL Server","You require to have access to a SQL Server instance and modify the connection string before proceeding without error." & @CRLF & "The current Connection string will generate a COM error.") $sConnectionString = 'DRIVER={SQL Server};SERVER=ServerNameorIP;DATABASE=DatabaseServer;Trusted_Connection=no;uid=myUserID;pwd=myPassword;' $sSQL = 'SELECT 18 AS "Circle Radius",round(PI() * power(18,2) ,3) AS "Surface of circle", round(PI() * 18 * 2,3) AS "Circumference of circle"' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "MS SQL Server") #========== SQLite ============= #cs Accessing SQLite via ODBC requires installation of the SQLite ODBC driver software available here: http://www.ch-werner.de/sqliteodbc/ Note: You will have to install the 32-bit driver, as far as I could test, x6' version, sqliteodbc_w64 doesn't function on my x64 Windows You can access SQLite natively FROM AutoIt For example look at my topics - SQLite Report generator http://www.autoitscript.com/forum/topic/149767-sqlite-report-generator/#entry1068258 - SQLite demonstration of native recognition of BLOB object in Listview http://www.autoitscript.com/forum/topic/150876-sqlite-demonstration-of-native-recognition-of-blob-object-in-listview/#entry1078492 #ce $sConnectionString = 'DRIVER=SQLite3 ODBC Driver;Database=' & $sDatabaseLocation & '\Timeline_of_space_exploration.xdb;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;' ;~ $sConnectionString = 'DRIVER=SQLite3 ODBC Driver;Database=D:\SQLite\Netline.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;' $sSQL = 'SELECT * FROM space_exploration;' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQLite") #========== Excel ============= #cs The sheet range to be queried has a name defined AS SpaceTable Check the Excel sheet by selecting all (Ctrl-A) #ce $sConnectionString = 'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=' & $sDatabaseLocation & '\Timeline_of_space_exploration.xlsx;' $sSQL = 'SELECT Format (Date, ''dd-MM-yyyy'') AS "Event", Mission_Achievements AS "Mission Achievements", Country, Organization, Mission_Name AS "Mission Name" ' & _ ' FROM SpaceTable ORDER BY Date' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQL Microsoft Excel") #========== DBase DBF ============= #cs Old Borland/Clipper Database #ce $sConnectionString = 'DRIVER={Microsoft dBase Driver (*.dbf)};Dbq='& $sDatabaseLocation & ';' $sSQL = 'SELECT Format (DATE, ''dd MMM yyyy'') AS "Event", MISSION AS "Mission Achievements", Country, ORG AS "Organization", MNAME AS "Mission Name" ' & _ ' FROM SPACE.DBF WHERE (((DATE) Between #22/04/1900# And #22/04/2015# +1)) AND UCASE(Country) <> ''USA'' ORDER BY Date' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQL DBase") #========== MS Access ============= #cs The MSAccess Example.mdb is protected with following password: DatabaseSQL #ce $sConnectionString = 'DRIVER={Microsoft Access Driver (*.mdb)};Dbq=' & $sDatabaseLocation & '\Example.mdb;uid=;pwd=DatabaseSQL;' $sSQL = 'SELECT Format (Date, ''dd MMM yyyy'') AS "Event", Mission_Achievements AS "Mission Achievements", Country, Organization, Mission_Name AS "Mission Name" ' & _ ' FROM Timeline_of_space_exploration WHERE (((Date) Between #22/04/1900# And #22/04/2015# +1)) ORDER BY Date' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQL MS Access") #========== CSV Text driver, semicolon delimited ============= #cs Some variations of the same SQL requires schema.ini in the same folder AS the text file https://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx schema.ini settings: [Timeline_of_space_exploration_semicolon.txt] ColNameHeader=True Format=Delimited(;) MaxScanRows=1 #ce $sConnectionString = 'DRIVER={Microsoft Text Driver (*.txt; *.csv)};Dbq=' & $sDatabaseLocation & ';Extensions=asc,csv,tab,txt;' $sSQL = 'SELECT Format (Date, ''dd MMM yyyy'') AS "Event", Mission_Achievements AS "Mission Achievements", Country, ' & _ 'Organization, Mission_Name AS "Mission Name" FROM Timeline_of_space_exploration_semicolon.txt ORDER BY Date' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQL Text semicolon delimited") #========== CSV Text driver, comma delimited ============= #cs schema.ini settings: [Timeline_of_space_exploration.csv] ColNameHeader=True Format=Delimited(,) DecimalSymbol=. MaxScanRows=1 #ce $sSQL = 'SELECT Format (Date, ''dd MMM yyyy'') AS "Event", Mission_Achievements AS "Mission Achievements", Country, ' & _ 'Organization, Mission_Name AS "Mission Name" FROM Timeline_of_space_exploration.csv ORDER BY Date' Query($sConnectionString, $sSQL, 500, "SQL Text comma delimited") ; USA only $sSQL = 'SELECT Format (Date, ''dd MMM yyyy'') AS "Event", Mission_Achievements AS "Mission Achievements", Country, ' & _ 'Organization, Mission_Name AS "Mission Name" FROM Timeline_of_space_exploration.csv WHERE (((Date) Between #22/04/1900# And #22/04/2015# +1)) and UCASE(Country) = ''USA''' Format_SQL($sSQL, $sConnectionString) Query($sConnectionString, $sSQL, 500, "SQL Text comma delimited - USA Only") #========== End Demo ============= Exit Func Query($sConnectionString, $sSQL, $iMaxRows, $sTitle) If _ODBC_OpenConnection($sConnectionString) Then Local $aRecords = _ODBC_GetRecords($sSQL, 500, $iMaxRows) _ODBC_CloseConnection() ; set header string Local $sHeader = "" For $i = 0 to UBound($aRecords, 2) - 1 $sHeader &= $aRecords[0][$i] & "|" Next $sHeader = StringTrimRight($sHeader, 1) ; trim of last separator _ArrayDisplay($aRecords, _ $sTitle & " - " & "Query result: " & UBound($aRecords)-1 & " rows" & (UBound($aRecords)-1 = $iMaxRows ? " (result limited to " & $iMaxRows & " rows)" : "") , _ "1:" & UBound($aRecords)-1, _ 0, _ Default, _ $sHeader) EndIf EndFunc ;==>Query Func _ExeSQL($sSQL) If Not IsObj($oODBC_Connection) Then Return -1 $oODBC_Connection.Execute($sSQL) If $oErrADODB.number Then MsgBox($MB_YESNO + $MB_ICONERROR + $MB_DEFBUTTON2, "_ExeSQL", $sSQL & @CRLF & @CRLF & 'Error. ' & @CRLF & 'Exiting.') Exit Else Return 1 EndIf EndFunc ;==>_ExeSQL Func _ODBC_CloseConnection() $bODBC_conn = False Return $oODBC_Connection.Close EndFunc ;==>_ODBC_CloseConnection Func _ODBC_OpenConnection($sConnectionString) If Not IsObj($oODBC_Connection) Then Return -1 If Not @Compiled Then ConsoleWrite ("@@ Debug(" & @ScriptLineNumber & ") : " & $sConnectionString & @CR) $oODBC_Connection.ConnectionString = ($sConnectionString) $bConn_error = False $oODBC_Connection.Open If $bConn_error = True Then Return False EndIf $bODBC_conn = True Return True EndFunc ;==>_ODBC_OpenConnection Func _ODBC_GetRecords($sSQL, $iArrayIncrement = 250, $iRows = 0) ; syntax: _ODBC_GetRecords(SQL, ArrayIncrement, MaxRows) If Not $bODBC_conn Then MsgBox($MB_OK, 'Error', 'Connection failure') Return EndIf If $iArrayIncrement = 0 Then $iArrayIncrement = 250 If Not IsObj($oODBC_Connection) Then Return -1 If Not IsObj($oODBC_RecordSet) Then Return -2 _ODBC_OpenRecordset($sSQL) Local $aRecords[1][1] If $oODBC_RecordSet.EOF = True Then _ODBC_CloseRecordSet() Return False EndIf $oODBC_RecordSet.MoveFirst Local $x = 0 ReDim $aRecords[1][$oODBC_RecordSet.Fields.Count] For $objField In $oODBC_RecordSet.Fields $aRecords[0][$x] = $objField.Name $x += 1 Next Local $iAIn = UBound($aRecords) + $iArrayIncrement ReDim $aRecords[$iAIn][$oODBC_RecordSet.Fields.Count] $oODBC_RecordSet.MoveFirst Local $y = 0 Do $x = 0 $y += 1 For $objField In $oODBC_RecordSet.Fields $aRecords[$y][$x] = $objField.Value $x += 1 Next If $y = $iAIn - 1 Then $iAIn += $iArrayIncrement ReDim $aRecords[$iAIn][$oODBC_RecordSet.Fields.Count] EndIf $oODBC_RecordSet.MoveNext If $iRows > 0 Then If $y = $iRows Then ;_ArrayDisplay($aRecords, @ScriptLineNumber) ReDim $aRecords[$y + 1][$oODBC_RecordSet.Fields.Count] ;_ArrayDisplay($aRecords, @ScriptLineNumber) Return $aRecords EndIf EndIf Until $oODBC_RecordSet.EOF ReDim $aRecords[$y + 1][$oODBC_RecordSet.Fields.Count] _ODBC_CloseRecordSet() Return $aRecords EndFunc ;==>_ODBC_GetRecords Func _ODBC_OpenRecordset($sSQL); If Not IsObj($oODBC_Connection) Then Return -1 If Not IsObj($oODBC_RecordSet) Then Return -2 Return $oODBC_RecordSet.Open($sSQL, $oODBC_Connection, 0, 1) EndFunc ;==>_ODBC_OpenRecordset Func _ODBC_CloseRecordSet() Return $oODBC_RecordSet.Close EndFunc ;==>_ODBC_CloseRecordSet Func _ErrADODB() MsgBox($MB_ICONWARNING, "ADODB COM Error", "We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oErrADODB.description & @CRLF & _ "err.windescription:" & @TAB & $oErrADODB.windescription & @CRLF & _ "err.number is: " & @TAB & Hex($oErrADODB.number, 8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $oErrADODB.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oErrADODB.scriptline & @CRLF & _ "err.source is: " & @TAB & $oErrADODB.source & @CRLF & _ "err.helpfile is: " & @TAB & $oErrADODB.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oErrADODB.helpcontext _ ) ConsoleWrite($oErrADODB.description & @CR) $bConn_error = True Return SetError(@error, @error, 1) EndFunc ;==>_ErrADODB Func Format_SQL($sSQL, $sConnectionString) ; format SQL script a bit ; the formating does not consider UNION, Local $stempSQL, $sSQLPart1, $sSQLPart2, $sSQLPart3, $sSQLPart4, $sSQLPart5, $sSQLPart6 $stempSQL = $sSQL ; LIMIT - should be after FROM ! If StringInStr($stempSQL, "LIMIT", 0, -1) > 0 Then If StringInStr($stempSQL, "LIMIT", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then $sSQLPart6 = @CRLF & "LIMIT" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "LIMIT", 0, -1) + 5) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "LIMIT", 0, -1) + 1), 2) EndIf EndIf ; ORDER BY - should be after FROM ! If StringInStr($stempSQL, "ORDER BY", 0, -1) > 0 Then If StringInStr($stempSQL, "ORDER BY", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then $sSQLPart5 = @CRLF & "ORDER BY" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "ORDER BY", 0, -1) + 8) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "ORDER BY", 0, -1) + 1), 2) EndIf EndIf ; GROUP BY - should be after FROM ! If StringInStr($stempSQL, "GROUP BY", 0, -1) > 0 Then If StringInStr($stempSQL, "GROUP BY", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then $sSQLPart4 = @CRLF & "GROUP BY" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "GROUP BY", 0, -1) + 8) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "GROUP BY", 0, -1) + 1), 2) EndIf EndIf ; WHERE - should be after FROM ! If StringInStr($stempSQL, "WHERE", 0, -1) > 0 Then If StringInStr($stempSQL, "WHERE", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then $sSQLPart3 = @CRLF & "WHERE" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "WHERE", 0, -1) + 5) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "WHERE", 0, -1) + 1), 2) EndIf EndIf ; INNER JOIN If StringInStr($stempSQL, "INNER JOIN", 0, -1) > 0 Then $stempSQL = StringReplace($stempSQL, "INNER JOIN", @CRLF & @TAB & @TAB & "INNER JOIN") EndIf ; FROM If StringInStr($stempSQL, "FROM", 0, -1) > 0 Then $sSQLPart2 = @CRLF & "FROM" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "FROM", 0, -1) + 4) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "FROM", 0, -1) + 1), 2) EndIf ; SELECT If StringInStr($stempSQL, "SELECT", 0, 1) > 0 Then $sSQLPart1 = "SELECT" & @CRLF & @TAB & StringReplace(StringReplace(StringTrimLeft($stempSQL, StringInStr($stempSQL, "SELECT", 0, 1) + 6),", ", ",") , ",", "," & @CRLF & @TAB) $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "SELECT", 0, 1) + 1), 2) EndIf GUICtrlSetData ( $hConnString, $sConnectionString) GUICtrlSetData ( $ViewSQL, $sSQLPart1 & $sSQLPart2 & $sSQLPart3 & $sSQLPart4 & $sSQLPart5 & $sSQLPart6 & @CRLF ) EndFunc ;==>Format_SQL Script and database packs SQL demo DSN-less connections (multi-DB).zip GreenCan
  8. Hey Every 1 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
  9. Hi there, I need to make a little program that takes some text input from user and saves it in a database. Then later, user presses the hotkey of each text, my program will retrieve it and send to the top most window. I have made the GUI. Now i need to learn about how autoit deals with databases. I have access 2007 in my system. So i would like to make a table in access. Is it possible to connect access database to an autoit program. If so how. ?. Thanks in advance.
  10. Hello all, this script(wrapper) uses Alternative Data Streams to store INI formatted data in the active executable even while it is running. This is great for standalone executables, or storing data you don't want a user being able to edit! There are four simple commands: Func _iniwrite($section, $key, $value, $stream = "DEFAULT") return IniWrite(@ScriptFullPath&":"&$stream, $section, $key, $value) EndFunc Func _iniread($section, $key, $default = "", $stream = "DEFAULT") return IniRead(@ScriptFullPath&":"&$stream, $section, $key, $default) EndFunc Func _inidelete($section, $key = "", $stream = "DEFAULT") if $key <> "" Then Return IniDelete(@ScriptFullPath&":"&$stream, $section, $key) Else Return IniDelete(@ScriptFullPath&":"&$stream, $section) EndIf EndFunc Func _inirenamesection($section, $newsection, $flag = 0, $stream = "DEFAULT") return IniRenameSection(@ScriptFullPath&":"&$stream, $section, $newsection, $flag) EndFunc Simply copy and paste these functions into your script!
  11. Continuation of topic started I have been testing various times and configurations after reading the tips given and things are a bit better, though now a new issue has come up - after installing the latest version of AutoIt (3.3.10.2) _SQLite_Startup() is taking a LOT of time to load. Odd thing is, it is not consistent (I hate that....) - though times vary from 5 seconds (which is 'long', IMHO) to OVER 60 seconds! ConsoleWrite("various states disabled/hidden " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) ; ************************************ read from / update the database FileInstall("sqlite3.dll", @ScriptDir & "", 1) ConsoleWrite("SQL0 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) _SQLite_Startup() If @error Then MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!") Exit -1 EndIf ConsoleWrite("SQL1 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) Console output from one test (not 'typical', but certainly 'common' to be very long.....) Prior to loading 3.3.10.2 times were 'fast' (never noticed any lag enough to care to time it), though now when I start the program it is very noticably delayed. I want to address various other tweaks and such as discussed by @jchd and certainly the differences and tweaks for working with disk based or memory based databases (in this project, I need one disk based and it may be that I go with memory based for the other, more temporary), though now, this is my #1 priority! It doesn't matter if it is disk or memory based at this point, I'm just doing the Startup and with such L O N G times, it has my project stalled (I can't put something out that takes over a minute to start!). BTW (not sure how important it might be), the #include list (which seems to grow constantly...) is now I will be eliminating some of those, I'm sure as I tweak this program for speed (one of them being "AssocArrays" - I have found it to be (sadly, as I am used to named array elements...) causing some of the slowness in the program. I have elimanted most of the AssocArray calls in the main loop and increased times about 20% (calling it nearly 40 times in the loop, with 30K+ files, it made a difference!) Anyway, more on that stuff later - right now, I need to understand what to do about _SQLite_Startup()! Ideas/suggestions?
  12. Hello, I've created a script for a Bingo / Lottery kind of game in PHP. Now I want to make this a standalone application in AutoIT. I really dont know how to do that as i´m just a Autoit-beginner. Can you help me with it? In general I have the following files: data.csv - the "database" where the bingo-numbers are stored, together with the name of the candidate index.htm - to show the 'program' in a browser input.php - to take care of input results.php - to show the winners numbers.txt - the winning numbers to compare with I have the following files: Input.php: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN"> <html><head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <style> body { font-family:Times New Roman, Verdana,Geneva; font-size:12px; color:#000000; margin-left:20px; background:#F7F5DF; width: 300px; } H2 { font-family:Times New Roman, Geneva; font-size:16pt; color:#840018; text-align:center; } </style> <link rel=stylesheet href="style.css" TYPE="text/css"> <title>Input</title> <?php $sepsign = '/;/'; $text = ''; $wnumbers = ''; $counter = 0; $filed = "data.csv"; $fileg = "numbers.txt"; $in_numbers = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0); if ($_SERVER['REQUEST_METHOD'] == 'POST') { $name = $_POST['name']; $in_numbers = $_POST['$in_numbers']; } $fd = fopen($fileg,"r") or die ("Unable to open $file."); $text = fread($fd,filesize($fileg)); $wnumbers = preg_split($sepsign,$text); // wnumbers is an array with winning numbers Fclose($fd); for ($j = 0; $j < 10; $j++) { for ($i = 0; $i < 10; $i++) { if ($wnumbers[$i] == $in_numbers[$j]) { $counter++; } } } // $counter will contain the number of matches on the input # Append and write the line in a file <number of matches>;<name>;<number 1>;<number 2>; etc $fdb = fopen($filed,"a+"); fwrite($fdb,$counter . ";" . $name); for ($i = 0; $i < 10; $i++) { fwrite($fdb,";" . $in_numbers[$i]); } fwrite($fdb,"\r\n"); fclose($fdb); ?> <h2><br>Your input has been stored successfully!</h2> </body></html> Results.php: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN"> <HTML><head> <meta http-equiv="Content-Type" content="text/HTML; charset=iso-8859-1"> <style> body { font-family:Times New Roman, Verdana,Geneva; font-size:12px; color:#000000; margin-left:20px; background:#F7F5DF; width: 300px; } H2 { font-family:Times New Roman, Geneva; font-size:16pt; color:#840018; text-align:center; } </style> <title>Result</title> </head><BODY> <h2>Result of the draw</h2><hr><br> <?php $sepsign = '/;/'; $text = ''; $filed = "data.csv"; $fileg = "numbers.txt"; $fd = fopen($filed,"r") or die ("unable to open $file."); $text = fread($fd,filesize($filed)); $lines = preg_split("/\r\n/",$text); // Will split the file data.csv in lines $count_reg = count($lines); fclose($fd); sort ($lines); reset($lines); for ($i = ($count_reg - 1); $i>($count_reg-4); $i--) { // The three highest scores are showed $collums = preg_split($sepsign,$lines[$i]); print "<b>Name: " . $collums[1] . "<br>"; print "Number of matches: " . $collums[0] . "</b><br>"; print "Input: "; for ($j = 2; $j < 12; $j++) { print $collums[$j]; if ($j <> 11 ) { print ", "; } // to prevent the "," to appear after the last number } print "<br><br>"; } $fd = fopen($fileg,"r") or die ("Unable to open $file."); $text = fread($fd,filesize($fileg)); $wnumbers = preg_split($sepsign,$text); Fclose($fd); print "<br><b>Bingo numbers</b><br><hr>"; for ($j = 0; $j < 11; $j++) { print $wnumbers[$j]; if ($j < 9 ) { print ", "; } // to prevent the "," to appear after the last number } ?> </body></html> Index.html <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN"> <html><head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <style> body { font-family:Times New Roman, Verdana,Geneva; font-size:12px; color:#000000; margin-left:20px; background:#F7F5DF; width: 300px; } H2 { font-family:Times New Roman, Geneva; font-size:16pt; color:#840018; text-align:center; } </style> <title>Bingo game, input numbers.</title> </head><body> <form action="input.php" method="post" name="form"> <center> <h2>Input numbers</h2> lines &nbsp; <input type="text" name="lines" size=20 maxlength=50><br><br> Number &nbsp;1 &nbsp;<input type="text" name="$in_numbers[0]" size=4><br> Number &nbsp;2 &nbsp;<input type="text" name="$in_numbers[1]" size=4><br> Number &nbsp;3 &nbsp;<input type="text" name="$in_numbers[2]" size=4><br> Number &nbsp;4 &nbsp;<input type="text" name="$in_numbers[3]" size=4><br> Number &nbsp;5 &nbsp;<input type="text" name="$in_numbers[4]" size=4><br> Number &nbsp;6 &nbsp;<input type="text" name="$in_numbers[5]" size=4><br> Number &nbsp;7 &nbsp;<input type="text" name="$in_numbers[6]" size=4><br> Number &nbsp;8 &nbsp;<input type="text" name="$in_numbers[7]" size=4><br> Number &nbsp;9 &nbsp;<input type="text" name="$in_numbers[8]" size=4><br> Number 10&nbsp;<input type="text" name="$in_numbers[9]" size=4><br><br> <input type="submit" value="Submit..."></FORM> </center> <p />&nbsp;<p /> </body></html>
  13. Hello, I can't find anything helpful in the forums about querying a Firebird database. Does anyone know if this can be done and how? Thanks
  14. Wow.. It's been a long time since I last used AutoIT ^^ I am currently making games with Construct2 and learning to set up multiplayer games. Since I kinda find AutoIT similar to C2 (Easy use and single threaded(I think C2 is single threaded...)) I wondered if a login or database check for "is online" is possible for autoit? And if it is reasonable to make it with a single threaded application. And if this is one of the things that are taboo to talk about!