Link to home
Start Free TrialLog in
Avatar of kinnon_2000
kinnon_2000

asked on

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





Avatar of Johnjces
Johnjces
Flag of United States of America image

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
Avatar of kinnon_2000
kinnon_2000

ASKER

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

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
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.
ASKER CERTIFIED SOLUTION
Avatar of Johnjces
Johnjces
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.