Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL LIKE operation doesn't recognize variable #437

Open
2 tasks done
sachinshahredmane opened this issue Jan 25, 2024 · 2 comments
Open
2 tasks done

SQL LIKE operation doesn't recognize variable #437

sachinshahredmane opened this issue Jan 25, 2024 · 2 comments
Assignees

Comments

@sachinshahredmane
Copy link

sachinshahredmane commented Jan 25, 2024

Issue Summary

The following SQL in SqlCommandProvider doesn't recognize @logTblName as a substitution variable.

First, here is the raw SQL that works in SSMS:

SELECT *
FROM MyDataTable dl
WHERE dl.Options like '%"STATE\%';

The values that we are looking at look like serialized JSON.

To Reproduce

Create a simple table with at least one column Options set to nvarchar(max). Set values exactly like follows in this field (including the backslashes, quotes, etc.):

[\"COUNTY\"],[\"STATE\"],[\"COUNTRY\"]

Code:

let cmd = 
   new SqlCommandProvider<"
      SELECT *
      FROM MyDataTable dl
      WHERE dl.Options like '%\"@logTblName\\%';
   ",connectionString>(connectionString)

cmd.Execute(logTblName="STATE")

Error

Error message:

error FS0495: The member or object constructor 'Execute' has no argument or settable return property 'logTblName'. The required signature is SqlCommandProvider<...>.Execute() : Collections.Generic.IEnumerable<SqlCommandProvider<...>.Record>.

Expected behavior

It should recognize @logTblName as a parameter.

What you can do

  • I am willing to contribute a PR with a unit test showcasing the issue***
  • I am willing to test the bug fix before next release

The *** is because I don't know enough to fix this, but may be able to do so with (a lot of) guidance.

EDIT: Fixed F#.

@smoothdeveloper
Copy link
Collaborator

@sachinshahredmane, this isn't achievable, the SQL parser itself wouldn't accept the statement.

What you can do, is have like @logTblName in your statement, and put the patterns (and escape %) in the parameter itself; I think this would fit your need, but please let us know.

@sachinshahredmane
Copy link
Author

Ah I see. Will try and get back to you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants