Invoke-SqlQuery with parameter used multiple times in query

[Ture2019]

My query refers the same parameter in several places. Does not seem to work today. Query return with empty result. Workaround is obviously to replace parameters inbeforehand. But this is quite a basic feature, would be nice to see fixed. :-)

[mithrandyr]

@Ture2019 without an example of what is going wrong (i.e. steps to reproduce) then I cannot provide a fix. Please provide an example, along with the result you are seeing and then the result you would expect to see.

[mithrandyr]

Here is an example of using the parameter in multiple locations in the query -- I'm using the SQLite provider (but it would work with any other providers).

Open-SQLiteConnection #this will open an inmemory database
Invoke-SqlQuery "Select @name AS First, @name AS Second, @age AS Third, @name AS Fourth" -Parameters @{name = "John Doe"; age = 50}
Close-SqlConnection

Output:

First    Second   Third Fourth
-----    ------   ----- ------
John Doe John Doe    50 John Doe

[Ture2019]

Sorry, I can provide a propper example becuase this would involve to set up a test mysql-database. But here is my query:

SELECT * 
FROM Settlement 
WHERE settlementDate >= @dato 
AND settlementDate < date_add(@dato, INTERVAL 1 DAY)

[mithrandyr]

@Ture2019 -- so the full powershell code you would be invoking is something like:

Open-MySqlConnection -Server $someServer -Database $someDatabase

$query = "SELECT * FROM Settlement WHERE settlementData >= @dato AND settlementDate < date_add(@dato, INTERVAL 1 DAY)"

Invoke-SqlQuery -query $query -parameters @{dato = [datetime]"2022-11-14"}

And you are saying that this does not return any results? What I need is for you to provide some kind of sample demonstrating your full code (not just the query) for how you are using the SimplySql module itself (what you provide would look like one of my examples). Thanks.

[mithrandyr]

@Ture2019 -- I've created a script that creates a temporary table, populates it with data, and then runs a query against that table that mimics yours above -- referencing a SQL variable multiple times -- the query does return data.

Open-MySqlConnection -Server $server -UserName $username -Password $password

$tempTableQuery = "CREATE TEMPORARY TABLE test (someId int AUTO_INCREMENT PRIMARY KEY, someValue varchar(50) NULL, someDate datetime NULL)"

Invoke-SqlUpdate -Query $tempTableQuery | Out-Null

$queryInsert = "INSERT INTO test (someValue, someDate) VALUES (@v, @d)"
foreach($i in (0..100)) {
    $params = @{
        v = "Generated Value = $i"
        d = (Get-Date).AddDays(-7).AddHours(3 * $i)
    }
    Invoke-SqlUpdate -Query $queryInsert -Parameters $params | Out-Null
}

$query = "SELECT * FROM test WHERE someDate >= @dato AND somedate <= date_add(@dato, INTERVAL 1 DAY)"
Invoke-SqlQuery -Query $query -Parameters @{dato=(Get-date)}

Invoke-SqlUpdate -Query "DROP TABLE test" | Out-Null
Close-SqlConnection

[mithrandyr]

@Ture2019 -- closing out this issue as I can't reproduce it -- if you can create repro steps, happy to reopen and look into it.