SQLite Forum

cannot create tables in sqlitestudio than other apps can see
Login

cannot create tables in sqlitestudio than other apps can see

(1) By Steve (steveinalabama) on 2021-06-27 21:23:58 [link] [source]

I am having trouble getting a Delphi program to use sqlite. First, the program would fail with a "data exception" error, followed by a long hex number.

I made a guess that since the database file was created on a win7 computer, and now I am on a newer Win10 computer, that maybe I need to create the ...db file on the win10 computer.

I had trouble creating the database in sqlite studio. After creating and connecting to it, it stayed frozen on the creation screen. There is an ok button but it was greyed out.The only other button was a test button, and clicking that returns a green check mark.

I then created a table and wrote sql code to insert a record. I ran a select * on the new table and the data is there.

I reran the delphi program and the good news is there was no external error. The bad news is it tells me there is no such table. I checked the spelling and it is correct.

I got a clue when I went into dbbrowser for sqlite and loaded in the new database file. On the database structure tab, it shows zero tables. At least this is consistent with the Delphi program,but the obvious problem is how to get sqlite studio to commit the table addition?

There is a "commit changes" button which I hit and it appears to work because if I exit the program and come back in, the table is there.

I copied the file sqlite3.dll to the same folder of the database file, but same results.

Thank you.

(2) By Larry Brasfield (larrybr) on 2021-06-27 22:53:38 in reply to 1 [link] [source]

I am having trouble getting a Delphi program to use sqlite. First, the program would fail with a "data exception" error, followed by a long hex number.

Is it a Delphi program you created? If so, what was it doing? If not, would you not suspect that its identity would help others see what might be wrong?

I made a guess that since the database file was created on a win7 computer, and now I am on a newer Win10 computer, that maybe I need to create the ...db file on the win10 computer.

An incorrect guess. SQLite database files have a platform-independent format.

I had trouble creating the database in sqlite studio. After creating and connecting to it, it stayed frozen on the creation screen. There is an ok button but it was greyed out.The only other button was a test button, and clicking that returns a green check mark.

That is obviously self-contradictory. You had trouble "creating the database", yet after creating it, you saw some stuff. Do you mean after attempting to create it?

I then created a table and wrote sql code to insert a record. I ran a select * on the new table and the data is there.

This suggests you were able to create some database. Was it the same as the one you created, attempted to create, or had trouble creating?

I reran the delphi program and the good news is there was no external error. The bad news is it tells me there is no such table. I checked the spelling and it is correct.

This is the first useful clue. Did that spelling include the whole, absolute pathname of the database file? I would guess not, and venture that if you specify an absolute pathname you will begin to see that your "sqlite studio" session and you "delphi program" session are not referring to the same database.

I got a clue when I went into dbbrowser for sqlite and loaded in the new database file. On the database structure tab, it shows zero tables. At least this is consistent with the Delphi program,but the obvious problem is how to get sqlite studio to commit the table addition?

I think you need fewer DB access tools, not more. What makes you think your earlier "sqlite studio" session did not commit the table creation when you were able to select from the table? Did you explicitly open a transaction and never close it? I'm guessing not because that would have been a relevant fact for you to report.

There is a "commit changes" button which I hit and it appears to work because if I exit the program and come back in, the table is there.

You seem to be reporting that "dbbrowser for sqlite" works as you expect. I do not see how that advances the investigation.

I copied the file sqlite3.dll to the same folder of the database file, but same results.

That is immaterial. A "red herring".

I think you need to focus on exactly what file is being opened/modified by your program as a database, then use one reliable tool to see what is in fact happening to that same database. I would recommend the CLI shell as that tool because it will save a lot of typing and/or guessing about clicks, buttons, etc. as you proceed in your investigation and obtaining help here.

(5) By Steve (steveinalabama) on 2021-06-28 03:39:59 in reply to 2 [link] [source]

Thank you for your reply. The Delphi program is one I created, and it does nothing but on a button click connect to the sqlite connection, then open a table with a "select * from tablename".

Trouble creating a database was in sqlitestudio. I entered a newfile name including a folder, selected "add a database" and there was a button that said create, bit it takes me to the same screen as when I select "browse for I navigated to . After retyping a new file name it as assuming I was trying to load a file, even though I select the "create" button.

When I selected an existing db file created in dbbrowser, these was an ok button that is greyed out, so I could not get out of that screen until I hit esc.

The comment I made about checking the spelling was in the Delphi program, where the connection parameters include a databas file with folder name and ...db name.

I also checked the spelling of the table name in the sql statement, a simple name like "mytable" in lowercase in db browser and in the select statement. When the query is run, a message of "table not found" is displayed, and sometime I get the "external error has occurred" from the same program, same table. My guess is that when it told me there is no table of that name, I went into one or all 3 of my sqlite programs, and without making any changes I run the program again and it gives me the external error.

