ADO.au3 UDF - BETA - Support Topic

[jchd]

Do you experience the same result with a distinct DB management tool?

[FMS]

no then i get all of the 9 colums :S
that's the strange part of it
Don't you think that the problem is the code I'm using or do u think that's ok?

[jchd]

You'd better discuss this point with mLipok, as I don't use MySQL nor his ADO UDF.

[mLipok]

This is interesting problem.
@FMS please PM me I have some "secret" question (of coruse we back with sumary to this topic).
Currently I have 13:30 on my clock, will you be on line about 22:00 ?
 

[FMS]

I din't read you message, but I'm here now

and Will be for à while.

what was Your question @mLipok ?

Edited August 27, 2016 by FMS

[mLipok]

On 19.08.2016 at 2:37 PM, FMS said:

Hello,

I got some strange issue's whit the script below and hope somebody can help me whit it.
In this query i ask 5 thing from the DB ( id,city,phone,address and zipcode) and it returns only the first and the last ,in this case id and zipcode.
When i do this query directly on de DB I got all the info i request.
And when i change the query like city,phone,id,zipcode,address I also get the first and last???
If I query only 1 thing i get that thing( if i ask only city i get the city name) , so the query works and all the things a ask the DB are filled in.

That could only mean 1 thing that is this peace of code below.
Does somebody see what's wrong below?
 

Local $sDriver = 'SQL Server'
         Local $query = "SELECT id , City , Phone , Address , ZipCode FROM homes WHERE id = '1'"
         
         Local $sDatabase = 'DBname'
         Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $GL_base_server_ip & ';DATABASE=' & $sDatabase & ';UID=' & $uid & ';PWD=' & $pass & ';'
         Local $oConnection = _ADO_Connection_Create()
         _ADO_Connection_OpenConString($oConnection, $sConnectionString)

         If @error Then
            Return SetError(@error, @extended, $ADO_RET_FAILURE) 
         Else
            Local $aRecordset = _ADO_Execute($oConnection,$query, False)
            Local $aRecordsetArray = _ADO_Recordset_ToArray($aRecordset, False)
            Local $aresult = _ADO_RecordsetArray_GetContent($aRecordsetArray)
            Local $iColumnsCount = UBound($aresult,$UBOUND_COLUMNS)
            Local $iRowsCount = UBound($aresult,$UBOUND_ROWS)
            _ADO_Connection_Close($oConnection)
            $oConnection = Null

            If $iRowsCount = 0 Then
               msg( "Error", "DB says no! (or there are no results)")
            ElseIf $iRowsCount = 1 Then
               msg( "Error", "found something : rowcount = " & $iRowsCount)
               _ArrayDisplay($aresult,"testje",Default,8)
            Else
                msg( "Error", "found something : rowcount = " & $iRowsCount)
               _ArrayDisplay($aresult,"testje",Default,8)
            EndIf
         EndIf

thanks in advanced.

 

I do not have your DB but I test it with default Examples DB just like this:
 

Func _Example_MySQL()
    ; Link to Windows MySQL ODBC drivers
    ; https://dev.mysql.com/downloads/connector/odbc/

    Local $sDriver = 'MySQL ODBC 5.3 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'localhost' ; change this string to YourServerLocation
    Local $sDatabase = 'world' ; change this string to YourDatabaseName
    Local $sPort = '3306' ; change this string to If your Server use non standard PORT
    Local $sUser = 'AutoIt' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    ; Local $sConnectionString = 'Driver={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';User=' & $sUser & ';Passwd=' & $sPassword & ';'
    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

;~  _Example_1_RecordsetToConsole($sConnectionString, "SELECT * FROM city")
;~  _Example_2_RecordsetDisplay($sConnectionString, "SELECT * FROM country WHERE `region` LIKE '%Europe%'")
    _Example_2_RecordsetDisplay($sConnectionString, "SELECT Name , CountryCode , District , Population FROM city WHERE name='Cary'")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MySQL

 

And it works for me OK.
Which Driver you are using ?

 

[FMS]

Hello,

I use the SQL server not MySQL.

Local $sDriver = 'SQL Server'

 

[mLipok]

On 31.08.2016 at 5:01 PM, FMS said:

Hello,

I use the SQL server not MySQL.

Local $sDriver = 'SQL Server'

 

Try to use SQL Native Client.

Also try to use:
 

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

 

