Invoke-SqliteQuery Fails when there is Rank() used in views

[manikandancr]

Invoke-SqliteQuery is failing if we have used Rank(), or in fact any analytic function in view queries. Below is the error that I am getting.

"database disk image is malformed malformed database schema (UserCommentsSummary) - near "(": syntax error" At line:24 char:16"

[bgwdotdev]

Hi @manikandancr

Would you be able to provide further details or a method of recreating this issue? I have tried with the following code and did not experience problems but I may be misunderstanding your problem.

$d = @{datasource = './test.db'}
Invoke-SqliteQuery @d -Query "create table rankdemo ( val text )"
Invoke-SqliteQuery @d -Query "Insert into rankdemo(val) values('a'),('b'),('c')"
Invoke-SqliteQuery @d -Query "create view rankview as select val, rank() over ( order by val ) valrank from rankdemo"
Invoke-SqliteQuery @d -Query "select * from rankview"

val valrank
--- -------
a         1
b         2
c         3

[manikandancr]

Hi @fffnite

The above script is failing for me at create view session. Please find the screenshot below,

[bgwdotdev]

Ah, I see the problem now. I only updated PS Core to the new sqlite .dll so when using PS5 you are loading the old binaries which, I assume, don't support the rank function.

I will write a patch for this now though it may take a little bit to get merged into master as RCM is a busy individual.

@manikandancr You can either pull my repo once I reference the patch or if you can, you can use PS Core instead of PS5

[manikandancr]

Hi @fffnite ,

The issue seems to resolved after using the modified .psm1 script. But still facing an issue in another machine which is running windows server 2012 R2 with the below powershell version .