Jump to content
mLipok

ADO.au3 UDF - BETA - Support Topic

Recommended Posts

On 2/1/2019 at 2:09 AM, faustf said:

the  func _DB_Sql_Execute ......   insert in ADO.au3 , i think is better 

The ADO UDF works very well as is. If you want this simplified approach, perhaps make a wrapper for the standard ADO function? 

 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

I've got record sets triggering the following COM Error:

ADO.au3 v.2.1.15 BETA (599) : ==> COM Error intercepted !
$oADO_Error.description is:     Rowset position cannot be restarted.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  599
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

This one was triggered on a call to _ADO_Recordset_ToString, with a result from a MySQL procedure call that returns a single row status result message, but also occurs on some simple queries as well.

I know exactly what the issue is, it is the $oRecordset.moveFirst() call.

I'd suggest that this:

If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else

be changed to:

If $oRecordset_Bookmark = Null Then
            If $oRecordset.Supports($ADO_adMovePrevious) Then $oRecordset.moveFirst()
        Else

This fixes the errors on my DSN's, but I've only tested a couple different drivers.

I changed all occurrences of .moveFirst in my copy of the udf (3 of them), to this format.


By far, the worst four letter word (swear word) out there has to be USER

Share this post


Link to post
Share on other sites

Your proposal looks reasonabled.
Thanks.
Will look in my current DEV version.

btw.
I hope also @jchd will add his 2 cents.


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication 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 *

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

11

Signature last update: 2018-10-31

Share this post


Link to post
Share on other sites

@xrxca 

Did you read other sites like this :
https://stackoverflow.com/questions/22356278/recordset-movefirst-rowset-position-cannot-be-restarted

specifically I want to ask you about this "Keith" answers:

Quote

Make sure you are not using a forward-only recordset. Recordsets are this way by default. Instead use a dynamic (adOpenDynamic) or static (adOpenStatic) cursor type.

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication 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 *

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

11

Signature last update: 2018-10-31

Share this post


Link to post
Share on other sites

Indeed not all ADO interface layers support all CursorTypes. Some support all inconditionally, some offer those the DB engine offers, some support only some (conditionally or not),  etc. The variable support for CursorLocation property makes things even muddier.

The same occurs with LockTypes. But all LockTypes can't be implemented only in the ADO wrapper: they can only reflect what the engine is capable of.

ADO specifies a fair number of recordset properties, methods and events.

Full cursor support can be counter-productive in that if the engine doesn't offer built-in efficient cursor support relying on the engine capabilities, the ADO has to implement it in its code, forcing the full recordset of a query to be stored in memory. Then it's too easy for inexperienced SQL programmer to forget that an inconspiscious (too broad) query may need yottabytes of RAM at the ADO level.


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/ That's a huge task and ADO is already pretty well agnostically documented, for instance: https://www.w3schools.com/asp/ado_intro.asp

2/ What do you have in mind? Extend ADO objects, methods, properties & events ou useable SQL functions?
The former implies adding to the ADO code, which isn't always public.
The latter requires adding user functions to the target SQL engine.


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

:)

Instead of trying to do everything for everybody, rather state the functionality of the ADO UDF clearly.  As in this example, there should be sufficient documentation to indicate that this is supported/not by the UDF.  It does not need a motivation.  A simple "this is the capability" statement.  It should be easy for the end-user to determine these UDF capabilities.

Whatever else the user requires, beyond the capabilities of the UDF, should then be the responsibility of that end-user, in such a way that the ADO UDF can be used as-is, with only additional functionality added to it.

So, yes the documentation part will be a challenge. I have in the past offered my assistance in this regard. I am still willing to contribute (a CHM for the ADO? :))  and the ADO UDF must not prevent the user from extending its capabilities.

Good? 😎

Skysnake


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
Posted (edited)

Will answer later.

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication 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 *

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

11

Signature last update: 2018-10-31

Share this post


Link to post
Share on other sites
Posted (edited)
4 hours ago, Skysnake said:

Instead of trying to do everything for everybody, rather state the functionality of the ADO UDF clearly.

WARNING: I'm going to simplify things in the text wall below. I won't discuss the actual mess between ADO, ODBC, OLE DB and I'll group all this under the term ADO.