I will try the cli tool you mentioned. Thank you.

(3) By Simon Slavin (slavin) on 2021-06-28 01:46:22 in reply to 1 [link] [source]

The SQLite file format is identical on all versions of Windows.

We can't help you with 'sqlite studio'. It's not part of SQLite and the SQLite team didn't develop it.

The most likely cause of your problem is that the different programs you're using expect to find their databases in different folders. You crease a database using one of them, and it's stored in one folder. You tell the other to open a file of the same name but it's looking in a different folder so it doesn't find it.

Fixing this is nothing to do with SQLite. If that really is your problem, you need to understand Windows folders and paths better.

(4) By Steve (steveinalabama) on 2021-06-28 03:12:35 in reply to 3 [link] [source]

Thank you.

I created a new database and table in com-sqlite. I went into sqlite studio and also in db browser for sqlite and neither could see the .db file because it was not in the folder where I just created it. It turns out there is another .db file by the same name in the ...appdata... folder, and when I go back into com-sqlite it still lists the .db file in the same solder I tried to create it in where db browser and ...studio could not find it.

Go figure.

(6) By Simon Slavin (slavin) on 2021-06-28 12:25:11 in reply to 4 [link] [source]

I think you need to get used to specifying paths in all your applications. At least until you figure out which folder each of your apps wants to use.

In the meantime, you can copy the database around from folder to folder, or use aliases to have them all point to the same database file.

(7) By Ryan Smith (cuz) on 2021-06-28 13:17:08 in reply to 1 [link] [source]

Having some bit of Delphi experience I can tell you that the above errors might not be at all what you think. It should work perfectly well.

I typically use a direct import of the SQLite DLL but the built-in Data-objects should work just fine too, though sometimes setting up the connection can be finicky and you have to watch out for any auto-magic transactions being started on your behalf. You could try the free SQLiteSpeed from here sqlitespeed.com to see a working Delphi SQLite engine, and if that system opens the file correctly then it should rule out Delphi being a problem. (You are also welcome to our unit code that deals with the SQLite DLL interfacing).

Other things to consider might be UAC messing with virtualizing the files so the file you "think" you are opening might not be the one you are indeed opening. Some older versions of Delphi used to not play well with Windows manifests and using correct user folders and the like.

I think the issues you are seeing are just some combination of the above, there are not much else I can imagine that will go wrong. SQLite is an abnormally solid piece of kit, and Delphi, while an overly expensive dev environment and having some bugs in near every IDE version, has a rather solid and well-maintained compiler and component library (what you pay all that money for).

If the above does not help to solve your problem, perhaps showing some code or posting a project on some file-share would help to enlighten.

(8) By Steve (steveinalabama) on 2021-06-28 17:45:03 in reply to 7 [link] [source]

Here is a possible clue: the folder containing the .db file under properties is read only. I unchecked it, applied it, and reran the program and got the same results. I went to folder properties again and it is read only again.

I went to windows permissions and gave full access to everyone, but same results.

(9) By Richard Damon (RichardDamon) on 2021-06-28 18:06:39 in reply to 8 [link] [source]

Some folder are just protected because of where they are. What is the path that you are trying to access.

(11) By Steve (steveinalabama) on 2021-06-28 19:15:21 in reply to 9 [link] [source]

The folder path is a simple folder name off of the c drive The Win10 properties say read only, but it is not. I was able to create and change new txt files with notepad, I am able to create a new database or add new columns to an existing db file in the same folder.

But I continue to get "unable to open database file", witch the file folder name and file name are correctly shown in the sqlconnection1.parameters.

The same code runs fine on my windows 7 desktop. On both systems the path contains the sqlite3.dll file. The Win10 computer was a recent Windows install via "reset". The delphi was then installed from the same ESD file as used for my working Win7 computer. The same program can successfully connect to Oracle and Interbase on both computers.

I wrote a program with 2 lines of code, for the button1.click:

edit1.text := sqlconnection1.params[2]; // shows me the db file sqlconnection1.connected := true;

and edit2.text shows "database = c:newffmyfile.db";

(13) By Ryan Smith (cuz) on 2021-06-28 19:50:08 in reply to 11 [link] [source]

Well if all you say is true, then there is nothing wrong with any of what you done, nothing wrong with SQLite, nor Delphi, and the answer is something like "Something weird is afoot on your system".

But since, I'm an unbeliever in magic, could you do the following:

Drop a TMemo on the main form, make it nice and big. I'm assuming the default name of "Memo1"

in the button click add this code:

procedure ....
  var n : Integer;
      a : AnsiString;
