Jump to content
mLipok

ADO.au3 UDF - BETA - Support Topic

Recommended Posts

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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?


as finishing touch god created the dutch

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 ?
 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

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

and Will be for à while.

what was Your question @mLipok ?

Edited by FMS

as finishing touch god created the dutch

Share this post


Link to post
Share on other sites
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 ?

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

Hello,

I use the SQL server not MySQL.

Local $sDriver = 'SQL Server'

 


as finishing touch god created the dutch

Share this post


Link to post
Share on other sites
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)

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

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 by Skysnake

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

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')?

Share this post


Link to post
Share on other sites
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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

@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.


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 by TurionAltec

Share this post


Link to post
Share on other sites
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.

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

@jchd 

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


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2019-10-01

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($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, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By buymeapc
      Hello,
      I was looking for a way to use the CLR functions to check a SQL 2016 database state, but I've been unable to find an example that I can get to work. Any help would be appreciated. Here's what I have, which always errors when I try to set the connection string and I'm not sure why. Thanks!
      #include ".\Includes\CLR.au3" #include ".\Includes\CLR Constants.au3" JustATest() Func JustATest() Local $oAssembly = _CLR_LoadLibrary("System.Data") ConsoleWrite("$oAssembly: " & IsObj($oAssembly) & @CRLF) Local $oSQLConn = _CLR_CreateObject($oAssembly, "System.Data.SqlClient.SqlConnection") ConsoleWrite("$oSQLConn: " & IsObj($oSQLConn) & @CRLF) $oSQLConn.ConnectionString = "Server=serverip;Database=dbname;UID=user;PWD=aaaa;" $oSQLConn.Open EndFunc Edit: I am using ptrex's CLR UDF from here:
    • By nooneclose
      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. 
×
×
  • Create New...