NOTE: ADO isn't always accessing a database. It goal is to provide high-level support to access data à la SQL. Said data might be a .CSV file, a filesystem, a bunch of industrial equipments, a relational (or not) DBMS, an Excel file, .. whatever.

ADO objects, methods, properties are not supported nor managed nor implemented by the AUtoIt ADO UDF! All machinery resides inside the ADO DLL you use.

@mLipok's UDF has very little room to "do everything for everybody". This AutoIt ADO UDF is -- like other AutoIt ADO UDFs -- just a relay between AutoIt coder and the specific ADO DLL (s)

he selected to use, itself dictated by the choice of the DB engine or data source itself.

There is zero possibility for this ADO.au3 to decide if object/method/property/parameter X is implemented or not in the DLL the coder uses. For instance, if method MoveFirst is not implemented in my_fancy_db_ado.dll there is no possibility for mLipok to substitute its own code. mLipok's UDF is generic (that's the whole purpose of ADO) and from its end, has zero knowledge about the guts inside the actual ADO DLL, which is dedicated to a particular DB engine.

Hence, mLipok documentation can only describe the higher-level AutoIt functions offered by his UDF, then refer to an agnostic documentation (like the one from W3School already mentionned) for the description of full-fledged generic ADO, which may have full or partial support in a given my_fancy_db_ado.dll.

As per adding functionality, I can't see how you can do that from outside the ADO DLL you use (for which you may not have source code).  Don't think "AutoItObject.au3": this is stuff to create your own objects, not expand functionality of objects implemented in a separate DLL.

To illustrate the inability of an AutoIt ADO UDF to work as you would want it to, consider the following SQL statement:

with recursive Rnd (n, x) as (
     values(1, Random())
       union all
     select n+1, Random() from Rnd
     where n < 10
)
select n, x from Rnd;

In your AutoIt code, you connect to some DB managed by engine YYY, using a DLL DDD you designate.
Then you invoke mLipok's ADO Execute function with this SQL statement; this yields a RecordSet object RRR. With RRR you read a number of rows using RRR.Movenext method (remember the RRR methods reside inside the DDD dll, so is beyond your control as well as mLipok's control).  At some point right in the middle or at EOF, you invoke method RRR.MoveFirst to re-read from the first row.  If this method isn't fully implemented in DDD how do you think mLipok can do it for you?

Even without a CTE (recursive or not), even without the non-determinism of Random() I used above, just think of a very simple plain select statement. In the middle of a number of .MoveNext, you invoke .MoveFirst and say that the ADO dll you use doesn't support it. The AutoIt ADO layer (wrapper) might store rows on the fly at each .MoveNext invokation so it would be theoritically possible to process .MoveFirst by its own.
But then, it would have to intercept and process by itself any subsequent change in cursor position, because the cursor inside the DLL is still pointing to the position it had before it processed your .MoveFirst and it would need to intercept .BOF, .EOF, ... as well and do the job of the DLL because of the loss of sync in the recordset between old DLL cursor and AutoIt ADO cursor.

Another point: you can't just re-issue the select statement because even if you could (a very big IF and you don't even have the right to do so at any rate), SQL makes no assumption about the order the rows fed to a recordset when an order clause by isn't specified.  It's uncommon for a DB engine to provide rows in a varying order when a select is issued twice in a row, but it would be perfectly legitimate to do so, for instance, to make best use of some cache. If you intend to process .MoveFirst (or its friends) outside the DLL, how would you do?

I hope this clarifies who does what. Thanks for your patience!

 

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
3 hours ago, jchd said:

I'm going to simplify things in the text wall below.

Thx @jchd

Thank you for the detail.  I think the important part is to state that ADO is very high level, and by way of exclusion, what it is not capable of, such as  ADO objects, methods, properties are not supported nor managed nor implemented , and that it is in the end merely a wrapper for which ever data driver (ODBC or otherwise?) being used...  This also means that the user must be very familiar with the capabilities of the chose data driver (DLL).

I use the ADO UDF daily and it works like a charm :)


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
On 3/14/2019 at 4:24 PM, mLipok said:

@xrxca 

Did you read other sites like this :

Actually did a fair bit of research  (The rabbit-hole went straight on like a tunnel for some way, and then dipped suddenly down...)

On 3/14/2019 at 9:20 PM, jchd said:

Indeed not all ADO interface layers support all CursorTypes. Some support all inconditionally, some offer those the DB engine offers, some support only some (conditionally or not),  etc.

