On-database filesystem unsupported on MS SQL Server?

[stegl83]

I tried to use the table "sqlpage_files" for storing the content in the DB in spite of localfilesystem. But creating and filling the table does not have an effect.

Enabled the debug-Log, then I got this:

DEBUG sqlpage::filesystem] Initializing database filesystem queries
DEBUG sqlpage::filesystem] Using local filesystem only, could not initialize on-database filesystem. You can host sql files directly in your database by creating the following table: CREATE TABLE sqlpage_files(path VARCHAR(255) NOT NULL PRIMARY KEY, contents TEXT, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

followed by:

The error while trying to use the database file system is: error returned from database: Falsche Syntax in der Nähe von ">".: Falsche Syntax in der Nähe von ">".

The next thing I did: Run SQL Server Query Analyzer to get the SQL-Statement that SQLPage send to the SQL-server:

declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@p1 nvarchar(1),@p2 nvarchar(1)',N'SELECT last_modified >= @p1 from sqlpage_files WHERE path = @p2 LIMIT 1',1
select @p1

Seems, that SQLPage generates no mssql compatible query here.

• OS: Win10
• Database MS SQL (v16.0.1121.4)
• SQLPage Version 30.1

[lovasoa]

Thank you for reporting this ! We'll implement mssql-specific database filesystem queries for the next version.

What are you building, by the way ?

[lovasoa]

The problem is fixed, and the on-db filesystem feature is now automatically tested on all supported databases :)

You can try the latest build with the fix on docker: lovasoa/sqlpage:main

[stegl83]

Very nice. Thank You very much!

I had a little trouble with the text encodings. But finally, I get it working. The contents-Column is varbinary and SqlPage needs UTF8 encoding here. NVarchar is UTF16, varchar depends on database collation, which i didn't want to change.

In my case, the table "sqlpage_files" is filled by my application. So I encoded the content on application side to a UTF8-encoded Byte Array and use a varbinary parameter to insert the row.