delphi sqlite service ap issue

Hi,

I've found a weird and annoying problem when running a delphi made service which uses sqlite on a vista machine.

The sqlite wrapper i'm using, with a few tweaks to allow for multiple connections, came from here: http://www.itwriting.com/blog/a-simple-delphi-wrapper-for-sqlite-3/comment-page-1#comment-133030

Great wrapper, simple and easy to use.

On windows XP, which is what I use for everything, if i create a service that writes to and reads from an sqlite database, it works just fine. If I have another app reading from that database, I also see the updates coming through exactly as expected.

If I run the same service on a vista machine, it sems to do as required, but, if I then run a second app, reading from the same database file, the data is not correctly shown. It seems to not get the updates the service is applying, and this only happens when using a service app.

If you download sqliteman and run the same query over and over, after your service updates the tale, the updates dont show in sqliteman. if you do the same test with an identical application instead of the service, every update is shown in sqliteman.

I've build two examples.using delphi 2006 one is a service, and one is a normal windows app. install and uninstall bat files are included. once you have the service running, rename the normal app to something like, normtest, and copy it into the service folder, then run it, and keep pressing the update. you will see that the value is the same each time, and yet if you look in the services debug.txt file, the values seem correct.

Hope this makes sense. I've included all files in a zip for anyone who wants to have a look at this wierd issue.
The database files have been renamed from data.db to data.doc so they would upload, so you'll need to switch the filenames back again.
In care theres a problem with the attachement, I've also uploaded it to here:
http://www.hebware.co.uk/sqlite_service_issue_example.zip

regards,
kinnon





kinnon_2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnjcesCommented:
What version of Vista are you using and are you using TCP/IP to communicate to the service which I am assuming is controlling the SQLite database?

If you are using Vista home and TCP/IP connections, you hit a wall at two (2) concurrent tcp/ip connections.

Just a thought.

John
0
kinnon_2000Author Commented:
Hi John,

The database is a local file in the app folder. All comms are via a tiny little dll, so as far as i'm aware, no tcp/ip.  See below for examples of code to execute and select data to/from the database file.

Just had a look, the vista details are as follows:
Windows Vista Business Service Pack 1 on dell optiplex 760.

I really wish my manager hadn't baught vista as an OS!

Any ideas?
procedure DoQuery(qry:String);
var
sldb: TSQLiteDatabase;
progdir:String;
begin
  progdir:=extractfilepath(paramstr(0));
  sldb := TSQLiteDatabase.Create(progdir+'data.db');
  try
    sldb.ExecSQL(qry);
  finally
    sldb.Free;
  end;
end;
 
function GetVal(field:String):String;
var
sldb: TSQLiteDatabase;
sltb: TSQLIteTable;
progdir:String;
begin
  progdir:=extractfilepath(paramstr(0));
  result:='';
  sldb := TSQLiteDatabase.Create(progdir+'data.db');
  try
    sltb := slDb.GetTable('SELECT '+field+' FROM test');
    try
    if sltb.Count > 0 then begin
      result:=sltb.FieldAsString(sltb.FieldIndex[field]);
    end;
    finally
      sltb.Free;
    end;
  finally
    sldb.Free;
  end;
end;

Open in new window

0
JohnjcesCommented:
Ahhh.. Welcome to vista!

You might just try turning User Access Cpntrol (UAC) off and test as there "could" be something there.

If you run this test application as an administrator, do you still get the same problem?

John
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

kinnon_2000Author Commented:
Hi, some updates on this.
John, your on the right track. Issues are UAC based. My app wont even write to text file without right click run as administrator. How anything is expected to work without hassle I dont know.

I've resorted to dumping the service idea for now as I couldnt for the life of me work out how to get it running as administrator. I created a non visual app to do the job the service was doing, and executed by the primary program, which says, is my liilt prog running, if not, execute it.

I found that right click - properties - advanced - start as administrator and win xp sp2 compatibility settings enabled, on both apps, made a big difference.

I really dont like Vista.

Any idea if there is a guide on dealing with UAC as a Delphi developer?

Regards,
Allan.
0
JohnjcesCommented:
Allan,

UAC is talked about a lot here on EE. One of the big things is to do proper Vista manifest and compile them into your exe and your service exe as a resource. Again search here on EE for "vista manifest" and "UAC".

In services make sure they are running under an administrator account and of course have a proper manifest.

That's about all I can help you with.

John
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kinnon_2000Author Commented:
Hi,

Thanks for the advice. got the manifest working and making progress. some other issues to work out but getting there. thanks again for your help.

regards,
allan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.