Need a better Insert no duplicates, method

[HatForCat]

Using SQLdb and I want to prevent the user adding an existing AccountName. Currently I m using the "UNIQUE Column of SQLite and rolling it back during Post if a duplicate exists. But that is slow and messy.

Slow because it doesn't happen until the user has entered a lot of other data (using TDBEdit) only to find out it can't be used and the slate is cleared. Quite annoying. I'd like the duplicate to be checked on the TDBEdit.OnExit. I could use TEdits and transfer everything in and out but a lot of extra work?

Messy because the UNIQUE is is case sensitive, so "Dave" "DAVE" and "dave" are allowed. Currently I have set the TDBEdit.CharCase to ecUpperCase, but do not like forcing a user to bend to my will. If they like seeing "Dave" then that's what they should see. The underlying database is not mine, so I canNOT ("NOT" = EDIT)  add another column for an upper-case version of the Account.

Have not been able to find anything that checks and alerts with OnExit without causing the user some grief.

Anyone have a slick solution to this?

Thanks

[ASerge]

create unique index AccountNameUnique on AccountTable (UPPER(AccountName))?

[HatForCat]

create unique index AccountNameUnique on AccountTable (UPPER(AccountName))?

Thanks, but, that scrolls the database off the current "insert/Edit" position. I appreciate it if you can provide me some code that would do the check and not move the database-cursor as I may not be doing that correctly.

[mangakissa]

What's the problem to create an other function to search for the name in the same table?

Code: Pascal  [Select][+][-]

1. function SearchAccName(const aName : string) : boolean
2. //SQLConnection is the commonname of the component to connect to the database.
3. //On firebird could be TIBConnection and MySQL TMySQL40Connection (mysql4)
4. var Myquery  : TSQLquery;
5.      MyTrans   : TSQLTransaction;
6. begin
7.   Myquery  := TSQLquery.create(nil);
8.   MyTrans   := TSQLTransaction.create(MyQuery);
9.   try
10.     try
11.       Myquery.database := SQLConnection1;
12.       MyTrans.database  := SQLConnection1;
13.       Myquery.transaction := MyTrans;
14.       MyQuery.SQL.Text := 'SELECT lower(name) FROM accounttable where name = LOWER(:name)';
15.       MyQuery.Params[0].AsString := aName;
16.       MyQuery.active := true;
17.       result := MyQuery.recordcount > 1;
18.       MyQuery.active := false;
19.     except
20.       result := false;
21.     end;
22.   finally
23.     MyQuery.free;
24.  end;
25. end;
26.  

[mse]

I appreciate it if you can provide me some code that would do the check and not move the database-cursor as I may not be doing that correctly.

TmseSQLQuery (MSEgui) can search in local indexes without scrolling the dataset. See TLocalIndex.Unique() or the variants of TLocalIndex.Find().
https://gitlab.com/mseide-msegui/mseide-msegui/blob/master/lib/common/db/msebufdataset.pas

[HatForCat]

What's the problem to create an other function to search for the name in the same table?

Thanks for that, but it will not compile.

I get an error "udm.pas(242,40) Error: Incompatible type for arg no. 1: Got "Class Of TSQLTransaction", expected "TDataBase"

for

Code: Pascal  [Select][+][-]

1.       Myquery.database := TSQLConnection;
2.       MyTrans.database  := TSQLConnection;
3.  

Unfortunately, that is well beyond my SQL skills to know how to fix that. I tried various brute-force things that did allow it to compile, but then got runtime errors. "Cannot start a Transaction within a Transaction," etc.

Also, I do not understand the "[naam]" thing. I have done some searching and cannot finding anything to explain what's is expected.

Sorry for being such a dunce, but pretty new to Ubuntu, Lazarus and SQL.

[mangakissa]

