Hello, I am new to SQLite but have some knowledge of SQL. I have a web page written where I am trying to update an SQLITE database using JAVASCRIPT and WebKit. I have copied and example from one of the online manuals and it appears to work, but I can't find the location of the database. I have tried specifying a directory but it doesn't appear there. Below is the code that I am using to open/ create the database. I now that this should go in C:/users/craig/workspace/... but I can't find it there. any help would b greatly appreciated.

var db = openDatabase('My1stdb.db', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
  tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');
  tx.executeSql('insert into foo (id, text) values (2, "testme")');
  tx.executeSql('SELECT * FROM foo', [], function (tx, results)
	{
		var len = results.rows.length, i;
		for (i = 0; i < len; i++) {
		  alert(results.rows.item(i).text+ ' Row: '+i);
		}
	});
});

Since you did not specify a path the database file will be stored in the "current directory" of the process which created it.

To "search" for the file you can use something like this from a command prompt:

dir <drive>:\My1stdb.db /s/b

substituting in turn for <drive> the letter one by each of every drive attached to your computer. Eventually you will uncover the location of the file.

eg:

dir c:\My1stdb.db /s/b

then

dir d:\My1stdb.db /s/b

and so on and so forth until the file is found.

I have provided a specific path, c:/My1stdb.db, run the page and still nothing. The database opens in memory and updated but is never saved. Is there something that I should be doing to save the db?

The code you posted does not specify a path.

How is anyone to know that you provided a path?

Have you "searched" your computer for the file by name?

If you have not searched for the file by name, then how can you be sure that it does not exist?

update an SQLITE database using JAVASCRIPT and WebKit.

What API are you using? As far as I can tell, the most popular option is the sqlite3 package from NPM, but your code doesn't follow that API.

The fact that you say "WebKit" is also strange, given that you're apparently on Windows. Do you mean V8 and Node, or are you doing some sort of other out-of-browser thing?

I also don't see any error checking.

The path, c:/My1stdb.db, is clearly to be effective on a Windows machine. On modern versions of Windows, the system drive root directory is write-protected for ordinary (non-administrative) user accounts. If the OP was doing some error checking, such problems would have already become apparent. Perhaps, from this experience, the OP will learn that error checking is a self-favor more than an extraneous bother.

I cannot quibble with your assertion about a dozen or more OS's. However, the odds are high that when c: is given as the leading part of a path, particularly from a novice, that it is intended to be a valid path component on a Windows system. So I accept that I could have more accurately stated my "on Windows" assertion with a qualifier to cover some unlikely possibilities. Whether I should have done that is a separate issue. I decided not to do that because, on most OS's that are not Windows, the root directory is also write-protected for non-administrative users. Hence, the qualification would have added nothing helpful to the OP's probable issue, where he attempts to write, without any error checking, to a directory that is likely to be unwritable for his usual account. I maintain that this decision was logical, even if the post, strictly construed, was not a valid syllogism due to statement of a not-always-true antecedent fact.

Thank you to all who have replied. I am sorry for not responding sooner but being retired has some advantages... I have placed the database in C:/Users/craig/My1stdb.db on a windows 10 system. I am new to SQLite, webkit and all of the other access methods. I apparently don't understand everything that I am trying to do. I have accessed the database thru JS but can't get a transaction to work. I have a breakpoint on the first function statement but that appears to be the failing point. The var db contains the version of the database so I am assuming it has been found and is open. The database contains 1 row and was built and populated using the DB Browser for SQLite tools. The code below is what I am trying to get to work but am at a complete loss. Any help would be greatly appreciated.

var dbSize = 2 * 1024 * 1024; // 2MB
var db = openDatabase("c:/users/craig/db/Medical.db","1.0","Medical Database", dbSize);

db.transaction(
    function a(tx){
        tx.executeSql('SELECT * FROM FAMILY', [],
            function printResults(tx, results){
               var len = results.rows.length; 
               msg = "<p>Found rows: " + len + "</p>";
               alert(msg);
            }
        );
    }
);

I am not sure if this is the problem, but if you are running this as JavaScript in a browser, you are likely running into the issue that the browser puts the page in a sandbox that limits its access to your computer, and that may be blocking you. After all, you wouldn't want pages you visit on the web to have random access to files on your computer.

JavaScript typically has no access to the local file system, when run in a browser. How are you running this javscript?

I have accessed the database thru JS but can't get a transaction to work.

I tried to drive my car to the bank. It didn't work. Please guess which of the following is the problem:

(a) I do not own a car
(b) The car had no gas
(c) The car had no wheels
(d) The car had no engine
(e) The car had no transmission
(f) The car was at the bottom of a ditch
(g) The Bank was Closed
(h) The road was Closed
(i) One of the Above
(j) All of the above
(k) None of the above
(l) Some of the above

