Main Site Documentation

SQLite Hanging


#1

Hi ,

I have a problem with SQLite as I try to create a trigger .

I created the SQL commands using the SQLiteman - 1.2.2 :

http://sqliteman.com/page/2.html

Which uses SQLite 3

The SQL is as follows :

[em]create table if not exists
Photo
(
id INTEGER PRIMARY KEY ASC AUTOINCREMENT
, Name VARCHAR ( 255 )
, Image BLOB
, Date_insert DATETIME
);

CREATE UNIQUE INDEX idx_photo
ON Photo (id COLLATE NOCASE ) ;

CREATE TRIGGER AFTER INSERT trg_a_insert
ON Photo
BEGIN
Photo date_insert UPDATE SET = datetime ( 'now ’ ) WHERE id = new.id ;
END;[/em]

To test the SQLiteman - [em]INSERT INTO Photo (name) VALUES ( ‘Test’ ) ;[/em]

All these commands work perfectly when run directly on SQLiteman .

However , when I run the commands to create the structure through C # using GHI.Premium.SQLite , it runs normally without showing any error .

But when I try to run even simple insert command , it hangs .

I was testing the possibilities that could cause this error and I noticed that when I do not create the trigger , everything works normally , but if the table has the trigger then the insert command hangs .

This is the source code :


private void SetupDataBase(string baseDir, string dataBaseName)
{
	try
	{
		_dbNETMF = new Database();
		_dbNETMF.Open("\\" + baseDir + "\\" + dataBaseName); // Open or Create new database file

		_dbNETMF.ExecuteNonQuery(@ "drop table if exists Photo");

		_dbNETMF.ExecuteNonQuery(@ "create table if not exists 
										Photo 
										(id INTEGER PRIMARY KEY ASC AUTOINCREMENT
										, name VARCHAR(255)
										, image BLOB
										, date_insert DATETIME)");

		_dbNETMF.ExecuteNonQuery(@ "CREATE UNIQUE INDEX idx_photo 
									ON Photo ( id COLLATE NOCASE )");

		_dbNETMF.ExecuteNonQuery(@ "CREATE TRIGGER trg_a_insert AFTER INSERT 
									ON Photo
									BEGIN
									  UPDATE Photo SET date_insert = datetime('now') WHERE id = new.id;
									END");

		// Hangs here
		_dbNETMF.ExecuteNonQuery("INSERT INTO Photo (name) VALUES ('Test')");

		SQLiteDataTable result = _dbNETMF.ExecuteQuery("SELECT * FROM Photo");

		_dbNETMF.Close();
		_dbNETMF = null;
	}
	catch (Exception e)
	{
		Debug.Print(e.Message);
		Debug.Print(Database.GetLastError());
	}
}

Does anyone know the reason that can cause this?

Regards,
Andrew Paes


#2

Only INTEGER, DOUBLE and TEXT datatypes are supported in the GHI’s implementation.


#3

Hi,

I was trying to save a file as a byte array into a blob.

With what you told me so I’m not doing this.

Have a way to work around to this problem?

There is a forecast of the other data types to be implemented?

Thank you,
Andrew Paes


#4

For VARCHAR and DATETIME you can probably go around with TEXT and a couple of INTEGER fields. BLOB is problematic. For small images you can do Base64 and store them as TEXT for example.


#5

Hi,

I figured it had to be that way. So I had already taken a look at this possibility and found this:

Could you confirm that this works well in Fez Spider?


#6

I don’t see why it shouldn’t. There is also a version on codeshare:

https://www.ghielectronics.com/community/codeshare/entry/331


#7

There is a forecast of the other data types to be implemented?