And this was my conclusion (and I rarely expect a 3rd party driver to support all features).  So I just wanted to suggest that if a recordset doesn't support the ability, it shouldn't throw an error in a function like _ADO_Recordset_ToString and since it's already checking compatibility for .Bookmark it made sense to me to also check before using .MoveFirst

 


By far, the worst four letter word (swear word) out there has to be USER

Share this post


Link to post
Share on other sites

Thank you for this ADO interface. 

 

I'm using it with Postgres.

 

When executing an insert query with a return command something weird happens:

 

$Query = "INSERT INTO table VALUES (DEFAULT, 2, 3, 4) RETURNING id"
    
    $oRecordset = _ADO_Execute($oConnection, $Query, False)             ;Working as expected
    
    $aRecordset = _ADO_Execute($oConnection, $Query, True)              ;Creates three times the execution of $Query
    
    $sRecordset = _ADO_Recordset_ToString($oRecordset, "|", False)      ;Creates duplicate execution of $Query  
    
    $aRecordset = _ADO_Recordset_ToArray($oRecordset)                   ;Creates two more dublicates

 

Share this post


Link to post
Share on other sites

I'do not have access to Postgres currently, will try to check your case this week.
But what exactly you mean when you state "Creates duplicate execution" .
Is that mean that multiple records/rows were added instead single one per each _ADO_Execute() calling ?


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication 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 *

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

11

Signature last update: 2018-10-31

Share this post


Link to post
Share on other sites
2 minutes ago, mLipok said:

I'do not have access to Postgres currently, will try to check your case this week.
But what exactly you mean when you state "Creates duplicate execution" .
Is that mean that multiple records/rows were added instead single one per each _ADO_Execute() calling ?

Yes, multiple rows appear in my database table.

 

I could imagine this happens on other SQL servers too, as soon you use an INSERT with RETURNING command.

Edited by snoopie
Line added

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

  • Similar Content

    • By Blois
      Hi Guys,
      Fine?
      I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error.
       
      #include <GUIConstants.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Example() Func Example()     Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)     If @error then Return SetError(@error, @extended, 0)     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")     $adoRs = ObjCreate("ADODB.Recordset")     GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)     Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING     $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')     Local $aResult     With $adoRs         .CursorType = 2         .LockType = 3         .Open($queryInsert, $adoCon)         If @error Then             ; deal with Probable SQL error             Return SetError(1)         EndIf         If Not .EOF Then $aResult = .GetRows()         .Close()     EndWith     $adoRs = 0     _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))     For $iRow_idx = 0 To UBound($aResult) - 1         GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)     Next     $adoCon.Close     GUISetState()     ; Loop until the user exits.     While 1         Switch GUIGetMsg()             Case $GUI_EVENT_CLOSE                 ExitLoop ;~             Case $idButton ;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)             Case $idListview                 MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)         EndSwitch     WEnd EndFunc   ;==>Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError)     ; Do anything here.     ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _             @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _             @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _             @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _             @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _             @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _             @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _             @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _             @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _             @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc   ;==>_ErrFunc Help, Please!
       
    • By TheSaint
      Only early days at this point, but I have been pondering such a program for a while.
      As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those.
      What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ.
      When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option.
      A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show.
      The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point.
      At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding.

      Older Screenshots
      Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max.
      As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI.
      My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that.
      Here is another screenshot, of what you see when you click the larger Cover image.

      If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such.
      Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted).
      Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section.
      CalibBrowser.zip  - Upload 4  (previous downloads: 1 + 12 + 5)
      My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it.
      Enjoy!
       
    • By 9252Survive
      Hello guys! 

      I am having some difficulty in achieving a very simple task here. I have gone through the forums and tried some examples and UDFs but I can't seem to work it out. I would really appreciate if someone could help me out. 

      Problem:

      Currently, I am logging the required feedback from the script into a log file in a simple way ... get the info in the var >> write it in the file 

      But now I am in need to perform some analysis and need some of the values to go into an MSSQL table  in the attached format
      Also, I need to be able to use Integrated Security" or "Trusted_Connection set as true or use the logged in windows credentials to connect to the server/database




      any help will be much appreciated!!!  

      Thanks! 
    • By dangr82
      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  
    • By argumentum
      ..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 =/
×
×
  • Create New...