[Skysnake]

I know this is not exactly the kind of response you expect, but do you have a database mangement tool?  What does your query do in the manager?  Can you copy & paste that query into AutoIt?

http://www.databasejournal.com/features/mysql/slideshows/top-10-mysql-gui-tools.html

If it is MS SQL Server, you should have Studio?

Also, change insert this line

Local $aRecordset = _ADO_Execute($oConnection,$query, False)
            ConsoleWrite("$query " &query& @CRLF)  ; <<<<<<<<<<<<<<<<<< add this to your script

Now you can see EXACTLY what AutoIt has executed.  Are there strange characters  in your Console output?  Copy that output query and run it in your manager.  Same result?

Edited September 14, 2016 by Skysnake

[TurionAltec]

I'm completely new to SQL programming. It seems most examples have people reading data from a Database. I'm looking to write a new record into an MSSQL Table.

Merging the ADO UDF (which seems to be the most active MSSQL one), with the example in the built-in SQLite database (since I'm new to building queries), I came up with this, which is completely untested since I don't have access to MSSQL right now. Am I on the right path?

#include "ADO.au3"
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

;Query cribbed from _SQLite_Query example
$sQUERY = "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');"

Example_1($sQUERY)



Func Example_1($sQUERY)
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'YourUserName'
    Local $sPassword = 'YourPassword'
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'


    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
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>Example_1

Second, since I'm going to be passing text data along, is there any characters I need to avoid in my strings other than" ' "? And is there any easy way in AutoIT to strip characters that aren't standard printable characters Chr(32)-Chr(126)? The formation of the Query will probably look closer to

$sA = "b"
$sB = "3"
$sC = " "
$sQUERY = "INSERT INTO aTest(a,b,c) VALUES ('" & $sA & "','" & $sB & "','" & $sC & "');"

Third: My goal in the end is for users to access the SQL Data in Excel. We have other Applications where the workbook is linked to SQL, the user enters a start time, and stop time, then refreshes the SQL data. For this I assume I need to store my date as type datetime2. When I do that, can I just pass along VALUES ('2016-09-24 12:30:55') or is it VALUES ('2016-09-24T12:30:55')?

[jchd]

1 hour ago, TurionAltec said:

$sA = "b" $sB = "3" $sC = " " $sQUERY = "INSERT INTO aTest(a,b,c) VALUES ('" & $sA & "','" & $sB & "','" & $sC & "');"

That will work with the example values shown, but look what happens when, say, $sA = "O'Connor".

Once stringified together, the query look like this: INSERT INTO aTest(a,b,c) VALUES ('O'Connor', '3', ' ');
You see that the single quote in $sA will cause a SQL syntax error. Worst, if some malvolent user manages to enter something like "zzz', '3', '456'); drop table aTest;" literally, the SQL engine will be happy to insert the dummy row, then proceed to execute the next statement, removing the table altogether with no way back.

This is called an "SQL injection attack" and hopefully it's pretty easy to guard against, by passing every string parameter in an SQL statement thru a simple function:

Func _SQLSanitize($s)
    Return(StringReplace($s, "'", "''"))
EndFunc

SQL mandates that single quotes in user data be doubled to remain data.

So your real-world statement would rather be:

$sQUERY = "INSERT INTO aTest(a,b,c) VALUES ('" & _SQLSanitize($sA) & "','" & _SQLSanitize($sB) & "','" & _SQLSanitize($sC) & "');"

But you can streamline things a little bit, by enclosing the parameter in single quotes directly in the sanitize function, which simplifies the building of the SQL statement, like this:

...
$sQUERY = "INSERT INTO aTest(a,b,c) VALUES (" & _SQLstr($sA) & "," & _SQLstr($sB) & "," & _SQLstr($sC) & ");"
...

Func _SQLstr($s)
    Return("'" & StringReplace($s, "'", "''") & "'")
EndFunc

Also beware that for SQL engines, the value '3' is a string while the value 3 is an integer (i.e. something completely distinct). Depending on the engine and the definition of the column, this can make a big difference.

Edited September 24, 2016 by jchd

[mLipok]

1 hour ago, TurionAltec said:

Am I on the right path?

Yes.

...

You should try and see what you get, in case of error you should check information which you should get from COM Error handler description.

 

[Skysnake]

@jchd - I understand the risk of " ' " queries.

Should " ; " be treated as a threat? Simply strip from queries? 

Eg 

select * from mytab
where text = ''; drop table fubar;--');

where user could enter the portion " '; drop table fubar;-- "

Where stripping the " ; " character reduces this risk.

[jchd]

No need, as once your input is sanitized (doubling single quotes that are part of data string), the ; is just another character inside the string and can't be interpreted otherwise.

[TurionAltec]

Thanks @jchd and @mLipok

Once I got my database setup, the query worked as desired and wrote the new line to the table.

Is ' the only character that I need to worry about sanitizing? What about non-printable ASCII? I'm also looking to strip values above ASCII 127. Currently my scripting is writing to a CSV file, and the data it's populating shouldn't be such characters (as they are read from a data stream, and not a user entry), I have seen such invalid characters come across before. My script writes them to a CSV file fine, but excel starts barfing when it hits those characters. Does this look right?

$sOutput=StringRegExpReplace($sInput,"[^\x20-\x7E]", "")

 

Also I noticed if one of my columns were type DateTime or int (for example), and I try to send a string, it will reject the whole row. Is there any way to have the database replace invalid entries with just the default value?

[jchd]

The single quote is normally the only character to sanitize, but some engines allows literal strings to be within double quotes in some syntax cases. You can ignore that if the engine you use doesn't allow "Hello World!" as a literal string.

Regarding your input, are you sure you don't read UTF8 input? If you do, then ignoring characters with codepoint > 0x7E will delete normally valid UTF8 characters. If you insist on input being strictly 7 bit then the regexp will do.

About dates, the question is both engine- and schema-dependant. There is no standardized way to implement dates and every vendor is free to offer a number of possibilities like JulianDay, JulianDate, Un*xEpoch, ISO date (string in various formats), etc. You should select the best format suited to your use case, but remember that the usual DD-MM-YY[YY] or MM-DD-YY[YY] and friends are the worst choices because they don't collate naturally. Use the suitable format at display time, but store in DB according to computer/programmatic convenience.

To force the required type without extra code at the app level, you can use BEFORE INSERT triggers to convert data into the correct type. You can also use CHECK constraints to verify at DB insert/update time that your data isn't garbage. Remember, you can have dozens of distinct programs interfacing to the DB, so you'd have to duplicate validation code consistantly among them. But if you push a significant part of the job to the DB engine, you only have to do it once in the DB schema, (but at the price of a small slowdown when inserting or updating).

Don't hesitate as well to format the output of your SELECTs the way you find it the more comfortable in your app. For instance if you store dates in JulianDate and just whish to display it (without further processing for instance) in your local human format, say MM-DD-YY, you can use the power and speed of SQL to massage the dates into what you want using functions offered by the engine to manipulate dates formats.

To illustrate, here's the definition of a view that I'm using to report to myself some weekly stat about one of my activities,

Spoiler

with
    wk as (
       select '01' as ww union all select printf('%02i', cast(ww as integer) + 1) from wk where ww < '53'          
    ),
    k14 as (
        select printf('%02i', (strftime('%W', substr(depart, 1, 10)) + 1)) ww,
               count(*) cnt
        from cartes
        where depart like '%2014%' and
              not abandon and
              not diy and
              depart like '____-__-__%'
        group by ww
    ),
    k15 as (
        select printf('%02i', (strftime('%W', substr(depart, 1, 10)) + 1)) ww,
               count(*) cnt
        from cartes
        where depart like '%2015%' and
              not abandon and
              not diy and
              depart like '____-__-__%'              
        group by ww
    ),    
    k16a as (    
        select printf('%02i', (strftime('%W', substr(depart, 1, 10)) + 1)) ww,
               count(*) cnt
        from cartes
        where depart like '%2016%' and
              not abandon and
              not composants and
              not diy and
              depart like '____-__-__%'
        group by ww
    ),
    k16b as (
        select printf('%02i', (strftime('%W', substr(depart, 1, 10)) + 1)) ww,
               count(*) cnt               
         from repartners
         where depart like '%2016%' and
               prix is not null and               
               prix > 0 and
               depart like '____-__-__%'
        group by ww
    ),
    k16 as (
        select ww,
               coalesce(a.cnt, 0) + coalesce(b.cnt, 0) cnt               
        from
            k16a a
              left outer join
            k16b b using (ww)
    ),
    weeks as (
          select
                case ww when printf('%02i', (strftime('%W', Date('now')) + 1)) then ww || ' ***' else ww end Semaine,
                printf('%4i', coalesce(k14.cnt, 0)) " 2014",
                printf('%4i', coalesce(k15.cnt, 0)) " 2015",
                printf('%4i', coalesce(k16.cnt, 0)) " 2016",
                printf('%4i', coalesce((select sum(cnt) from K14 where k14.ww <= wk.ww), 0)) "Σ 2014",
                printf('%4i', coalesce((select sum(cnt) from K15 where k15.ww <= wk.ww), 0)) "Σ 2015",
                printf('%4i', coalesce((select sum(cnt) from K16 where k16.ww <= wk.ww), 0)) "Σ 2016"       
          from
              wk
                left outer join
              k14 using (ww)
                left outer join
              k15 using (ww)
                left outer join
              k16 using (ww)     
    )
    select
          Semaine,
          " 2014",
          " 2015",
          " 2016",
          "Σ 2014",
          printf('% 6.1f', 100 * (cast(trim("Σ 2015") as real) - cast(trim("Σ 2014") as real)) / cast(trim("Σ 2014") as real)) "2014 ◢ 2015",
          "Σ 2015",
          printf('% 6.1f', 100 * (cast(trim("Σ 2016") as real) - cast(trim("Σ 2015") as real)) / cast(trim("Σ 2015") as real)) "2015 ◢ 2016",
          "Σ 2016"
from weeks

and the output it generates (whatever that means):

https://dl.dropboxusercontent.com/u/26433628/Weekly stats 2014..2016.html

You can see that I heavily use SQL functions to built a complete report without a single applicative line of code.

Edited September 27, 2016 by jchd

[TurionAltec]

Thanks again.

I'm very new to SQL so at the moment I'm just trying to keep my application as simple as possible. Currently I have AutoIT logging into CSV files. One file per day, thousands of rows per file. My data is already piling up after a few months. My vision is to have AutoIT insert new rows into a table, then users will be able to "search" within Excel (connected to the database). As a bonus I won't have to worry about writing to files that might be in use by the user. I will handle bells and whistles in the future.

The data is coming from ASCII strings being sent from a legacy system, so I shouldn't see anything higher than 7 bit ASCII. But I have in the past, and in those cases it was corrupt garbage being sent across, which seemed to confuse Excel when importing my CSV.

On the date format, The column is configured as datetime2, and I'm parsing the raw data into ISO formatted date "YYYY-MM-DD HH:MM:SS", which seems to work when I insert as such, and they will display in Excel as desired in formatting settings. However if corrupt data is sent, and I read a date as "2016-09-Ae 13:45:32" the entire row does not get inserted. Is there any way to have SQL "ignore" that one wrong cell, and instead insert either null, or the default value for that column?

As far as the engine I'm using MSSQL, and the schema... I'm still trying to understand what they are.

Edited September 28, 2016 by TurionAltec

[mLipok]

33 minutes ago, TurionAltec said:

and the schema... I'm still trying to understand what they are

construction shema - this is a way of showing how Database/Table is created - this is just SQL statement which will create exactly the same structure on different SQL server or instances.

 

[jchd]

Hi,

Sorry I ddin't read up prior posts well enough, where you specified which engine and date-time format you were using.

I have close to zero knowledge of MSSQL by itself, but if I had to check for datetime validity, I'd do something like his using my pet SQL engine (SQLite):

create trigger CheckDateTime
before insert on mytable
for each row
when
  length(new.`datetimecolumn`) != 19
  or
  datetime(new.`datetimecolumn`) != new.`datetimecolumn`
begin
  update mytable
  set new.`datetimecolumn` = NULL   -- or put there your default literal value or expression
  where idcolumn = new.idcolumn;
end

This triggers act before the row is actually inserted and gives you the opportunity to change its content. In the above example, I check that the date is exactly 19 character long and the conversion form this input datetime ISO string to a datetime ISO string gives the input back, meaning that the date is valid. If not, I change this row date column to NULL (or as commented, to some default value you think is more convenient).

You'll probably have to adapt this to bizarre MSSQL syntax but the idea will remain the same.

EDIT: thanks @mLipok for adding what a schema is, I was just about editing my own post to add something about that.

Edited September 28, 2016 by jchd

[mLipok]

@jchd 

I've never been interested yet, in how triggers work but your example looks interesting.