begin  
  Memo1.Clear;
  for n:=0 to sqlconnection1.params.count-1 do 
    memo1.Lines.Add(sqlconnection1.params[n]);

  Memo1.Lines.Add('');
  Memo1.Lines.Add('Driver:     '+sqlconnection1.DriverName);
  Memo1.Lines.Add('lib:        '+sqlconnection1.LibraryName);
  Memo1.Lines.Add('Connection: '+sqlconnection1.ConnectionName );

  Memo1.Lines.Add('Metadata:   '+
    IntToStr(Integer(sqlconnection1.ColumnMetaDataSupported)));

  Memo1.Lines.Add('Filecheck1: '+ 
    IntToStr(Integer(FileExists('c:\newff\myfile.db')));

  Memo1.Lines.Add('Filecheck2: '+ 
    IntToStr(Integer(FileExists(sqlconnection1.params.Values[2])));

  try
    with TFilestream.Create(sqlconnection1.params.Values[2],fOpenRead or fmShareDenyNone) do begin
      SetLength(a,15);
      ReadBuffer(a[1],15);
      Free;
    end;
  except 
    on Ex:Exception do a:='Exception: '+Ex.Message;
  end;

  Memo1.Lines.Add('SQLite Mark: '+String(a));

end;

I typed it from my head so please correct any syntax errors.

Run it and paste us the resulting text in the memo.

If the compiler fails on any of those that will also be very telling, so please let us know.

(14) By Steve (steveinalabama) on 2021-06-28 23:27:07 in reply to 13 [link] [source]

Ryan,

Here is part of what you asked for:

DriverName=Sqlite

Database=c:newffmyfile.db HostName=steve-laptop

Driver: Sqlite lib:
Connection: SQLITECONNECTION Filecheck1: 1 parms [1] DriverName=Sqlite parms [2] = parms [3] Database=c:newffmyfile.db parms [4] HostName=steve-laptop Filecheck1: 1 SQLite Mark:

I changed the code to show the first 4 param values. The code "params.values[3]" does not compile - when you click on params it shows the word values as a column heading, but params does not have a property called values - I just write something like label1.caption :== connection name.params[3].

I cannot find which unit I need to add to my uses clause to get "Fopenread" or "Fsharedenynone". If you can tell me what to add to "uses" I will get back to you

(15) By Ryan Smith (cuz) on 2021-06-29 00:01:22 in reply to 14 [link] [source]

Apologies, that's "fmOpenRead" and "fmShareDenyNone" which is defined in the same unit as TFileStream, so it must be available once spelt correctly.

The code for the Values thing would actually be of the form "ValueFromIndex[n]" or "Values['name']" but the stuff you posted already answered that question.

The only important (fixed) code to still run is:

  try
    with TFileStream.Create(sqlconnection1.params.Values['Database'], fmOpenRead or fmShareDenyNone) do begin
      SetLength(a,15);
      ReadBuffer(a[1],15);
      Free;
    end;
  except
    on Ex:Exception do a:='Exception: '+Ex.Message;
  end;

  Memo1.Lines.Add('SQLite Mark: '+String(a));

This time I did syntax check it on my side, so the copy paste should work directly.

This just reads the first 15 bytes from the file and will tell us if we see the correct file, if it is accessible, if it can be opened by you/your program, and if it is indeed a valid SQLite file. If so, it should say something like "sqlite format 3", or else, give a telling error.

(16) By Steve (steveinalabama) on 2021-06-29 00:53:43 in reply to 15 [link] [source]

here is the final memo line from the corrected code.

SQLite Mark: SQLite format 3

There was no exception message.

(17) By Ryan Smith (cuz) on 2021-06-29 01:27:35 in reply to 16 [link] [source]

Ok, well, verdict is in then. Your file exists in the place you think it exists, your file works and it should all run smoothly.

I'm now starting to think perhaps sqlitestudio is a victim of the UAC virtualization or such, since DB Browser and your code seem to agree.

Perhaps an arbiter can be called in, would you mind trying the Delphi-made SQliteSpeed to open the file and poke around in it? Or better yet, if you are ok with using a CLI, use the sqlite3.exe CLI tool downloadable from sqlite.org and open the DB and confirm the tables presence or absence mentioned before.

Use one of those tools to also add tables/data and see if your program sees the same.

If the theory about the UAC holds, you should find a file with the exact same name but hidden somewhere in the appdata folder of your user. You can search for it there using the windows searcher to confirm.

(10) By Larry Brasfield (larrybr) on 2021-06-28 18:10:49 in reply to 8 [link] [source]

I have not yet seen any good reason to believe that your Delphi app and whatever trusty tool you use to independently view/alter the database are both accessing the same file. Because of the way SQLite auto-creates the DB file if it does not exist, such file discordance is a problem we often see here. And your initial post showed the symptoms of it. That is why Simon, Ryan and I have suggested measures to ensure you are not suffering that problem. Yet, instead of either seeing that issue resolved or enough clues to show that is not your issue, we keep seeing various workaround attempts based on hypothetical issues for which no evidence has appeared in this thread. It makes for a very disjointed joint troubleshooting effort, bordering on hopeless.

(12) By Steve (steveinalabama) on 2021-06-28 19:25:09 in reply to 10 [link] [source]

Thanks for your reply, Larry.

It may be hopeless. My error messages switch between "external exception" and "unable to open database file. I am no longer getting the file table does not exist message, which seems like it just went away on its own.

(18) By Steve (steveinalabama) on 2021-06-29 05:04:43 in reply to 7 [link] [source]

I downloaded SQLITESPEED and the databases and tables it shows are consistent with db browser for sqlite, and with sqlitestudio. The com-sqlite program gives me different results so I will not use it any more, as it is using a different table in ...appdata... although it says it is reading it fro "C:\newff".

I have working Delphi programs using sqlite db files on my Win7 desktop. When I copy the db file to my Win10 laptop, it gives me the external exception error. When I copy the EXE file for my Delphi program to the laptop, it also fails with the exception error. When I copy the Delphi source code, the dpr, dproj, pas and dfm files and compile them on the laptop I get the same results.

When I copy a .db file created on the Laptop to my desktop and try a Delphi program to read it, I get the unable to read file error message. Something on the laptop is hopelessly screwed up, even though it had Windows 10 and Delphi reinstalled within the last week.

The obvious difference is Windows 7 sqlite programs work and Windows 10 fail.

One other difference - the DBXCONNECTIONS.INI file which Delphi uses or data explorer has 2 items on my win7 pc:

[SQLITECONNECTION] DriverName=Sqlite Database=C:STEVEMYNEWDATA.DB

and the laptop has a 3rd line: hostname = steve-laptop

I can't see that being a problem but thought I would mention it.

(19) By Ryan Smith (cuz) on 2021-06-29 10:31:44 in reply to 18 [link] [source]

using a different table in ...appdata... although it says it is reading it fro "C:newff"

It's fun when a completely unrelated problem in a different software, or hardware/OS errors on the machine, makes you think your software is broken.

In case you are interested, this is why the first replies to your problem statement started suggesting the UAC virtualization might be the culprit.

TLDR:UAC+Old Software does this --------

Long ago in Windows and its historic unsafe file systems, software (and virii) had the unsafe ability to write anywhere in the system. Windows attempted to fix this by preventing software, or making them ask permission, to write to any place deemed "unsafe".

Problem is this would break a lot of legacy software, so Windows added the "helpful" UAC virtualization that, if you (as the older software) try to write to a place deemed unsafe and you did not ask to do so (Elevate) or state your intents in the Windows executable manifests, it would take the file you are trying to write, say "c:securefoldermyfile.txt" and internally redirect (soft-link) it to a "safe" space over in the user's own /AppData folder.

As you can imagine, older programs (like the one you've used) that did not adapt to play nice with the manifests and UAC requirements, would fall prey to this problem. To add insult to injury, a user could simply turn the UAC off, and then everything would work again, but there was no way for software to know whether a file it was accessing was being virtualized or not.

The amount of confusion this "magic" caused is hard to measure, but I can confirm it is measured in blood and tears - you being the latest victim.

/TLDR

As to your problem, it does sadly seem something is amiss on that computer, because the things you are trying to do should work fine, as you noted, and indeed it does on other places, as you also noted, and Windows 7 already had UAC, so that's not the difference. You are also correct in that the hostname is of no consequence in that DB object.

Is there any sort of drive-encryption or file-encryption software active on any of the computers? We've also seen antivirus software preventing write access while it is checking on a file. Either way, this seems a system-related problem.

Good luck!

(21) By Steve (steveinalabama) on 2021-06-30 01:57:51 in reply to 19 [link] [source]

Still no success. I spent the data formatting the hard drive on my laptop, doing a "clean install" of windows, which took much longer than doing the "Reset" option, then I wrote a new Delphi program on my working win7 pc. I created a new .db sqlite adtabase file, and I added the folder and name of the .db file to a tedt.

When I click the go button, it first checks that file exists for the value in the tedit, then sets the connection params[1] to that value, then opens the connection and runs the query.

I then copied the exe file and the .db file to a usb drive and it runs fine on win 7. I put the usb drive in the laptop of went there to run it. I had the change the drive letter, as the usb drive on the laptop is "D", not "E" like the win7 machine.

I right clicked on the file to get the properties, and as I hoped, it did not say read only like it does on all the other files I have on that machine.

I ran it with great hopes... it said the file was there, and it ended with the same error message "unable to open database file".

I ran it in SAFE mode to turn off the antivirus and firewall in case they were blocking it. I am completely out of ideas.

Is there any person out there that can run a Delphi 7 on a win7 computer, then see if it runs on win 10? I realize most people probably don't have a win7 and a win10 pc.

(22) By Ryan Smith (cuz) on 2021-06-30 10:50:24 in reply to 21 [link] [source]

Hang on, did you say Delphi 7? As in not recent XE7, but the very ancient version from the 1990's? That might be fixed by simply using compatibility mode. Also, that version would not compile exe's that would run well on Windows 10, it did not have manifests at all, and a few other shortcomings. It was also exclusively 32-bit, as was all exe's it made, so all DLLs used must be 32bit.

That error seems very unspecific though.

You should really upgrade, and if you don't like the new expensive Delphi's you could use the very fantastic (and much better in my opinion) open source FPC (Free Pascal Compiler) and Lazarus IDE which you will be right familiar with coming from D7, and you'd be able to use 99% of your existing source code as-is.

If you insist on making D7 work on a Win10 PC, well that sounds interesting to me and I'm willing to have a look with you, but this thread has been quite off-topic for this forum already and now it's crossing into the abyss. Mail me (ryansmithhe at gmail dot com) to discuss further without polluting this forum.

(24) By Steve (steveinalabama) on 2021-06-30 15:56:02 in reply to 22 [link] [source]

sorry bout that, it was a type. I am on Delphi 10.3, reinstalling it on my laptop now, as the hard drive was formatted and win10 reinstalled yesterday.

(20) By anonymous on 2021-06-29 10:41:00 in reply to 18 [link] [source]

Is there some magic whereby the connection can find the file with no path separators, or does that occur in the post markup?

That would save some escaping contortions in various languages on Windows.

(23) By anonymous on 2021-06-30 11:25:44 in reply to 20 [link] [source]

That was my thought too, that the forum was eating backslashes.

c:\test should have one.

I have just verified that Markdown style eats backslashes!

Fossil is OK, ditto plain text if we believe the preview window.
Posting this in Plain text mode.

(25) By Steve (steveinalabama) on 2021-06-30 16:10:19 in reply to 20 [source]

I tried running the program with and without the slash like d:bob.db and d:bob.db. Both had the same error, and in both cases the "if fileexists" returned that it does exist.

FWIW, I went into dbbrowser for sqlite andtried to open the database without the slash and it said file not found.

(26) By Larry Brasfield (larrybr) on 2021-06-30 16:33:45 in reply to 25 [link] [source]

You may consult the forum markdown rules to avoid confusion such as the above engenders. Note also that you can edit your posts to correct markdown mishaps should they escape your preview effort.

On the thread topic: I thought you were past pathname difficulties. This thread is confusing as to what the problem(s) and symptoms are, and whether the problem-plagued program is a moving target for diagnosis by others.

(27) By Steve (steveinalabama) on 2021-06-30 17:03:43 in reply to 26 [link] [source]

Sorry for causing confusion.

The problem is I get the message "unable to open database file" when I run a Delphi program on my Win10 laptop.

This is a message that could mean there is no sqlite database file by the name specified in the program, or because the file is in a read-only folder, or windows 10 is blocking it via Defender or the firewall, (but the file really is there in the folder the program expects it to in, the read-only attribute is not set for the folder as specified, which is why I put it on a usb thumb drive, as all older folders have the read-only attribute set even though the are not read only), or the database file is corrupted, though I can read the same file on a win 7 computer, and have tried several program to create the database file, and none of the files can be opened on Windows 10).

I have tried to ensure the correct dll for sqlite is on the path and in the folder with the database file and where the .exe file is.

As far as I know, I have eliminated every known possibility that could cause the "unable to open" message, including reinstalling Window 10 and Delphi 10.3, but obviously I overlooked something.

(28) By Simon Slavin (slavin) on 2021-07-01 12:33:53 in reply to 27 [link] [source]

You are confusing the folder that your program files (including DLLs) are in and the folder that the database file is in. The two things have nothing to do with one-another.

You are also confusing yourself with different versions of Windows, and with with firewalls. Neither of those things have anything to do with your problem, which is far more simple.

I still don't think you understand how to specify a full path in Windows correctly.

In the program you're using, try specifying the full path of your database file to both programs. All the way from the C: down to whatever folder you want your database file in. When you have used each program to create a new database file, go check the timestamp on that file and verify that that file really was created by whatever it is you just did. Use both. programs to create new database files using explicit paths and check to see that they can both do it, and both really. are creating their own new database files.

If it doesn't work when you try to create databases on C:, use a Flash drive instead, and specify the full path to a folder on that Flash drive. You should be able to read and write files on a Flash drive on whatever computer the drive is in.

Only when you've got both programs to create new database files, try accessing the same file from both programs.

(29) By Keith Medcalf (kmedcalf) on 2021-07-01 13:41:23 in reply to 28 [link] [source]

And don't forget: DOS does not allow special characters, so alphanumerics only, no symbols or spaces ...

(31) By Steve (steveinalabama) on 2021-07-01 18:34:24 in reply to 29 [link] [source]

I have had what looked like some "progress" which raises more questions 1. I created a new Delphi program with a button whose onclick says "tsqlconnection1.open"; the connection parameters set by me in the IDE are:

DriverName=Sqlite Database=c:mynewdata.db HostName=steve-laptop

I made several attempts with the filename beginning with "localhost" which did not help.

with the db file in the root directory, with no "" It would fail with "unable to open database", but I went into data explorer, selected sqliteconnection and right clicked and tried to "modify". I entered steve-laptop as the host name, and "C:mynewdata" with the backslash, after nothing else worked. When I click test connection, it succeeds!

I dropped a tsqlquery on the form, wrote a simple select statement, and set the connection to sqlconnection1. When I run it I get external exception C06D007F.

In the IDE under data explorer, under sqliteconnection, there are no entries under table, views, or procedures, even though the properties of sqlconnection1.connected is TRUE.

I right clicked on "tables" in data explorer and it gave me an option for a new table. I selected that, added a couple fields, but when I went to save it, the error message was "attempt to write a readonly database".

I sent back to the connection params and entered a table name that was not in the root directory, making it necessary to have backslashes. Clicking test Connection still works, and the new table still does not appear under "tables". When I run the program I still get the external exception error.

(32) By Scott Robison (casaderobison) on 2021-07-01 18:42:37 in reply to 31 [link] [source]

Windows 10 does not allow create and / or write access to the root of the C drive by default. Thus if you try to open a database in the root as a normal user, it will appear to succeed (because SQLite doesn't actually open the file right away, deferring that until a subsequent access of the file). Only once you try to do something that accesses the database will it open the file and the journal, and since you don't have create access in the root by default, open fails.

(33) By Larry Brasfield (larrybr) on 2021-07-01 19:02:47 in reply to 31 [link] [source]

  1. I created a new Delphi program with a button whose onclick says "tsqlconnection1.open"; the connection parameters set by me in the IDE are: DriverName=Sqlite Database=c:mynewdata.db HostName=steve-laptop

When you use "C:filename" as a file path, what it means to the OS is "In the process current directory as kept for the 'C:' drive, find or create and use a file whose plain name is 'filename'. In other words, its meaning depends on something unlikely to be well controlled or understood easily by anybody reading the code.

I made several attempts with the filename beginning with "localhost" which did not help.

If that made any difference, it would mean you are using either a driver attempting to involve a network layer or one created by demented folk.

with the db file in the root directory, with no "\" It would fail with "unable to open database", but I went into data explorer, selected sqliteconnection and right clicked and tried to "modify". I entered steve-laptop as the host name, and "C:\mynewdata" with the backslash, after nothing else worked. When I click test connection, it succeeds!

That is because, with that leading backslash (after the drive specifier), you were specifying an absolute path, just as I, Simon and others have advised. (See above regarding "process current directory".

And please, in future posts, even if you cannot learn all of the markdown rules, double your backslash characters to produce something that renders as \. Without doing that, your single backslashes render as nothing, leading to needless confusion and/or effort to look at your post in raw form.

(36) By Simon Slavin (slavin) on 2021-07-02 16:33:40 in reply to 31 [link] [source]

I told you to specify a full path to the file in all the programs you're using to create/access databases. You specified just which drive it was on.

You obviously don't know what a full path means. Please search. the web and read results until you understand it.

Unless you specify a full path to the file we have no idea what's going wrong or how to help you.

(37) By Scott Robison (casaderobison) on 2021-07-02 19:58:11 in reply to 36 [link] [source]

I believe a full path is being specified but the backslash character is being eaten by markdown because it is not escaped.

(38) By Simon Slavin (slavin) on 2021-07-03 12:35:56 in reply to 37 [link] [source]

A full path would have a number of folder names in it. Even if the backslashes were being eaten there would be many characters between the 'C:' and the filename.

(40) By Larry Brasfield (larrybr) on 2021-07-03 13:07:03 in reply to 38 [link] [source]

(This not specifically to Simon. It is more on where this thread has gotten and is going. And I do not think Simon needs instruction on topicality.)

This forum is not about how to specify a file by pathname on the Windows OS. It is about SQLite and its usage. It is fine, friendly, and in line with that topic that people who have run into various programming difficulties while using SQLite will often get tips that are not really about SQLite except in the most tangential way. But when such difficulties and help stray well away from SQLite-related issues, the discussion is no longer on-topic for this forum.

It has become apparent that the OP needs to learn how to use pathnames on Windows. This is fundamental and not a SQLite issue. In the spirit commonly exhibited here, (to contributors' credit), I recommend to the OP: Study Microsoft's explanation of Windows pathnames to see how the pathnames you pass into the SQLite API or one of its wrapper libraries will relate to files located in a Windows filesystem. This is not the place to be getting a tutorial on that subject, whether all at once or bit-by-bit-by-bit... as is happening here.

To others, I would ask that the OP be firmly encouraged to go to more appropriate places for instruction on non-trivial non-SQLite problems. Most contributors here are or want to be helpful, and most hesitate to post anything that might offend. But we should not allow such nicety to degrade the utility of this forum for people who come here with actual SQLite-related issues.

(41) By Steve (steveinalabama) on 2021-07-03 14:12:58 in reply to 40 [link] [source]

I have used this SQL in my query: "select * from sqlite_masterq" purposely misspelling the table name, and the program runs correctly, giving the "no such table..." message.

This shows to me that the program correctly connects to the specified database name, but when I correct the table name it failed with the external error.

I have run the same code in Lazarus, using the same database file name, and it runs fine.

(42) By Larry Brasfield (larrybr) on 2021-07-03 15:36:29 in reply to 41 [link] [source]

Steve, The SQLite library is tested extensively to ensure not just basic functionality but correctness with many thousands of input variations exercising feature combinations. The chance that something as basic as table existence after creation failing, in any version of the library ever released, are vanishingly small. The odds are overwhelmingly in favor of your problem being with your code.

If you were to use the CLI shell, with text input of your choosing, to demonstrate the failure that you see, that would be totally on-topic here. If you in fact demonstrated a failure of the SQLite library (which is used by the CLI shell), and reported it here with reproduction steps using that shell, it would be fixed quickly, with thanks for your report and care to author it.

What you are seeing is a problem arising from incomplete and ambiguous specification of the database file.1 We can see evidence of that in your posts, which has led to the recommendations regarding use of "absolute" and "fully qualified" pathnames to specify the database file. You need to understand what that means and why it is important, which my above link will facilitate with a few minutes of your study. That use of time is far more efficient than you writing here about various GUI apps, button clicks, with improperly rendered pathnames due to markdown misuse, and other arcane details familiar to few here, then hoping somebody can see what you are doing wrong and tell you enough about it after many readers spend their time trying to sort it out for you.

Now, I will respond to your post #41 in a way related to this forum topics.

I have used this SQL in my query: "select * from sqlite_masterq" purposely misspelling the table name, and the program runs correctly, giving the "no such table..." message.

Good. That means that you know how to recognize when the library report an attempt to reference a nonexistent table with SQL. And you have confirmed correct operation of the library.

This shows to me that the program correctly connects to the specified database name, but when I correct the table name it failed with the external error.

This apparently gives meaning to your term, "the external error". It is nothing mysterious -- just an ordinary SQLite library usage error.

I have run the same code in Lazarus, using the same database file name, and it runs fine.

That's great news. Now, if you will figure out how to consistently and reliably refer to the same database file2 for creation operations, queries, and diagnostic/verification steps, you should be past the problem mentioned in your post #1.

Other problems, if they are related to the SQLite library, should appear in a new thread with an apropos title. I encourage you to first replicate such problems with the CLI shell, and post here showing the text inputs, outputs, and what you expected to be different. That effort will help you to separate Delphi/Lazarus/GUI and program logic problems from true SQLite problems. And when you find a true SQLite problem that way, it will be quickly (and efficiently) solved here when presented as I suggest. And there will be no question as to whether the thread is on-topic.


  1. As has been already mentioned in this thread: Due to the auto-DB-create feature of sqlite3_open(), successful "connection" does not imply that the intended database file has been opened, especially when that intent is weakly expressed with a relative pathname.

  2. As has been recommended repeatedly, use absolute (aka "fully qualified") pathnames, per Microsoft's explanation of Windows pathnames, on the Windows OS to avoid ambiguity and ensure replicability as to what file is named.

(43) By Steve (steveinalabama) on 2021-07-03 17:52:31 in reply to 42 [link] [source]

Problem solved !!!!! The solution was for me to use the 32 bit sqlite3.dll in Delphi's bin folder, but to use the 64 bit sqlite3.dll in the folder containing the .EXE file.

The only remaining problem is the text fields appear in the dbgrid as "MEMO" or (WIDEMEMO". Integer fields show in the grid correctly.

My workaround is to use a tmemo component and add sqlquery1.fieldbyname(fieldname').asstring;

I have seen solutions offered suggesting to have the sql cast (myfield as varchar) but that did not work.

Still, I am happy the data can be retried.

I am grateful for all people who have responded.

(44.2) By Scott Robison (casaderobison) on 2021-07-03 18:50:48 edited from 44.1 in reply to 38 [link] [source]

A full path would have a number of folder names in it. Even if the backslashes were being eaten there would be many characters between the 'C:' and the filename.

Correct. My comment was based on an earlier comment where it was said that the root drive was being used, hence only needing to have a single unescaped backslash character.

Your comment is exactly correct, a fully qualified path needs to be used (I think) to resolve this problem. I was only pointing out that the particular test case was trying to use the root directory, which would not need a long path, and would likely result in a failure to open because of an inability (by default) to create and / or write to the root directory of the C drive in Windows 10.

(30) By anonymous on 2021-07-01 15:06:56 in reply to 27 [link] [source]

May I suggest a brute force solution such as SysInternals Process Monitor?

It should at least tell you which file the combination of Delphi and UAC is attempting to read.

(34.1) By Steve (steveinalabama) on 2021-07-01 20:32:43 edited from 34.0 in reply to 30 [link] [source]

I just used Process Monitor and one line at the time of the error said "Regkey" followed by a lot of hex characters and then the words "name not found desired access R"

I could not see anything that indicates what name is not found, or which file name it is trying to open...

I went into Regedit and found the registry key listed in Process Manager, but on the right side the value was "Window Property Store".

Right below the key, it has the word "Inprocserver32". Might this mean I am using a 32 bit DLL on a 64 bit operating system?

Will keep looking

(35) By Steve (steveinalabama) on 2021-07-01 23:45:57 in reply to 34.1 [link] [source]

In the Delphi IDE the target if forced to windows 32 bit.

Instead of creating a new VCL Forms Application, I should have chose to create a new multi device application, which allows me to select Win32, Win64, Android and Linix. I thought that explains it all.

But rerunning it as a Windows 64 bit target, it no longer bombs with the external
exception error. It immediately ends after the connection is set to active.

The next statement in the code is a showmessage, then sqlquery1.open, then another showmessage. But the show messages never happen and there is no error message. The program just ends and disappears from the screen.

(39) By anonymous on 2021-07-03 13:02:41 in reply to 34.1 [link] [source]

When you first start process monitor a dialog may open "Process Monitor Filter".
If it doesn't, click on the funnel icon for Filter.
Where is says "Display entries matching these conditions", change the first drop-down to "Path" and the second to "contains" and then put the filename part of your database name in the entry box (mynewdata) or possibly just (.db) if you want to watch for other names, ignoring the ().
The 3rd drop-down should be "Include", then click "Add" and "OK".
This will start capturing thousands of events but will only display the ones matching the filter you just added.
Running the different approaches should show the Process Name of the application, what it was trying to do, and the full path of the file it was trying to open/create/read/write etc.
Once you have stopped your app, you can click on the magnifying glass icon or press Ctrl-E to stop capturing while you look at the data you've go so far.
As the earlier poster said, this is the way to really know what is happening.

On my Win10 PC using the sqlite3.exe shell, trying (note that you have to use a / )
.open c:/testdb.sqlite
create table fred...;
.quit
and process monitor shows it trying c:\testdb.sqlite and then moving on to c:\Users\myname\AppData\Local\VirtualStore\testdb.sqlite.
You may also see access to .wal and .shm files with the same name as your database.

You may find that other apps add their preferred database suffix (.db or .db3 or .sqlite or .sqlite3 etc) to what you enter if they don't recognise your choice of suffix.

Hmmm, I have just repeated this test on my Win7/64 machine, and got the same result. I note that the sqlite3.exe is not signed, and has no manifest (which can prevent the redirection to the virtualstore leading to an application error).
Trying to create c:\fred.sqlite using SQLiteStudio results in a dialog saying "You don't have permission to save in this location".
Trying to open the supposedly created c:\testdb.sqlite in SQLiteStudio returns a "Could not add database" and Process Monitor shows there's no attempt to access the VirtualStore.

Len.

(45) By Steve (steveinalabama) on 2021-07-05 04:10:54 in reply to 39 [link] [source]

Thanks for your reply and the info on using Process Monitor.

I found the cause and solution to my problem (64 bit sqlite3.dll and 32 bit sqlite3.dll)

More details are above . I should have replied to the last post so mine would appear at the bottom until some one posts something.