[godot-sqlite] Retrieving table from database is failing on external machines and in HTML5

:information_source: Attention Topic was automatically imported from the old Question2Answer platform.
:bust_in_silhouette: Asked By Beefeater1980

Edit 2: It looks like this is caused by a problem with my file paths. The app works when the .db file exists inside %APPDATA%/Godot/app_userdata/[name of the game], but not when it does not. So the real problem is why files aren’t copying to the right places.

I am using 2Shady4Us godot-sqlite plugin and have encountered an interesting variant of what seems to be quite a common problem. The solutions I have found by searching have got the program running fine when exporting and running on my (Windows) machine. However, it fails on other (Windows) machines, and fails on HTML5 even inside the Godot app.

I have created a CYOA-like game that stores paragraphs and choices in a .db file. The database’s intended functionality is read-only.

On _ready in the main scene, the database is copied from res:// to user://. This step was sufficient to let the program run on my machine, when exported as a Windows .exe.

If I send the files to my testing computer, or if I attempt to run the program in HTML5 inside Godot, the database loads (that is, the console reports that it has successfully opened “user://databasename”), but cannot open the relevant table. The specific error message I am getting is that although the database is opened correctly there is “no such table” as the table I’m requesting in the query. This results in a lookup failure when the code attempts to lookup index[0] (that is, the row resulting from a query). I have also set the database to read-only mode, and included *.db in my export filters.

This is driving me nuts. Anyone have any idea what’s going wrong?

E: I appear to be getting an error code 7 from sqlite if that helps any.

Relevant code snippets below:

# Enable SQLite module and identify path to database
const SQLite = preload("res://addons/godot-sqlite/bin/gdsqlite.gdns")
var db
var db_name = "res://data/databases/lawyergame.db"

# Relevant bits of ready function
func _ready():	
	var data_path = "res://data"
	var copy_path = "user://data"
	
	var dir = Directory.new()
	dir.make_dir(copy_path)
	if dir.open(data_path) == OK:
		dir.list_dir_begin();
		var file_name = dir.get_next()
		while (file_name != ""):
			if dir.current_is_dir():
				pass
			else:
				print("Copying" + file_name + "to  /user-folder")
				dir.copy(data_path + "/" + file_name, copy_path + "/" + file_name)
			file_name = dir.get_next()
	else:
		print("An error occurred while trying to access the path.")
	
	db = SQLite.new()
	db.path = "user://lawyergame.db"
	db.verbose_mode = true
	db.read_only = true


# Relevant bits of function to read database events
db.open_db()
		db.query("SELECT eventtext FROM adventure_events WHERE ID = " + str(event_num))

Hello Beefeater,

You’ll have to check the version of your plugin as older versions (3.0) do not work anymore for HTML5. Please make sure that you download the latest version of the plugin (3.1) from the Godot Asset Lib.

Also I’m a bit confused with your code as the path of your db seems to be wrong?

db.path = "user://lawyergame.db"

Shouldn’t this be changed to:

db.path = "user://data/lawyergame.db"

Please check this and tell me if that fixes the issue.

Getting back error code 7 (SQLITE_NOMEM) is a generic error that is returned in a myriad of cases. (== It’s not useful in most cases)

Piet Bronders | 2022-02-02 01:05

Thank you, Piet - you were absolutely correct that I had stupidly got the wrong path. Doh! The change did mean the file copied over correctly, although only after I had deleted the contents of the User:// folder manually.

Regarding the version of the plugin, I believe it to be the correct one. HTML5 issues are more likely to be coming from something unique to my setup so I’m closing this. Thank you so much!

Beefeater1980 | 2022-02-02 06:41