helpDesk Maintenance Scheduler

[metro]

Started a new thread...

The problem relates to Dans helpDesk program

I'm trying to get this functioning correctly on a linux system and have another glitch.
When I attempt to create a new ticket I fill out all the boxes and then click the "add" button
Win10 system works for me without any mods but the linux system hangs.

I print sql$ prior to placing an error trap in ticket.bas just before the insert and get this when I attempt a save...

INSERT into ticket (ticketNum,issue,issueDate,status,cat,itemNum,priority,deptId,issueBy,asgnTo,asgnDate,compDate,logTime,probem) VALUES ('10','water tank leak','2018-04-12 12:35:16','A','IA','1','3','WH','1','0','2018-04-12','2018-04-12','1','welder')

Error is database is locked
Error number is 0

thanks in advance

[meerkat]

Error is database is locked
Error number is 0

The old famous database is locked. This is a fatal error. It's a problem with Run Basic itself. For some reason Run Basis holds the locks. The only way out is to stop Run Basic and restart it.

I've posted this error way back when Run Basic first came out. Back then they said the next release would allow ODBC or something to allow access to real databases such as mySQL or Postgres. Another year went by and nothing.

This error is the reason I left Run Basic and moved all my Production apps to REBOL now RED. But I still like and mess around with Run Basic as you can tell. It has cost me dearly. I had applications running that I had to rewrite. Not a lot of fun when you have something running with a few hundred users.

Here is a fix that will eliminate 99% of the errors. 99% for me was not good enough when you have production applications running.
Do not use sqliteconnect directly but use the following subrouting

' -------------------------------------
' Connect to Database
' --------------------------------------
[connectDb]
sqliteconnect    #sql,  yourDb$         'use your database routing here
#sql  execute("PRAGMA cache_spill=0")
#sql  execute("PRAGMA journal_size_limit=0")
#sql  execute("PRAGMA journal_mode=WAL")
#sql  execute("PRAGMA locking_mode=NORMAL")
#sql  execute("PRAGMA synchronous=0")
#sql  execute("PRAGMA wal_autocheckpoint=0")
RETURN


The RBgen program that generates Run Basic code does this, so RBgen will help.

I'll make the changes to the helpDesk programs. But like I say, this sql connection is no guarantee that it will work 100%

Even if Run Basic comes out with real database support as the recent post suggest, they have another fatal error that needs to be fixed. There is a memory leak and after a while you run out of memory. Again the only way out is to stop Run Basic.

Good Luck

Dan

[metro]

Thanks for the quick reply Dan,

The only way out is to stop Run Basic and restart it.

thats exactly what I did and yep... it works but for how long

I will try your fix with future apps.
as I'm only doing this for myself I can put up with the occasional glitch

I downloaded RED and have two books ...rebol for dummies + Rebol the official guide,

after playing for a coupe of days I found I'm better sticking with BASIC soo much easier for me

appreciate your help

Laurie

[meerkat]

The helpDesk program is really old code.
I can see by looking at the code, I was trying to get around the locks, way back then, by constantly disconnecting and reconnecting to the DB.
I'm running the ticket program through RBgen and copying detail changes from the current ticket program over just to see what happens. I'll post when all the changes are made to all the programs. Take a couple hours.

I'm with you on Run Basic.. I like it because it's simple and easy. If the new changes Carl talks about comes true, I'll see if I can use it for real applications again..

Ya! REBOL is really different, but once you get the hang of it, it's ok. It's one language that runs everywhere, does web, realtime, great graphics. But YA! it's a brain bender getting used to it.

Stay tuned..
It's our rainy season so I have lots of time in doors.

[metro]

Thanks Dan,
sorry about the extra work

Laurie

[meerkat]

Laurie,

Copy over the new ticket.bas program and see if that works for you. I simply did a RBgen and copied the changes from the old program. You'll notice that RBgen now uses the HTML5 popups for stuff like date, datetime, color, and does numeric checks for you.

I have to attend to some issues the rest of the day, so will look at the other programs later tonight.

BTW.. The RBgen was the guy that was assigning the database path incorrectly for linux. You can get a new copy from the ionSQL directory in www.kneware.com/runBasic

Stay Tuned..

[metro]

Thanks Dan,
I've spent the last few hours re-installing my system after upgrading to an i7
the 1TB HD should be handy.
its 1am here so I'll check out the program in the morning.
thanks again for your help

all the best

edit
could be a couple of days before I re-visit this
RunBasic will not work on my updated system (mint 18.3)
i get an error

Segmentation fault (core dumped)

[metro]

seems nobody on Mint Linux forum has a solution for the Segmentation fault
so I've installed VirtualBox and networked PCLinuxos copied Rb101 to the virtual system
downloaded the work you did last night Dan
got the original DB error

Runtime Error in program 'helpDesk': #sql execute(sql$)
file is encrypted or is not a database

replaced that with the old DB everything worked fine

then on the second transaction of entering a ticket I got

Error is database is locked
Error number is 0

hmm shame, you wouldn't want to spend hours creating programs you cant rely on

hope it can be fixed in the next release

[meerkat]

I doubt it will be fixed. This has been talked about extensively years ago when RB first came out.
Nothing ever happened! They believe that RB was not the problem. So why is it that stopping RB fixes it. Who is holding the lock?
I've spent many many days trying everything in the world to try and get around it. The best I could do is the [connectDb] routine I showed above.
I've even converted RB programs over the LB as a test. LB never ever locks.
There is no workaround I could find.

Funny that some systems you write never ever have a lock. Even though there is nothing different in the interface with programs that do lock.

SQLite is not a managed DB. So to prevent other access while you are in a Write or Update, they lock the entire DB. Most DB's lock at the record level. If you look at the SQLite interface for other languages they protect you from locks by doing a retry if it's locked. So you are guaranteed of success and you never ever see locks.


Basically I gave up!

[metro]

Basically I gave up!

completely understandable, especially with the mission critical systems you look after.

Thanks for support here.

[meerkat]

g'day Laurie,

This was set up for companies with hundreds of thousands of records. If it's for your personal use, I doubt that you will need that many records. With less records the system can live with fewer index. I deleted all the index except for those that are needed.


If you are still using it copy the new helpDesk over and see if you still get a locked database.

At least I'm not getting lockups with it. We'll see what it does on your linux system.

Good luck..
Dan

[metro]

Thanks Dan,
DB still throws the original error but I copied over it with an older DB and it opens ok.

In helpDesk.bas at about line 324 you need to check for the Platform$

if left$(Platform$,3) = "win" then
incdir$ = DefaultDir$ + "\projects\helpDesk_project\"
ELSE
incdir$ = DefaultDir$ + "/projects/helpDesk_project/"
end if

run incdir$;prog$,#include
render #include


I entered 3 tickets and had no locked DB issues
thanks for the help

I'll now customise to suit the job
my son manages a transport yard in the Pilbra region of our state
and maintenance is not being tracked so this should help.

I owe ya an ale or three now

Laurie

[meerkat]

Thanks Laurie,
I also notice there is a lot of *Dir and *Path left over from somewhere thars not needed.
If you want to delete anything with imagePath$, imiagDir$, thumbPath, and thumbDir$ from the programs it should clean stuff up a little. I used to have a complicated way of doing uploads. Now it's all done with the upload_project.
I notice the item program keeps track of photos of the item. It needs the upload program to be installed and published.

Good luck with your additions and changes..

Dan