Jump to content

Search the Community

Showing results for tags 'Database'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • 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

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Member Title


Location


WWW


Interests

Found 24 results

  1. 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
  2. Hello, I am facing difficulties connecting to SQL Server using AutoIt and would greatly appreciate some help. I have already tried using the MySQL UDF (User Defined Function) and the latest ODBC connect driver from the provided link here However, I have been unable to establish a successful connection. I have come across posts about this topic, but most of them are over 10 years old, and I suspect they might be outdated. I even attempted to change the driver name in the https://www.autoitscript.com/forum/applications/core/interface/file/attachment.php?id=15889 UDF from "MySQL ODBC 3.51 Driver" to the latest driver I obtained, but I still haven't been able to connect. If anyone has any guidance, suggestions, or updated information on connecting to SQL Server using AutoIt, I would be grateful for your assistance. Thank you in advance for your help! Best regards, Here is a code im trying to connect with... Func _MySQLEnd($oConnectionObj) If IsObj($oConnectionObj) Then $oConnectionObj.close Return 1 Else SetError(1) Return 0 EndIf EndFunc ;==>_MySQLEnd Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 Driver}", $iPort=3306) Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v) If @error or $val = "" Then SetError(2) Return 0 EndIf $ObjConn = ObjCreate("ADODB.Connection") $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort) If @error Then SetError(1) Return 0 Else Return $ObjConn EndIf EndFunc ;==>_MySQLConnect ; Example usage Local $host = "host" Local $user = "user" Local $password = "password" Local $database = "database" Local $conn = _MySQLConnect($host, $user, $password, $database) If $conn Then MsgBox(64, "Success", "Connected to MySQL database successfully!") ; Disconnect from the MySQL server _MySQLEnd($conn) else MsgBox(64, "error ", "connection failed") EndIf
  3. Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not. Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created) Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake) is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row") Example: line 11 does not exist right now. How would I go about to see if it did or not? This is what I have so far: SELECT DISTINCT iv.invoicenum, iv.description FROM invoice AS iv JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid /* = 'nothing' and yet somehow not null? */ WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL) I get 0 results where I should get more than 0.
  4. Only early days at this point, but I have been pondering such a program for a while. As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those. What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ. When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option. A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show. The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point. At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding. Older Screenshots Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max. As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI. My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that. Here is another screenshot, of what you see when you click the larger Cover image. If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such. Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted). Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section. CalibBrowser.zip 1.34 MB - Upload 4 (previous downloads: 1 + 12 + 5 + 282) CalibBrowser (new).zip My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it. Enjoy!
  5. Hi All, Here's a really simple question. I ran the code from the helpfile under: _SQLite_Open Issue is I end up with an error message: SQLite3.dll Can't be Loaded! I placed the *.dll in the include folder, but still nothing. Where must this file be placed.
  6. RamDemon

    AutoIT and Redis

    Hi, Did anyone tried to use Redis with AutoIT as store DB ? Redis <-- more about it Mladen
  7. 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.
  8. 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 1.27 MB (205 downloads) INItoSQL DB v1.1.zip 1.27 MB (181 downloads) (see Post #3 for update details) INItoSQL DB v1.4.zip 1.27 MB (166 downloads) (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
  9. Here is the bare bones of a UDF I have started work on. Mostly just a proof of concept at this stage, and still need to add some functions and dress the UDF up a bit ... to look like a UDF ... though it has my own distinct styling, especially as I have never really developed a UDF before now .... used plenty and modified plenty though. I've even invented my own UDF variable naming convention, which I am sure some of you will be aghast at. I work with what feels best for me, but others are free to adapt if they wish. The idea is to emulate the simplicity of INI files, but gain the benefits of SQL. Two scripts are provided. (1) The UDF, a work in progress - SimpleSQL_UDF.au3 (2) An example or testing script - UDF_Test.au3 Another first for me, is creating a 2D array from scratch, never done that before, that I can recall ... never had a need, and even for 1 dimension arrays, for a long time now, I have just used _StringSplit to create them. So I needed a bit of a refresher course, which my good buddy @TheDcoder assisted me with ... not without some angst I might add. LOL SimpleSQL_UDF.zip (12 downloads previously) (I have now completed all the functions I intended to. My next update will be a big improvement, bringing things more inline with my latest INItoSQL DB program changes.) Program requires the sqlite3.dll, not included, but easily enough obtained. Hopefully the usage is self-evident ... just change the Job number variable in the UDF_Test.au3 file to check the existing functions out. Enjoy! P.S. This is also related to a new program I have just finished and uploaded - INItoSQL DB
  10. Hello guys! I am having some difficulty in achieving a very simple task here. I have gone through the forums and tried some examples and UDFs but I can't seem to work it out. I would really appreciate if someone could help me out. Problem: Currently, I am logging the required feedback from the script into a log file in a simple way ... get the info in the var >> write it in the file But now I am in need to perform some analysis and need some of the values to go into an MSSQL table in the attached format Also, I need to be able to use Integrated Security" or "Trusted_Connection set as true or use the logged in windows credentials to connect to the server/database any help will be much appreciated!!! Thanks!
  11. Hi all, I hope you can help me with this. I need to connect to a Database using Cloudera ODBC Driver for Impala. I installed the driver, created a User DSN (drona2) and tested it successfully (got the message: Successfully connected to data source!) but when I try to connect to the database using a Conneciton String, it simply didn't work. I tried connecting to the database using the User DSN, I previously created, that has all parameters needed but got the following error: $ProviderDatasource = 'DSN=drona2;' $conn_Database = ObjCreate ("ADODB.Connection") $conn_Database.ConnectionString = $ProviderDatasource $conn_Database.Open ==> The requested action with this object has failed.: $conn_Database.Open $conn_Database.Open^ ERROR Also I tried adding all parameters to my connection string as follows but also got the same error: $ProviderDatasource = 'Driver=Cloudera ODBC Driver for Impala;Host=MyHost.domain.com;PORT=21050;AuthMech=3;UID=MyUserId@domain.com;PWD=MyPasswordc;Schema=default;SSL=1;AllowSelfSignedCerts=1;AllowHostNameCNMismatch=1;CheckCertRevocation=1;KrbFQDN=_HOTS;KrbServiceName=impala;ServicePrincipalCanonicalization=1' I hope someone has used this driver successfully before and can shed some light here. If you need additional information, let me know. Regards,
  12. 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
  13. 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
  14. 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=
  15. 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
  16. 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.
  17. 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
  18. 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
  19. 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.
  20. 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!
  21. 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?
  22. 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>
  23. 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
  24. 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!
×
×
  • Create New...