Jump to content

Search the Community

Showing results for tags 'SQL'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • 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


  • 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


  • Forum
  • AutoIt


  • Community Calendar

Found 49 results

  1. I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything. Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc
  2. json array in sql search

    ..let's say I store in a mysql column "[1,2,11,12,123]". How can I make s query to get all those rows that is 1, or is 12, etc ? I'm using mysqlnd 5.0.11-dev - 20120503 Any advise on what technique I should use is welcomed Thanks PS: ..I'm adding a comma to each end, as in [,1,2,11,12,123,] and will search with like %,12,% . Not pretty but I'm in a hurry and can't come up with a better solution =/
  3. Dear all, I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away. Current formula is: Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC; testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why. Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC; Error: near "(": syntax error: Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/ Thanks a lot for your help. EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr (substr(a.title, 1, instr(a.title, ' - '))
  4. I have code that does a WMI SQL query to find all defined printers, and I want to parse the returned object in several places. However, after parsing it the first time, all other times fail to find any printer objects. Here is my test code: test() Func test() Local $oPrinters, $oPrinter, $err, $cnt, $oP, $query $query = "SELECT * FROM Win32_Printer" $oPrinters = doQuery($query) $err = @error LogMsg("+++: $err = " & $err & ", isObj($oPrinters) = " & IsObj($oPrinters)) If ($err == 0) Then LogMsg("FIRST LOOP") ; <=== FIRST LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") LogMsg("SECOND LOOP") ; <== SECOND LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") EndIf EndFunc ;==>test Func doQuery($sQuery, $lnum = @ScriptLineNumber) #forceref $lnum LogMsg("+++:" & $lnum & ": doQuery(" & '"' & $sQuery & '"' & ") entered") Local $oWMIService, $oResults, $errstr Local $wbemFlags = BitOR(0x20, 0x10) ; $wbemFlagReturnImmediately and wbemFlagForwardOnly $oWMIService = ObjGet("winmgmts:\\" & "localhost" & "\root\CIMV2") If (IsObj($oWMIService)) Then $oResults = $oWMIService.ExecQuery($sQuery, "WQL", $wbemFlags) If (IsObj($oResults)) Then LogMsg("+++: doQuery() returns @error = 0, Good: returning the object") Return (SetError(0, 0, $oResults)) ;;; Good: return the object Else $errstr = "" _ & "WMI Query failed." & @CRLF _ & "This is the query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") LogMsg("+++: doQuery() returns @error = 1") Return (SetError(1, 0, $errstr)) ; Error: Query faled EndIf Else $errstr = "" _ & "WMI Output" & @CRLF _ & "No WMI Objects Found for class: " & @CRLF _ & "Win32_PrinterDriver" & @CRLF _ & "using this query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") MsgBox(0, "ERROR", $errstr) ; Error: Cannot get $oWMIService object Exit (1) EndIf EndFunc ;==>doQuery Func LogMsg($msg, $lnum = @ScriptLineNumber) ConsoleWrite("+++:" & $lnum & ": " & $msg & @CRLF) EndFunc ;==>LogMsg Parsing the returned $oPrinters object shows 5 printers: +++:15: FIRST LOOP +++:18: +++: isObj($oP) = 1 +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft XPS Document Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft Office Document Image Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Fax<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG7100 series Printer WS<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG6100 series Printer WS<== +++:24: +++: Found 5 printers Parsing it again, shows no printers: +++:26: SECOND LOOP +++:29: +++: isObj($oP) = 1 +++:35: +++: Found 0 printers
  5. Hello guys ! I'm stuck in something stupid (I guess), but since I've no clue on how to solve it, here I come I'm trying to collect the result of the query "select @@hostname" on MSSQL Server, but it doesn't work ... and I'm sure I'm connected to the database (I've successfully seen the database treeview). #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_UseX64=n #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include<_sql.au3> #include<array.au3> Local $aData,$iRows,$iColumns ;Server ID and credentials for tCards Global $ServerAddressT = "" Global $ServerUserNameT = "user" Global $ServerPasswordT = "password" Global $DatabaseNameT = "DataBASE" ;Connect to DB _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error $OADODB = _SQL_Startup() If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) If _sql_Connect(-1, $ServerAddressT, $DatabaseNameT, $ServerUserNameT, $ServerPasswordT) = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg()) _SQL_Close() Exit Else If _SQL_Execute(-1,"select @@SERVERNAME") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg()) Else _ArrayDisplay(_SQL_GetTable(-1,"select @@SERVERNAME",$aData,$iRows,$iColumns)) ;~ $toto = _SQL_Execute(-1,"select @@SERVERNAME") ;~ _ArrayDisplay($toto) EndIf EndIf
  6. I have an Autoit-based client GUI that uses a single MS SQL Server database. I have no problem connecting to and executing queries against this database from my AutoIT code, with the below exception: One of the database's stored procedures I need to execute requires a parameter that is a table. I've set up the appropriate table_type on the database and everything works fine on the database itself when I execute the stored proc with a table variable for the parameter (no AutoIT involved). The problem is that I can't figure out how to pass a table parameter from AutoIT, assuming it's even possible. The code below shows the two methods I've tried, both methods return a RecordSet object that is at EOF. #include <Array.au3> Opt("MustDeclareVars", 0) $sDBSrv = "ITSQL01.domain.com" $sDBName = "INVDEV" $sADOName = "ADODB.Connection" $oSQLConn = ObjCreate($sADOName) $sConnStr = "Driver={SQL Server};Server=" & $sDBSrv & ";Database=" & $sDBName & ";Trusted_Connection=yes;" $oSQLConn.Open ($sConnStr) $rsoFacFriendly = $oSQLConn.Execute("SELECT TOP 5 FriendlyName FROM Facility") If Not $rsoFacFriendly.EOF Then $aFacFriendly = $rsoFacFriendly.GetRows() _ArrayDisplay($aFacFriendly) $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $aFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec1", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec1", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec1", "At end of file") Case Else MsgBox(0, "sp exec1", "Something else happened") EndSelect $rsoSPResults = 0 $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $rsoFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec2", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec2", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec2", "At end of file") Case Else MsgBox(0, "sp exec2", "Something else happened") EndSelect $rsoSPResults = 0 $rsoFacFriendly = 0 $oSQLConn.Close $oSQLConn = 0 Exit
  7. romaSQL

    romaSQL This autoIt UDF is built on the concept of Laravel Query & doctrine. RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt. Most of the common SQL-queries are supported already and more are coming soon. All of your support is much appreciated. Connections For the connection the object ADODB is used. Therefore the connection string is based on ODBC. You can also use OLEDB connection strings or other database connections. In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection. I’d be very glad if you shared your modifications with me. Currently supported connections - MySQL (odbc) - Microsoft SQL Server (odbc) - SQLite (odbc) - Microsoft Access (odbc) Command reference $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy Examples establishing connection ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver') simple SQL query $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif Select $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif If you need more examples, then tell me exactly what you need. I hope you like my UDF and find some use for it. --- ->DONWLOAD romaSQL
  8. I hope my title is good enough. I'm using the ADO UDF and I have question regarding editing SQL records with this UDF. The owner of the UDF suggested an idea, but maybe there is another trix.
  9. Hi, I want to click a link by the element ID through IEGetObjById. <!DOCTYPE html> <html> <body> <button type="button" id="Random-1-ID" onclick="alert('Hello world!')"></button> </body> </html> I intend to click the button with ID"Random-1-ID". But on every refresh the ID changes to next number like "Random-2-ID" "Random-3-ID" The code i which i wrote for this function is #include <IE.au3> #include <MsgBoxConstants.au3> Local $oIE = _IECreate("I:\Documents\1. Work\Automation\My codes\Collections\11. Clicking button by Value and ID\button.html") Local $oDiv = _IEGetObjById($oIE, "Random-1-ID") _IEAction($oDiv, "click") _IELoadWait($oIE) So can anyone help me to alter this code like it clicks for every ID in format "Random-%-ID"
  10. DB1: CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO DB2: CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [ItemName] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO Compare and merge database. alter table [Item] add [ItemName] bigint not null default(0) alter table [Item] add [MoneyType] bigint not null default(0) alter table [Money] add [MoneyType] bigint not null default(0) Please help automate code AutoIt to generation new code for large sql file. Thanks
  11. Hi guys, This post was originally going to be a question on how to fix this issue but as I ended up figuring it out I thought I'd post it here for others that have the same issue. So you've downloaded and extracted the latest version of the SQLite dll files etc into the same directory as your SQLite script but it's failing at _SQLite_Startup()? What you need to do, that I couldn't see anywhere in the documentation, to fix the issue is rename the dll files from (for example) "sqlite3_301500000.dll" to "sqlite3.dll" and "sqlite3_x64_301500000.dll" to "sqlite3_x64.dll". Fixed my issues instantly! Hope it can help others too. Cheers.
  12. Hello! I'm fairly new to using Autoit, I like the language and simplicity, however, there is a bit of a learning curve for me. I'm stuck and need some community help! I need to manipulate a query by using GUICtrlCreateDate to select the correct date and pipe the selected date into my actual query in a specific format (yyyymmdd). Here is an example: _Flag_RecordsetDisplay($sConnectionString, "select * from trips_to_complete_20161122 where trip_type in ('P','C') and trip_status in ('S','PC','DC') and Flagged = 1") Func _Flag_RecordsetDisplay($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Recordset content') EndFunc ;==>_Flag_RecordsetDisplay The part of the query that needs modified is "trips_to_complete_20161122" I need to be able to select a date (via the gui) and that selection pipe into my query. Thanks in Advanced!
  13. Good morning all, I have a question regarding a situation im in... I have an array with information from sql query, how can i send this information to a listview ? $citeste_daune = "SELECT `id_dauna`,`data_incident`,`sala`,`autor`,`suma` FROM `daune` WHERE `status`=1;" $sa_citit_daune = _query($sqlinstance, $citeste_daune) Global $aresult[10001][5] = [[10000, 5]] Global $iindex = 0 With $sa_citit_daune While NOT .eof $aresult[$iindex][0] = .fields("id_dauna").value $aresult[$iindex][1] = .fields("data_incident").value $aresult[$iindex][2] = .fields("sala").value $aresult[$iindex][3] = .fields("autor").value $aresult[$iindex][4] = .fields("suma").value $iindex = $iindex + 1 .movenext WEnd EndWith ReDim $aresult[$iindex][5] $aresult[0][0] = $iindex - 1 _ArrayDisplay($aresult) GUICtrlCreateListViewItem(_ArrayToString($aresult), $lista_daune_active) This is what i see when i execure _ArrayDisplay($aresult)
  14. 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
  15. Hello guys, maybe some of you can help me. I manage to connect with sql database and use a variable to search for different things when i do the select from database. My problem is that i'm reading the database with an MsgBox to see the values. But sometimes for the thing i'm filtering the dabase for it shows me different values and i only one to select one of it and move forward with my script. Is there any chance to read the database...view it...and in front of each value to have a thick box to select with value i want for the line. After i select the the line value i want to stock the values from each column in different variables and then i want to send them as values to write in another database. Is this possible? Hope any of you can help me in this matter Apreciate guys.
  16. Referring to a question posted here, this is a possible SQL query that should do: supposed you have an sqlite table named 'multimedia' with a column named 'sha1_hash', and you want to see only rows with the 'sha1_hash' value that appears more than one time within the table, you could use an SQL query like this: SELECT sha1_hash, COUNT(sha1_hash) AS sha1_sum FROM multimedia GROUP BY sha1_hash HAVING sha1_sum>1 ORDER BY sha1_sum DESC; this query should show only records with the value of sha1_hash that appears more times also in other records, showing it only one time, with a column indicating how many occurrences are present, ordered from the most recurring to the least recurring one. I don't know if this query can be simplified a bit...?, .... if there is a simpler way I would like some hint, Thanks
  17. Best way to store Dates in SQLite?

    Hi I ask for generic advice on what field format is best to use to store dates in a database (SQLite). if is a better choice to stored separately year, month and day into 3 separate fields or use one field for a complete date-time group? I have to store information of this kind: a person is assigned to a job for a certain day (one day only) or also a person is assigned to a job from a day to another day (a range of days) then i have to query the database for a certain day so to know who is working to a certain job in a specific day. Well, in the case of ranges of days (for example Mr. Bean works to JOB1 from May/15 to May/20) I have to store one record for each day of the range (6 record in this case) or use only one record with both dates in 2 fields of the same record? the SQL query should 'ask' who is working on a specific day (even for days in the middle of the ranges). My doubt is, what's the best way to store ranges of dates (as in the above example), so to be facilitated in the retrieval of those informations. I'm afraid I'm not been clear, even if I did my best to be, anyway .... any suggestion is welcome.. Thank You
  18. PostgreSQL Video tutorials

    Currently I'm working on MS SQL >> PostgreSQL migration. Here are some of interesting Video tutorials about PostgreSQL
  19. Hi guys, You can run a sql query in a script of autoit ? I would like to see the result in an array table .. Thank's
  20. select top 0 * into #tmpdatabase from ABTABLE; select * from #tmpdatabase; BULK INSERT #tmpdatabase FROM $fileupload WITH (FIELDTERMINATOR =';',ROWTERMINATOR ='\n' ,FIRSTROW = 2); MERGE ABTABLE AS T USING #tmpdatabase AS S ON (T.A = S.A) WHEN NOT MATCHED BY TARGET THEN INSERT( [A] ,[K] ) VALUES( S.[A] ,S.[K] ) WHEN MATCHED THEN UPDATE SET T.[A] = S.[A] ,T.[K] = S.[K]; drop table #tmpdatabase; Hi, I'm trying to convert this query that I make from Toad that loads a file into a tempdatabase from .csv file based on an existing table on the sql server into autoit friendly code but don't know how to do it, been rubbing my head on this one :P, maybe need a different approach? I'm using AutoIt Version: This doesn't seem to do anything: Local $obj_SQL_DB = _SQLConnect("db.u\SQl1", "data", 1, "At", "Pass") If @error Then Return SetError(50, @error, -1) Local $strSQL = "select top 0 * into #tmpdatabase from ABTABLE;" $obj_SQL_DB.Execute($strSQL) Any suggestions would be fantastic Nevermind i managed to do this like this instead :), mark as solved! Local $file = FileOpen("\TEST\SQL_UPD.txt",0) Local $strSQL = FileRead($file) $obj_SQL_DB.Execute($strSQL)
  21. I am trying to get remote autoit to work with SQL. I know that I can use SQLLite however I have a server that I have SQL setup on already that I have used. I am using a SQL include “see attached”. However I can’t get it to work. Here is a simple snippet of code that I am using. I have looked in both google and here but can’t get anything working. Was wondering if it is a problem with being on windows 10. #include <Array.au3> #include <SQL.au3> $IP = "" $User = "user" $Pass = "pass" $DB = "Login" $out = _SQLConnect($IP,$DB,1,$User,$Pass) _ArrayDisplay($out) MsgBox(0,"",$out) Here is the error I am getting when it runs. --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "C:\Program Files (x86)\AutoIt3\Include\SQL.au3" (29) : ==> The requested action with this object has failed.: If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";") If $fAuthMode Then $oConn^ ERROR ->22:36:28 AutoIt3.exe ended.rc:1 +>22:36:28 AutoIt3Wrapper Finished. >Exit code: 1 Time: 16.16 SQL.au3
  22. SQL Query Help

    Hopefully someone a little more solid in SQL can give me a hand. I have a many-to-many-to-many setup. a.field1 a.field2 a.field3 x.afield1 x.bfield1 b.field1 b.field2 b.field3 y.bfield1 y.cfield1 c.field1 c.field2 c.field3 In essence, I need all a.field1 where c.field3="stringvalue". I could do it with multiple queries, and looping, but I am hoping there is a simpler, single query I can make. Thanks in advance for any help.
  23. Hello, I've made a script where this is a piece of, this because it will be a lit easyer to read and use for others iff solved. I am willing to learn the use of SQL / autoit connection but can't find anythin helpfull on the internet Or iff I find something it is a little dated. The following script is also build whit dated material. What mine consernens are is SQL injection and all the other security isseu's. I hope somebody can look at mine script and help me whit making this work. - Is this a good way to make a connection and check it or is there a better way? At this point I've an error : "D:\map\file.au3" (72) : ==> The requested action with this object has failed.: $adCN.Open ($constrim) $adCN^ ERROR >Exit code: 1 Time: 18.51 #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> $Form1 = GUICreate("Form1", 480, 100, 190, 320) $button1 = GUICtrlCreateButton("check and run", 128, 24, 113, 33) $Input_ip = GUICtrlCreateInput("", 8, 16, 113, 21) $Input_usr = GUICtrlCreateInput("harry", 8, 40, 113, 21) $connection_label = GUICtrlCreateLabel("connection = ", 248, 16, 52, 17) $user_label = GUICtrlCreateLabel("username = ", 248, 40, 52, 17) $connection_status_label = GUICtrlCreateLabel("connection not checked", 304, 16, 190, 70) $user_status_label = GUICtrlCreateLabel("user not checked", 304, 40, 190, 17) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $button1 $checked = check_connection_usr(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr)) if $checked Then ;MsgBox($MB_SYSTEMMODAL, "function","true") get_username(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr)) Else MsgBox($MB_SYSTEMMODAL, "function","an error occured") EndIf EndSwitch WEnd Func check_connection_usr($input_ip,$input_usr) GUICtrlSetData($connection_status_label, "checking") GUICtrlSetData($user_status_label, "checking") if $input_usr = "" then GUICtrlSetData($user_status_label, "no username!") Return False Else GUICtrlSetData($user_status_label, $input_usr & " will be checked") if $input_ip = "" then GUICtrlSetData($connection_status_label, "no IP or adres!") Return False else $check_ping = Ping($input_ip, 250) if not $check_ping Then GUICtrlSetData($connection_status_label, "error in ping") Return False Else GUICtrlSetData($connection_status_label, "ping = " & $check_ping & "ms.") return True EndIf EndIf EndIf EndFunc Func get_username($xIP,$usr) Local $ServerAddress = $xIP Local $ServerUserName = "root" Local $ServerPassword = "" Local $DatabaseName = "" $constrim="DRIVER={SQL Server};SERVER=" & $xIP & ";DATABASE=" & $DatabaseName & ";uid=" & $ServerUserName & ";pwd=" & $ServerPassword & ";" $adCN = ObjCreate ("ADODB.Connection") $adCN.Open ($constrim) MsgBox(0,"",$constrim ) if @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit Else MsgBox(0, "Success!", "Connection to database successful!") EndIf $sQuery = "select * from users where username=" & $usr $result = $adCN.Execute($sQuery) MsgBox(0, "", $result.username( " = username" ).Value) ;---------------------------is this ok ? GUICtrlSetData($user_status_label, $result.username & " username present");---------------------------is this ok ? $adCN.Close ; ==> Close the database EndFunc
  24. I want to present BETA Version of my ADO.au3 UDF. This is modifed version of _sql.au3 UDF. For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH This is first public release , and still is as BETA DOWNLOAD LINK (in download section): Have fun, mLipok EDIT: 2016-06-03 Below some interesting topics about databases: EDIT 2016/07/04: For more info about ADO look here: https://www.autoitscript.com/wiki/ADO
  25. ADO.au3 UDF

    Version 2.1.15 BETA


    I want to present BETA Version of my ADO.au3 UDF. Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850 This UDF is modifed version of _sql.au3 UDF. For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH