How to check for duplicate usernames?

Bob Sneidar bobs at twft.com
Thu Aug 4 14:30:57 EDT 2011


Have you dug into the RevDB commands? Typically the commands will return the number of affected rows in it or return (I cannot remember which). All your statement below does is create a new variable called CoundUN containing your sequel statement. It doesn't execute anything. 

You would first have to connect to a database using revdb_connect, then using the database connection ID that returns, query the database using revDataFromQuery() if you just want the raw data and don't want to work with the records, or revdb_execute() which will return the number of rows operated on, which I believe in a select statement would return the number of found records. RevDataFromQuery() will return text of the records in tab or comma delimited format, so you can check to see if it's empty. 

The database library takes some getting used to. I was put off by it at first, but was kind of getting the hang of it after MUCH trial and error. Then Trevor Devore produced sqlYoga, which VASTLY simplifies working with SQL databases. Even that has a bit of a learning curve, as it is a unique API, but it has some tremendous advantages. 

The way it works is, you first create a database object, which is an array in memory to hold the information you need to connect. Next you use dbconn_set to set things like the name of the database, the type, the port, the password etc. Then you use dbconn_connect to connect to the database. This will set the connection object up with all kinds of things, like the schema of the database and tables, the connection ID, and other things. It keeps track of all that for you so you don't have to yourself. 

Now to query the database, you use sqlquery_createObject() to create a query object that acts much the same as your connection object, only it keeps track of things like columns to retrieve, groupings, offset, sort order, etc. Setting these properties in the query object is a simple matter of calling sqlquery_set with the property name and what you want to store in the property. 

Finally to get information back, you use sqlquery_retrieve, sqlquery_retrieveAsArray, sqlquery_retrieveAsData, or sqlquery_retrieveAsRecords. All you have to do in these commands it give it you query object and the variable you want the results to be stored in, and voila! You have your data, without having to know a thing about SQL. 

I find it well worth the price. I could code all this myself using the built in revDB library, but frankly I find this counterproductive, simply because I am not that good at SQL, and the frustration of continually debugging my Sequel tend to make me walk away from projects. 

Sorry for the long post. 

Bob


On Aug 4, 2011, at 10:51 AM, Admin wrote:

> Sorry to sound stupid, but after the select statement, how do I
> then count for how many records got returned? 
> 
> Here's the select
> statement you wrote with my variable: 
> 
> put "select username from
> uservalidation where username = " &SQ &UN &SQ into CountUN
> --count how
> many records got returned. If greater than 0 you have a duplicate.
> 
> 
> SQ's are single quotes and CountUN is the new variable





More information about the Use-livecode mailing list