ADO.au3 UDF - BETA - Support Topic

[Valnurat]

I have another question about with this UDF. My goal is to make a application that will handle the content of a SQL db. I know for sure that more than 1 person would edit the content in some way. Do you read the content to an array with this UDF? What happens if 2 people for an exampel edit the content at the same time? The content will then be read in an array on each copy of the runned application.

[mLipok]

Who is the SQL DB Developer ? Only you , ? or you are working with DBase which are part of third party software ?
Can you add new column to DB Schema ?

[Valnurat]

I'm the SQL DB Developer. But some of the content can be updated of users who is goint to use the application. Yes, I can add new column to the DB Schema.

[mLipok]

you can do what you need but as far as I know you must to block the changes, by adding new column in which you will set the blocking state, and check it by each other user.

Maybe there are other way , but I'm not so burly brain to know all the tricks

 

Edited February 19, 2017 by mLipok

[jchd]

Rather use an SQL transaction (BEGIN; ... ; COMMIT) around read-modify-write SQL block.

[mLipok]

I think @Valnurat is asking about that situation:
user1: is reading content and start processing
user2: is reading the same content and start processing in another way, so he finish faster
user1: is finishing his processing

I think user @Valnurat is asking what to do if we want to stop User2 from reading content, until user1 is finish his processing.

I do not think in this case Transactional processing is changing antyhing, but as I said I do not know  all the tricks.

 

Edited February 20, 2017 by mLipok
typo

[jchd]

What you describe isn't possible in bare SQL. Transactions (either implicit or explicit) isolate users. To synchronize processes as in your example you need some IPC between applications (or threads or whatever else).

To answer post#1 you can block a second writer by using an IMMEDIATE or EXCLUSIVE transaction issued by the first writer. All other users will see the DB state before the transaction began. Only when first writer issues COMMIT all users will see the DB state after the transaction has taken place.

There is no such thing as two users updating the DB simultaneously, technically speaking. Some client/server engines use giant caches and sophisticated algorithms to make you believe two writes can occur simultaneously but at the DB level, that is plain impossible. Else the ACID properties of the DB would be jeopardized.

In your last example, if user1 intends to read-modify-write stuff, he needs to do:

-- DB is in state A for all users
[user1] BEGIN IMMEDIATE;
[user1] SELECT stuff
[user1] mix up stuff inside application
[user1] UPDATE old stuff
-- and/or
[user1] INSERT new stuff
[user1] COMMIT;
-- DB is now in state B for all users

Right after BEGIN IMMEDIATE is fully processed and until COMMIT isn't, all other users can only see the state A. At the moment when COMMIT is fully processed, the state of the DB is B for all users.

Engines differ in how the locking is implemented and how wide it is. For instance SQLite locks the entire DB, while other engines (mostly client/server types) can only lock a table, or even affected rows or even affected columns of affected rows.

[Valnurat]

Aha, I see your point.  The question is if this UDF does this?

[mLipok]

@Valnurat can you confirm do I have right about what you are asking ?
This is important as then we can think more what to do exactly in your case.

btw.
Here are some documentation/articles to read:

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-ado

https://support.microsoft.com/en-us/help/198024/info-understanding-ado-transactions-with-ms-sql-server

https://msdn.microsoft.com/en-us/library/ms971557.aspx

https://msdn.microsoft.com/en-us/library/777e5ebh(v=vs.110).aspx

https://technet.microsoft.com/pl-pl/library/jj856598(v=sql.110).aspx

https://social.msdn.microsoft.com/Forums/en-US/841e5e94-f8a1-4a62-9564-f3dafcb852be/select-statement-hangingtable-locked-until-sqltransaction-isolationlevel-readcommitted-ends?forum=adodotnetdataproviders

http://www.vbforums.com/showthread.php?736995-ADO-Exclusive-table-lock-with-SQL-Server

 

[mLipok]

1 hour ago, Valnurat said:

The question is if this UDF does this?

The question is not if this UDF does this but: how this UDF does this ?
The answer is you should read all this documentation/articles which I posted few minutes ago, and find out how to use it with this UDF.

I can not answer precisely, as I do not have the oportunity to use this feature which you are asking, and I currently have a small amount of spare time (Unfortunately, still, but things are improving).

I worked on a few new UDF (TeamViewer, Chilkat, and some other) to my work, but I still have a lot of confusion and I need to do more to work than to the community (I hope to back and finish some projects and then get on with few new).

But you can always ask and I or someone else will try to answer as much as possible.

Edited February 20, 2017 by mLipok

[Valnurat]

Yes, it is true.

Here is my goal. I have a small tool that should update the SQL DB automatic. That tool will run i non working hours.

What my tool will do, is that it should look for 3 columns. If 1 of them has changed then it should update the record. Do I read all content of the SQL DB to an array or do I do a "select...something from TableName" with some criteria?

[mLipok]

2 minutes ago, Valnurat said:

Do I read all content of the SQL DB to an array or do I do a "select...something from TableName" with some criteria?

I think the second option should be much better in this case.

Edited February 20, 2017 by mLipok

[Valnurat]

Every time our users login to our system, a file is generated. In that file, username, model of the computer and the servicetag is added. The name of the file is the_name_of_the_computer.txt. If the username has change or the servicetag is empty in the SQL DB, I need to update the SQL DB record for that computer. How can I pick the records I need with my criteria and should it still be the second option? It could be 300 + files every day. I don't know if I aproach this task the correct way.

[Valnurat]

If I do this:
 

$sQUERY = "Update " & $sTableName & " Set Shipdate = 'test' where ComputerName = 'DKSOLT1453'"
    $oRecordset = _ADO_Execute($oConnection, $sQUERY)

How can I check if the _ADOExcute went well?

How can I use the $oRecordset?

[jchd]

An update statement doesn't return any result, so the assignment to $oResultset is a no-op and can be removed.
Note that if no row with ComputerName = 'DKSOLT1453' exists, this isn't an SQL error as (1) the statement is valid and (2) it didn't have anything to do (but that is perfectly legal).

ADO would normally raise an error if something  turns bad, so use an object (COM) error function to trap those cases.

Edited February 21, 2017 by jchd

[Valnurat]

Ok. Thank you.

[jchd]

Reread my edit above, just in case.

[Valnurat]

This:

 

[Valnurat]

I see you are talking about  BEGIN IMMEDIATE; and COMMIT;

I have seen these in ADO.au3:

__ADO_EVENT__BeginTransComplete

__ADO_EVENT__CommitTransComplete

is that what you are referring to? If so, how do I use them?

Edited February 21, 2017 by Valnurat

[jchd]

Ask the UDF author and/or refer to associated documentation. BTW the identifiers you mention look like event names. Traditionnally, transaction statements are processed with SQL Execute function, i.e. statements not intended to return a resultset.