So let's play 20 questions:

The var db contains the version of the database so I am assuming it has been found and is open.

How do you know this? Have you validated this fact or is it merely a wishful assumption? What is the procedure you used to validate this assumption and how did that turn out?

I have a breakpoint on the first function statement but that appears to be the failing point.

What is the error message that causes you to believe this is the failure point?

Verified by changing the version number and the database didn't open. I am using WebKit since I am using Chrome and Safari. I will not run in Firefox. I have identified most of my problems and fixed them, they were with my code. I can add records and select the records and print them out, but the database in the code path has more data in than what I am adding. I have verified this by using DB Browser.
I am now trying to insert and print three columns in each row. I think I have the table created correctly but not sure, I am still working on that. I am also trying to get the error messages to print correctly. The error code that I am getting with the below code is 5. the Relationship column does not exist. The columns 1 and 2 are added and get populated. See code below: db.transaction( function a(tx) { tx.executeSql('create table IF NOT EXISTS Family (id unique, Name text, Relationship text, Age integer)'); tx.executeSql('INSERT INTO Family (id,Name,Relationship,Age) VALUES(1,"ABCD","Husband",00)'); tx.executeSql('INSERT INTO Family (id,Name,Relationship,Age) VALUES(2,"EFGHi","Wife",01)'); alert('Table created and rows inserted'); } ); db.transaction( function b(tx){ tx.executeSql('SELECT id,Name,Relationship,Age FROM Family', [], function resultCallback(tx,results){ var len = results.rows.length; alert('Found rows: ' + len); for (i=0;i<len;i++) { alert(results.rows.item(i)); } }, function errorCallBack(tx,results) { alert('Error Encountered: '+results); }

I am new to SQLite, webkit and all of the other access methods. I apparently don't understand everything that I am trying to do. I have accessed the database thru JS but can't get a transaction to work.

Firstly, it's perfectly OK being a novice, that's how we all start, but you are making one very typical rookie mistake: Thinking that the rest of us know what's going on.

With regards to your project, we are as novice as you, we have no clue. We rely on your very full description of every little detail to try and grasp it.

If we have full information, helping you becomes trivial and pleasurable. If we have to shake the box like a christmas gift to try and guess what is inside, it ends in frustration usually.

Further to this, it's almost never SQLite that is broken, so to try and help, us SQLiters have to start a guessing procedure about the target platform. You can find tons of discussions on the forum of the form:

"Hi Guys, my SQLite doesn't work because when I ask it for x y and z, I get nothing/badness."
"Really? what platform do you use? How do you ask it?"
"I use Programming Language PL-X. Here's some code:".

  "  G#^&*("SELECT a,b,c FROM x");"  
  "  *#^*&&*%^#("Slightly legible text");"  

" See? it doesn't do the thing."
"Ok, but what OS, architecture etc...?"
"It runs on OS Dingdong on a Jimbo-Pro PC."
and (from some reader who happens to know PL-X):
"what does it do when you run *#&%#("ver"); ?"
"It says: $@%^$#8=1!"
"Ok, have you tried doing a, b or c?"
...long silence...
"Oops, PL-X on Dingdong works different than I thought - Soz for the noise."

Which leaves us with a sort of frustrated emptiness... We've invested in this user's problem, and got nothing back. (Nor does the next PL-X programmer who comes along find any use in that thread).

Please say what you are doing, what you are trying to do, what you expect to see vs. what you are seeing, on what, in what, for what and what have you all tried before getting stuck - and for the love of Jebbediah, if you get an error, quote the full error message and error code/number.

I know it's impolite to write a wall of text for mass consumption on a twitter, for instance, but in a technical forum it's essential to be thorough, and most people are happy to skip over the bits they don't need to read, but when they start forming an opinion of the problem, it's helpful to be able to go back and read it fully to check. Even this post from me is too long for most to read, so they'll skip over most of it, but somewhere someone will read it all and might find it helpful - I'm hoping that's the OP in this case.

</2c>

---

Enough of that, on to the actual problem...

Above and beyond the mentioned Java ring-fencing of data, Windows itself also has virtualization weirdness that may or may not be turned on, so when you say "it should go into 'c:\users\craig\db\medical.db" - how sure are you about that? - Specifically, how sure are you it's not a copy of the file? Have you tried making a random new non-existing DB (from the JS app) and see if that file gets put there?
[PS: That is not a folder that typically would be virtualized, but the checking is no less important]

When you say it fails on the "first" statement....
You have only 1 statement "db.transaction(...)" - hopefully it fails in there, and if it does, some debuggers might put the failure indicator line somewhere that it thinks the problem happens, which may make it seem like a different thing is the problem.

Does selecting from the DB (sans transaction) work as expected? (producing good verifiable data?)

Ryan, You are correct. That is a rookie mistake so let me start over... First, I am running Windows 10 using Chrome and Safari. I have manually created the database on the c:/users/Craig/db directory using the DB Browser utility and populated two rows in two of the three tables. Let me diverge into the previous two posts for a minute to correct some errors in the code. First, on one line in the transaction, I didn't capitalize the S in tx.executeSql. This apparently is an error. The second problem was that I was enclosing the SQL statement with single quotes and the text in double quotes. I reversed them and all transactions worked. If I don't insert data with the program, I get nothing when I try to select the data, therefore, I don't believe that the database opens. Next, I have tried to create a new database on a different drive and directory, p:/db/My1stDB.db. As I run thru my program, I can't update tables because they are non existent which leads me to believe that the database is not being created nor are the tables. So right now, it appears that I can't create a database nor can I read the one on the disk that I created with manually with DB Browser. Any suggestions/comments would be greatly appreciated. And thank you for the wake-up call. I should know better.

Firstly, let me say thank-you - This is a perfectly stated question.

About the problem: What you describe is "impossible in a correctly functioning system". Which means either you are not telling the Truth, or the system is not functioning correctly. We do however believe you - which leaves one possibility: The system is not working as expected.

I believe c:/users/Craig/db should be good for app-related data (i.e. writable without superuser access) and should not get touched by the Windows UAC virtualization, but we will double-check.

This is the list of things I would suggest to check:

• Make the tables in slite3.exe (or DB Browser will do) in a common folder - "My Documents" will do -
  • See if you can SELECT the values from your program.
  • and if not, try from another program.
• If it works, see if it now works from your given problem folder,
• If it doesn't work, see if the file is read-only (Right-click --> Properties will do)
• Then, delete the file, run your program again, see if it makes a new file, and:
  • See if the new file gets created in the same place and with the same name,
  • If it does, see if you can open it and
  • SELECT from it using another tool

If my understanding of your question statements is relatively close, some of the above should fail and some not, which exactly fail and which errors they give when failing, will tell us a lot more about the problem.

Let us know.
Again, thank you for being clear, please keep it up!

I am running Windows 10 using...Safari.

Safari for Windows was discontinued 8 years ago. You will get no support on it from Apple, and it's unlikely you'll get anyone else interested in helping you with it today.

There are at least three different competing web browser DB APIs, none of which are supported in Safari 5 for Windows.

If you limit yourself to mass-market browsers (e.g. in-support versions of Chrome, Firefox, iOS/macOS Safari, and Edge) there are DB APIs that work "everywhere."

tx.executeSql

Okay, then, that's the unofficial Web SQL Database API, which isn't supported in IE, Firefox, original Edge, or current Safari. Pretty much the only ones supporting it are Google Chrome and the new Chrome-based versions of Edge.

As well, the standards body that tried to promulgate this API now warns you off of trying to use it.

I reversed them and all transactions worked.

The SQLite quoting rules are rather strict in current versions, by default. I assume the web browsers aren't enabling the feature that allows old-style loose quoting rules, so you shouldn't count on being able to do that even if you can get it to work in certain builds of SQLite.

it appears that I can't create a database nor can I read the one on the disk that I created with manually with DB Browser

Having never used this nonstandard DB API, I must simply assume that the browser sandboxes access to the filesystem to prevent web pages from reading arbitrary SQLite DBs on the filesystem, based on established standards of browser behavior. It doesn't surprise me in the least that you can't open files with this API using arbitrary paths.

Rather than continue wasting time trying to figure out what its rules are and working out accommodations for those rules, I think you should abandon use of that particular API and switch to one that is currently supported. You'll get a lot more help that way, both from interactive forums like this one as well as from the documentation and tooling surrounding it.

I am kind of facing a similar problem. I am using sequelize here and trying to give a storage path to sqlite db in the config.js file. Using Windows require('.env').config();

module.exports = { development: { dialect: "sqlite", storage: ".database.sqlite3" }, test: { dialect: "sqlite", storage: ":memory" }, production: { url: process.env.DATABASE_URL, dialect: '.database.sqlite3', }, };

Not able to find out the file which is getting created. Used the command *.db / from my root directory which is C.

2)I am also assuming that even if I have been able to create the transactions and migrations they are not getting stored until I give a storage path. They all get created "in memory" which we are not able to access.

You tell it in your command that you want to create a database in memory, not on disk. That's what storage: ":memory" means. If you want a disk file, specify a disk file instead.