|
|
Post by kokenge on Jul 25, 2008 8:03:25 GMT -5
When adding records to a table, usually you have a control number.
For example when adding a person to the DB you may have a unique personNum. Looking to the DB to find the last number will tell you the last number used. This however only allows you to add the next largest number. We all been here before, and eventually you have to reorganize the file to fill the holes where records were deleted, because the numbers simply get too large. If you have lots of related files the reorg system can be complex and you usually have to take the system down for a few hours.
So I always like to use numbers of deleted records. This eliminates complex reorg systems and downtime.
Anyway I have a very simple SQL statement, but for some reason it will not give me the last number used "max()". So I have to do it in 2 SQL statements..
What am I doing wrong...??
For example this will not work. WHY???
sql$ = "SELECT a.personNum,c.personNum,a.personNum - 1,max(a.personNum)
FROM person AS a
LEFT JOIN person AS c
ON c.personNum = a.personNum - 1
WHERE c.personNum is null
AND a.personNum > 1 LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows > 0 then
result$ = #sql nextrow$(" |")
aa = val(word$(result$,1,"|"))
cc = val(word$(result$,2,"|"))
useNum = val(word$(result$,3,"|"))
maxNum = val(word$(result$,4,"|"))
end if
print "rows:";rows;"use:";useNum;" max:";maxNum
Currently I use this function that works for any file. It uses any unused numbers or the last number. But it takes two SQL statements sometimes.
global #sql
nextPersonNum = useNum("person","personNum")
wait
' ---------------------------------------------
FUNCTION useNum(ffile$,ffield$)
useNum = 1
sql$ = "SELECT a.";ffield$;" AS aa, c.";ffield$;" AS cc,a.";ffield$;" - 1 AS useNum
FROM ";ffile$;" AS a
LEFT JOIN ";ffile$;" as c
ON c.";ffield$;" = a.";ffield$;" - 1
WHERE c.";ffield$;" is null
AND a.";ffield$;" > 1 LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows > 0 then
result$ = #sql nextrow$(" |")
aa = val(word$(result$,1,"|"))
cc = val(word$(result$,2,"|"))
useNum = val(word$(result$,3,"|"))
else
sql$ = "SELECT max(";ffield$;") as useNum FROM ";ffile$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows > 0 then
result$ = #sql nextrow$(" |")
useNum = val(word$(result$,1,"|")) + 1
end if
end if
END FUNCTION
Thanks for the help..
|
|
|
|
Post by Carl Gundel - admin on Jul 25, 2008 8:48:09 GMT -5
|
|
|
|
Post by kokenge on Jul 25, 2008 9:49:05 GMT -5
Ok here is why sometimes I have to do 2 SQL statements.
Since it was kind of a simple SQL statement, I thought someone would have the answer here.
Basically all it does is treats the Person file as if it were 2 tables - table "a" and "c". I read the first person table as "a", and LEFT JOIN the same person table as "c". It then tries to find table "c" (the same person file) that has a person number (c.personNum) one less than the first person table (a.personNum) that is equal to NULL. In other words, find some personNum that has a number before it that does not exist (= null). This means there is a hole in the number sequence and I want to use it.
This all works fine, but when I add the max(a.personNum) it will not work. I have to do it in another SQL. So if it don't return any rows with the first SQL statement I have to execute the second SQL statement to get the last number (max(personNum)).
I should be able to do it in one statement since the second person table is a LEFT join..
I guess it's not a real big deal. Currently I have a file with about 80K records and it is fairly active. So it probably does the second SQL about 60% of the time.
Thanks again for the help..
Thanks Carl for the link, I'll try it and see what happnes.
|
|
|
|
Post by mackrackit on Jul 25, 2008 12:26:32 GMT -5
I am sure I am missing something but... Trying to do it all in one query seems like a problem when (max(personNum)) is a variable that is being defined at the same time it is trying to be used. x = y = z Is there a way to save the line number in an array when the record is deleted. Then when a new record is created a value from the array is used for the line number? Probably get into more than two statements though  but only one query to the DB. |
|
|
|
Post by kokenge on Jul 25, 2008 13:33:32 GMT -5
Duh! It was my stupid error.!! Again!
The SQL was not well formed.
This works.
sql$ = "SELECT a.personNum,c.personNum,a.personNum - 1,max(a.personNum)
FROM person AS a
LEFT JOIN person AS c
ON c.personNum = a.personNum - 1
AND c.personNum is null
WHERE a.personNum > 1 LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
For some reason I had the WHERE statement including person "a" with person "c". And of course it will not find a max() under that condition. I moved the WHERE to it's rightful place.
As for arrays. How do you share arrays with everyone?? I have anywhere from 25 to 100 people using the system at any given time. One system with fair activity is a lab system where they do about 200 samples an hour 24/7. Each has to be logged. Plus there are lots of other things taking place.
Each person could be running any number of probably 25 systems. No telling what system they are running or when they decide enter samples or enter something in another system.
Thanks for all the help..
I'm on my way again..
|
|
|
|
Post by Carl Gundel - admin on Jul 25, 2008 14:17:27 GMT -5
Duh! It was my stupid error.!! Again! The SQL was not well formed. This works. As for arrays. How do you share arrays with everyone?? I have anywhere from 25 to 100 people using the system at any given time. One system with fair activity is a lab system where they do about 200 samples an hour 24/7. Each has to be logged. Plus there are lots of other things taking place.
Each person could be running any number of probably 25 systems. No telling what system they are running or when they decide enter samples or enter something in another system.[/quote]
You cannot share arrays. Use the database to share information.
Also, are you keeping the database closed when you're not using it? I would strongly recommend that you:
Open the database
Make your query or queries
Close the database right away
-Carl |
|
|
|
Post by mackrackit on Jul 25, 2008 14:22:18 GMT -5
The 25 to 100 people is the part I was missing  Just thoughts, have not tried... If all of these users are using the same app then the array could be of the global type(what happens when the app is closed?), or the array could be read/write to a file. That would make the data available to all at any time. Wonder what kind of problems that many users doing read/writes to the same file would create? And in what time frame? Can two or more users open the same file at the same time with RB? Sorry I am not being a help, just thinking through the keyboard. |
|
|
|
Post by kokenge on Jul 25, 2008 14:39:12 GMT -5
You won't believe all the hoops I'm jumping through to keep from lockups. And I always open the DB, do my thing, and close it ASAP.
Even then you run into lockups. I have a system now that checks for lockup, and caches for later tries at updates. But I don't like it, and am looking for other ways to do it.
Do you think RB will ever support a real DB. I like the fact that sqLite is simple and it don't run a manager. But being selfish, for me, it would be a whole lot easier if I had a DB manager behind RB.
Have a great day.
Dan
|
|
|
|
Post by Carl Gundel - admin on Jul 25, 2008 14:51:35 GMT -5
Even then you run into lockups. I have a system now that checks for lockup, and caches for later tries at updates. But I don't like it, and am looking for other ways to do it. I'd be interested in how you do this. And yes, there will be a Pro version of Run BASIC that uses the popular database servers. I suspect I may be able to improve the way the SQLite accessor works too. -Carl |
|
|
|
Post by kokenge on Jul 25, 2008 15:59:37 GMT -5
I've tried several methods to prevent disk lockup. The one I'm working on now uses JavaScript to send the SQL commands to a backend process.
The backend writes the SQL to a file. It then reads the file sequentually and executes the SQL commands. Basically it looks like a single user is doing all DB modifications sequentually. So no one to compete with.
Just started to test it, so not sure how it will work??
Dan
|
|
|
|
Post by Carl Gundel - admin on Jul 26, 2008 8:32:12 GMT -5
I've tried several methods to prevent disk lockup. The one I'm working on now uses JavaScript to send the SQL commands to a backend process. The backend writes the SQL to a file. It then reads the file sequentually and executes the SQL commands. Basically it looks like a single user is doing all DB modifications sequentually. So no one to compete with. Just started to test it, so not sure how it will work?? Dan You are using Javascript in the browser to do this? -Carl |
|
|
|
Post by kokenge on Jul 26, 2008 10:28:32 GMT -5
Yes. But there are issues. The Browser JavaScript sends the SQL string to a RunBasic SQL program (RBsql). Like I said I just started to test it so not sure how it will turn out. I wrote a JS routine that accepts data from anything on the screen and/or any string you send it. It sends data back to you or any place on the screen. I documented the JS program here. www.dkokenge.com/ajaxOne issue is that sometimes you actually want to do the round trip for a lot of reasons. So I'm setting up the RBsql to also be executed with the RUN command. I'm sure I'll run into other issues along the way. Since all modifications to the DB happen in RBsql, it becomes the manager, and I suspect before I'm done with it I'll find some surprises. This is probably as clear as mud, but when I'm done testing it, I can document the whole process if anyone is interested. By the way, I could sure use the dynamic variable discussed in another blog (dynamic$$) if that ever became a possibility.. Have a great day.. Dan |
|
|
|
Post by Carl Gundel - admin on Jul 27, 2008 20:37:30 GMT -5
By the way, I could sure use the dynamic variable discussed in another blog (dynamic$$) if that ever became a possibility.. What is dynamic$$ ?? -Carl |
|
|
|
Post by kokenge on Jul 27, 2008 21:19:08 GMT -5
I happen to see a blog somewhere where there was a discussion about a dynamic variable. A variable of a variable.
For example if a$ = "custNum" and you set a$$ = 123, it would be the same as saying custNum$ = 123.
It would certainly make life a lot easier, and reduce a lot of code especially when working with databases.
And the power it would add to RB would be overwhelming.
|
|
but only one query to the DB. 