Is there an SQL pack to be able to interact with SQL

[CaptainBeardsEyesBeard]

It'd be great to pull data from SQL then input it into our websites for testing for example. 

 

Is there a way to do it? 

[Earthshine]

did you search the forum? for better answers, try to ask better questions.

being vague doesn't help and my Stupomitron helmet isn't working.....

What SQL source are you talking about? SQL Server? Oracle? MySQL?

search the forum for SQL UDF

Edited August 1, 2019 by Earthshine

[CaptainBeardsEyesBeard]

Yeah I've had a bit of a look 

 

I've seen a reference to a #include <_sql.au3> file that my autoit doesn't recognise 

 

Managed to add these 2 OK

#include <SQLite.au3>
#include <SQLite.dll.au3>

Essentially I'd like to do a SQL query, return some values then attempt to search for them on the website to test they're appearing. 

[Earthshine]

there are several udfs, ADO udf would probably work for you. what database type? SQL Server? MySQL? what? 

Edited August 1, 2019 by Earthshine

[CaptainBeardsEyesBeard]

SQL Server

[Earthshine]

the ADO udf published on this site should work fine

[CaptainBeardsEyesBeard]

Thanks I found that. 

Where would I find the ADODB name which is the $oConnection in the function on my sql server? 

_ADO_Connection_OpenMSSQL(ByRef $oConnection, $sServer, $sDBName, $sUserName, $sPassword, $sAppName = Default, $sWSID = Default, $bSQLAuth = True, $bUseProviderInsteadDriver = True)

 

[Earthshine]

Google is your friend. this is how you would form a connection string

https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/create-an-ado-connection-string

 

example

$m_sConnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" "Initial Catalog='Northwind';Integrated Security='SSPI';"

 

the ADO provider is SQLOLEDB

Edited August 1, 2019 by Earthshine

[CaptainBeardsEyesBeard]

That's great I think I got it to work 

 

So the first argument should look like this?

 

_ADO_Connection_OpenMSSQL("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True", 

[Earthshine]

I can't test it to make sure. Does it work? can you do a Select * on a small table and get results?

[CaptainBeardsEyesBeard]

This executes without error. I take it I would have to return the results into an array then output the array to a text file or something ?

 

_ADO_Connection_OpenMSSQL("Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=True", "SERVER", "DATABASE", "USER", "PASSWORD")
_ADO_Execute("Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=True", "select * from timecard")

[Earthshine]

i can't really help without being able to test, and I don't want to install sql server. there are other guru's here who can help you with this... I would assume results come back in an array but I have not looked at the UDF code. Look at the UDF function you are using to determine the return type.

[CaptainBeardsEyesBeard]

Hey guys, 

any help on how to execute the _ADO_Execute function? 

I'd like to do a select statement. What's the most sensible way to do this? 

 

I've been looking at the wiki and took this function 

 

expandcollapse popup

Func SQLTest()


Global $sDSN = 'Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=True'
Global $sDatabase = 'DataBase'
Global $sServer = SERVER
Global $sPort = '1433'
Global $sUser = 'user
Global $sPassword = 'password'


    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $sTableName = 'timecard'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; CleanUp
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc

Then in the Main file I just run this function 

SQLTest()

It then produces this error

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\EliteAnvilAutomation\Framework\TestEnv.au3"    
"C:\EliteAnvilAutomation\Framework\SQLFunctions\ADO.au3" (769) : ==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR
>Exit code: 1    Time: 1.118

Which points to this line 

    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
 

Is my connection string wrong?

Edited August 2, 2019 by CaptainBeardsEyesBeard

[mLipok]

Please use recent ADO.au3 UDF Verision ... was recently updated.