I created the example in my head and not verified the code. I change it to the correct components.
TSQLConnection is the classname. So it's can't called directly and should bet SQLConnection1 (or another name you used for the component.

Also, I do not understand the "[naam]" thing. I have done some searching and cannot finding anything to explain what's is expected.

Also changed it. name (or naam) is the field for searching. If you want to understand what function lower() means: http://stackoverflow.com/questions/341338/sql-changing-a-value-to-upper-or-lower-case or https://docs.faircom.com/doc/sqlref/33436.htm

[ASerge]

create unique index AccountNameUnique on AccountTable (UPPER(AccountName))?

Thanks, but, that scrolls the database off the current "insert/Edit" position.

What is the position? It runs once without a program to solve the problem: "...Messy because the UNIQUE is is case sensitive, so "Dave" "DAVE" and "dave" are allowed..."
In order not to annoy users by refusing I would prefer to use the following sequence: add a blank record with the selected AccountName, Post it to run a quick scan for duplicates and to reserve. Then allow to enter values in the remaining fields. On error, delete the record.

[HatForCat]

I created the example in my head and not verified the code.

Thanks, I had guessed that might have been it. But, with the "brute force" I referred to, I had done exactly that (dm.sqlConn) and then got the next error "Cannot start a Transaction within a Transaction."

Clearly this is all beyond my current knowledge of using SQL directly. Up until recently I had been using ZEOS and was isolated from the complexities of underlying SQL. But, ZEOS 7.0/7.1/7.2 was proving to be buggy and unreliable, so I decided to bite the bullet and fall back to the bare SQLdb.

I guess I will live with the "UNIQUE" approach at Post and roll back on error. I have set the "Name" DBEdit to UpperCase so that will take care of the "Dave" "DAVE" and "dave ," issue with "UNIQUE."

I appreciate the attempts to help, but I really have no desire to do a full-course in SQL as I am Retired and probably will not use it again for years. I was just trying to emulate what I was used to doing with Win/Delphi/IB/ADO etc. I am rewriting an email program I wrote in Win/Delphi and I will most-likely be the only one using it, so will live with the irritation of the duplicates check at Post as opposed to DBEdit1.OnExit.

I felt sure this had already been well covered and several code snippets might be available. But my hals-day searching proved otherwise. It seems most programmers are comfortable using the UNIQUE-at-Post approach. Not a user-friendly approach at all, making them wait until all the other stuff has been entered to tell them the Name was not acceptable.

Thanks to all, anyway.

[Thaddy]

I appreciate the attempts to help, but I really have no desire to do a full-course in SQL as I am Retired and probably will not use it again for years. I was just trying to emulate what I was used to doing with Win/Delphi/IB/ADO etc.

Where you would have run into the same issue. There is not much difference between Delphi and Freepascal. If your methods worked in Delphi it should also work in Freepascal.
E.G. you mention IB, well Firebird is a child of IB, so maybe you should prefer to use Firebird as your embedded database over Sqlite. That should be familiar to you. And works the same.

[HatForCat]

Where you would have run into the same issue. There is not much difference between Delphi and Freepascal.

Not to belabor the point, but I stated in the first post that I have no control over the SQL database, so stuck with it I am. Also, using DataAware components in Delphi is quite a lot different to using SQLdb in Lazarus. There are many more automated processes available to remove the load from the programmer. But as I said in a past post, I'll live with the After Post banging, crashing and loud screaming. Checking for duplicates in OnExit, is a two line code thing in Delphi.

[mangakissa]

Tel me, how should you do it in code if you were using Delphi?

[egsuh]

One way I can think of is as follows:

1.  You have to find ways to "lock" database (if multiple users are accessing the database), or "lock process" using Critical Section, etc. (if DB is used by single user)     --> You don't have to lock if single user is accessing DB.

2.  At OnExit event,

     - Search DB for record with UPPERCASE(filedname) = uppercase('Edit1.text');
        (This will be done by SQL statement "select * from table where upper (user_name)= 'DAVE'" if Interbase.)
     - If found, inform the user (saying the "user name is duplicated")
     - if not found, "insert into database" as the user typed in.

3.  Unlock DB or process.

4.  Let the user fill in other forms (not changing user name), and update the content.

This process will work, I think.  I don't know how to "lock" and "unlock" DB or processes in SQLite / Lazarus. But they should not be very difficult.

[HatForCat]

Tel me, how should you do it in code if you were using Delphi?

Delphi Data-aware TDBEdits have an OnValidate field.

[HatForCat]

This process will work, I think.  I don't know how to "lock" and "unlock" DB or processes in SQLite / Lazarus. But they should not be very difficult.

Thanks, I am just going to live with the brute-force UNIQUE crash after a Post.

Seems like a lot of database programmers are not that concerned about user-friendly as the entire  data-aware fields are all cleared out on the UNIQUE error. I felt sure there must be hundreds of code snippets, but then when I look at "Professional" sites like Amazon, msoft-store etc and see how lazy-programmer focused they are I can see why nothing much is available.

It's like a punch in the face t the user clearing everything. But it's the way of the World these days.