Sign in to follow this  
Followers 0
Jat421

MySQL UDF

15 posts in this topic

#1 ·  Posted (edited)

Hi, I am using the mysql udf to connect to remote mysql server. Wanted to check on the best way to accomplisying the following.

I have a table with two columns. One is called id that is auto increment and the other one is link_no. So, basically the idea is to get a link_no from the table and delete it at the same time.

So the number is only used by one user and not get picked up by other user. It will be a really busy database and many users will be grabing the link_no. So my question is what are the chances of users grabbing the same number? and what is the best way to minimize the delay time between a link_no picked up and deleted? Thanks for any help!

This is what I have so far. I am little confused about the delete statement

$i = _Query($sql, "select link_no from temp_tbl limit 1")
      _Query($sql, "delete from temp_tbl where..
with $i
$a = .Fields("link_no").value

   MsgBox(0,'test',$a)

_MySQLEnd($sql)

Please let me know if you need any other info. Thanks!

Edited by Jat421

Share this post


Link to post
Share on other sites



From this website (Link) I found the following:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';
COMMIT;

Maybe this will help you.


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Thanks, this might help me. Can I send all these SQL statements in one query like this?..when I try it gives me a error "Return $oConnectionobj.execute ($sQuery)^ ERROR"

$i = _Query($sql, "
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';
COMMIT")
Edited by Jat421

Share this post


Link to post
Share on other sites

Also I am looking at LOCK TABLE that might work for me. Any suggestions on that?

Share this post


Link to post
Share on other sites

What you seem to need is simply this SQL:

delete from mytable where link_no = 123456;

This will atomically delete the row(s) having link_no = 123456

Of course you'll have to provide the value you need in place of 123456.

This single statement can't be interrupted and is atomic, which means that it either succeeds or fail altogether as part of one operation. Hence no two users can have issues with that behavior, either by reading (SELECT) or writing (UPDATE or DELETE).

If you need a more complex condition, just expose it so we can provide actual help.


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

#6 ·  Posted (edited)

Thanks Jchd for helping!...so when a select statement and the the delete statement is sent is it possible for other user to send select statement within those two queries?

And if a second user sends a select query before the delete statement of the first user he/she will get the same number?

Sorry if I am getting this totally the wrong way.

Edited by Jat421

Share this post


Link to post
Share on other sites

As a general rule, SQL engines use transactions to handle concurrency. Any single SQL statement is atomic (google that) which means you can't "see" an intermediate state thru another connection: either the single statement is not started (or fails) or it's completed.

Now you see that this isn't enough in very simple cases: using your favorite ATM is a common case. The ATM "reserves" the requested amount until the notes are delivered. No other operation on your card/account can take place in between, or that would violate the invariant "ATM can't deliver more than the account limit + allowed credit".

SQL offer a simple construct for that: explicit transactions. Transaction (a BEGIN statement, then a series of other SQL statements, then either a COMMIT or a ROLLBACK) make the statements between BEGIN and COMMIT (or ROLLBACK) atomic: either fully completed or not at all. During a transaction, other connections can't see what's going on, they acces the previous state only. After COMMIT or ROLLBACK has completed, other connections see the new state only.

As I understand it, your table has two columns; ID of type identity (an autoincrement integer) and a link_no integer supplied by you.

If you need to delete the row(s) having link_no = <some value>, just use a DELETE as in my previous post.

If link_no contains unique values, then you're garanteed to delete at most 1 row. Else SQL will faithfully delete any row satisfying the condition, inside an implicit transaction.

If you do:

SELECT link_no from mytable where <which condition should go here?>

and put that into a AutoIt/php/python/ruby/whatever variable you cal $var, then

DELETE from mytable where link_no = <content of $var>

you're doing exactly what the single-line DELETE does, but you're no inside an atomic transaction. So if after your SELECT, another connection (user or thread or program) runs the same SELECT, the first connection then proceeds with the DELETE, then the second transaction tries to DELETE as well (using the same link_no value!), it won't delete anything since the row(s) are already deleted.

In this scheme, you just don't need to first SELECT then DELETE. Use the WHERE clause of the DELETE statement to specify which row(s) the DELETE should remove.

My question would be: how do users (or programs or thread) choose the link_no value?


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

If he does a

SELECT from table LIMIT 1;

he'll recieve the first line of the query.

So it's kind of a "random" or a first-in-first-out situation he wants to create.

The problem here is that he doesn't want to rely on the "id" column of the table.

Another thought would be to add a third column that shows whether it's already "marked for deletion".

1. Insert a user specific value:

UPDATE temp_tbl SET mark = '" & @UserName & "' LIMIT 1

2. Run the Select

SELECT link_no FROM temp_tbl WHERE mark = '" & @UserName & "' ( LIMIT 1 )

3. Delete the row

DELETE FROM temp_tbl WHERE mark = '" & @UserName & "'

What you think about that?


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

Ah, is that a really kind of random single unique pick?

If so, that's almost as easy:

DELETE from mytable where id = (select id from mytable limit 1)


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

Ah, is that a really kind of random single unique pick?

If so, that's almost as easy:

DELETE from mytable where id = (select id from mytable limit 1)

I can't see where he will get the value from link_no from that statement.


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

So you interpret the OP problem and link_no semantics as a token distribution, randomly selected from a table?

It was very unclear to me what the aim was, so this may be the reason.

If so the table design isn't the right choice: remove the ID (serves no purpose) and make link_no the primary key.

Then in the probable case where the picked link_no will be used for significant time by the user who picked it, your proposal works fine.

If users are permitted to interrupt the program before the final delete occurs and if a given user may not pick more than one token at a time, one may have to clean up posible orphan rows from time to time. For instance by performing an

update temp_tbl set mark = null where mark = QuotingAndEscapingFunction(@UserName)

before the update.

However how to properly deal with orphan tokens should be dictated by the actual OP context.


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'm not 100% sure if I got the OP request right as well. :)

we'll see what the OP really wants to achieve if he answers to this. ;)


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

My question would be: how do users (or programs or thread) choose the link_no value?

When a user clicks a button the first link_no in the table will be given to that user. It doesn't matter what number they get as long as two people don't get that same link_no. So that link_no should be deleted as soon as it's displayed on a users screen.

And I think you are correct I don't need the column id auto increament. I can very well make the link_no primary. Yes, you are correct that link_no is uploaded by me in the database.

I think hannes suggestion of adding the third column just might work. So, by doing that the first SQL statement marks the first row with a username and no other user can get that number and only that user will get it and then it can be delete after. Great idea!!. Thanks!!!

Edited by Jat421

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

hannes08 has the right idea, but there should be more tests while assigning the number and LIMIT 1 in an UPDATE clause is AFAIK not alwayys working.

Table: link_no (PK), user_id

- assign link_no:

UPDATE linktable SET user_id='THISUSER' WHERE link_no = (SELECT link_no FROM linktable WHERE user_id = '' LIMIT 1)

- get link_no:

SELECT link_no FROM linktable WHERE user_id = 'THISUSER'

- delete link_no:

DELETE FROM linktable WHERE user_id = 'THISUSER'

or: DELETE FROM linktable WHERE link_no = link_no_previously_stored_in_script

Edited by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

Thanks that looks like better way of doing it. Thanks you again all for your help!!

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
Sign in to follow this  
Followers 0