How to Pass Parameters to Query

How to pass parameters to query?

There are two syntax expressions for parameter referencing in JasperReports: $P{} and $P!{}.

• $P{paramName} syntax is using mostly for setting WHERE input parameters values. The replacement algorithm is "smart", its implementation uses java.sql.PreparedStatement: for java.lang.String parameter the engine will replace $P{parameterName} with quoted value, for java.lang.Integer - with numeric value and so on.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|-------------------|:---------------:|
|   eventName    | java.lang.String  |  Olympic Games  |
|   eventType    | java.lang.Integer |       2         |

Original expression (to replace):

SELECT startDate, endDate, rating FROM events WHERE name=$P{eventName} AND type=$P{eventType} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND type=2 

• $P!{paramName} syntax is using mostly for doing the "simple" replace.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|------------------:|:---------------:|
|   tableName    | java.lang.String  |     events      |
|   eventName    | java.lang.String  |  Olympic Games  |
|   channel      | java.lang.String  |     'BBC'       |
|   type         | java.lang.String  |     sport       |

Original expression (to replace):

SELECT startDate, endDate, rating FROM $P!{tableName} WHERE name='$P!{eventName}' AND channel=$P!{channel} AND type=$P!{type} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND channel='BBC' AND type=sport

---

For more information you can read this Using report parameters post and look at this Query sample.

---

In your case the right expression may be like this:

SELECT name, phone, email FROM company WHERE $P!{clause} = $P{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

or like this (it depends on $P!{clause} value)

SELECT name, phone, email FROM company WHERE $P!{clause} = $P!{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

What is the correct way to pass parameters to a React-query useQuery method that uses Axios

The query function that you pass to react-query gets a queryContext injected, which is an object that consists of the queryKey (and some more information if you are using an infinite query). So yes, one correct way to access dependencies is through the queryKey:

export const getProduct = async ({ queryKey }) => {
    const [_, prodId] = queryKey
    const { data } = await axios.get(`/api/v1/products/${prodId}`)
    return data
}
const { data } = useQuery(['product', prodId], getProduct)

Another way is to use inline anonymous functions, which is well documented in the docs in: If your query function depends on a variable, include it in your query key

export const getProduct = async (prodId) => {
    const { data } = await axios.get(`/api/v1/products/${prodId}`)
    return data
}
const { data } = useQuery(['product', prodId], () => getProduct(prodId))

Pass parameter in the select of a query

Your problem is that the select method unlike the where method expects a plain string as its argument, it doesn't allow ? and :variable placeholders.

To use expressions like :rutaId you need to wrap the select query with a call to sanitize_sql_array.

Assuming that reporte_inventario_total is a method on some ActiveRecord::Base subclass:

query_string = "SELECT Ruta FROM Rutas WHERE IdRutas = :rutaId or :rutaId ..."
query = select(sanitize_sql_array([query_string, rutaId: params[:search], fechaDiaO: params[:fechaDiaO]))

If reporte_inventario_total is a written outside of some ActiveRecord::Base subclass then you need to replace sanitize_sql_array(...) with something like ActiveRecord::Base.send(:sanitize_sql_array, ...), because that method is private and is intended to be used in models only.

How to pass parameters in a native query JPA

There are two ways around that when you use Spring Data JPA

1) Named Parameters

public interface CarRepository extends JpaRepository<TRace, String> {
        @Query(nativeQuery = true,
               value = "select *" +
                       "from car_records" +
                        "where carVinNo = :vinNo and carSerialNo >= :serialNo")
     }
     List<Car> retrieveCars(@Param("vinNo") Long vinNo,@Param("serialNo") Long serialNo); 
    }

spring doc for named parameters

2) Indexed Parameters

public interface CarRepository extends JpaRepository<TRace, String> {
    @Query(nativeQuery = true,
           value = "select *" +
                   "from car_records" +
                    "where carVinNo = ?1 and carSerialNo >= ?2")
 }
 List<Car> retrieveCars(Long vinNo, Long serialNo); 
}

example for index parameter from spring doc

Then from your service class you call it

carRepository.retrieveCars(vinNo, serialNo);

Both cases will work the same for you.

Passing parameters from Python to SQL Server

If I remember correctly, the placeholder is %s and not ?.

Regardless, you can use the format method / string formatting to get the job done:

conn = pyodbc.connect(<Connection Details>)
c = conn.cursor()
employee_id=(100,101)

query = "select * from employees where employee_id in {}"
c.execute(query.format(employee_id))

Pass parameter from Excel to SQL in PowerQuery

You can reference a table on a sheet from Power Query and integrate values from that table into your other queries. Eg if ParameterTable is a single-row table on some worksheet with a column called "StartDate", something like

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    Source = Sql.Databases("localhost"),
    AdventureWorksDW2017 = Source{[Name="AdventureWorksDW2017"]}[Data],
    dbo_DimDate = AdventureWorksDW2017{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] = theDate )    
in
    #"Filtered Rows"

for M query folding, or

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    sql = "
      select *
      from dimDate
      where FullDateAlternateKey = '" & Text.From(theDate) & "'
    ",
    Source = Sql.Database("localhost", "adventureworksdw2017", [Query=sql])
in
    Source

for dynamic SQL.

How to pass parameters to a query on the Dao from the Repository?

it seems like the list is not static and changes by the selection, so I would change the Repository like this:

    private val allWordsByDatesMLD = MutableLiveData<List<Word>>()
    val allWordsByDates: LiveData<List<Word>> = allWordsByDatesMLD

    @WorkerThread
    suspend fun WordsByDates(from_date: Long, to_date: Long) {
        allWordsByDatesMLD.postValue(wordDao.findTransactionsBetweenDates(from_date, to_date))
    }

to work with Dao like this

    @Query("SELECT * FROM word_table WHERE transaction_date BETWEEN :from_date AND :to_date")
   suspend fun findTransactionsBetweenDates(from_date: Long, to_date: Long): List<Word>

---

Related Topics