How to pass SELECT DB Pervasive parameter

Question

I’m having to work with Pervasive database, so I’m having trouble passing a parameter to perform a SELECT

I’ve tried using:

DBCmd.Parameters.Add("@inicio", PsqlDbType.VarChar).Value = date_inicio;
DBCmd.Parameters.Add("@fim", PsqlDbType.VarChar).Value = date_fim;

And I tried that too:

DBCmd.Parameters.AddWithValue("@inicio", "'" + date_inicio + "'");
DBCmd.Parameters.AddWithValue("@fim", "'" + date_fim + "'");

I know that by Pervasive’s default, the dates have to be in the format AAAA-MM-DD.

That is to say, date_inicio = 2016-01-01 and date_fim = 2016-12-31

Below is the query:

SELECT a.code_client AS CODIGO, a.raison_sociale AS 'RAZÃO SOCIAL', a.Adresse_1 AS ENDEREÇO, a.Ville AS CIDADE, e.etat AS 'ESTADO', a.code_postal_siege AS CEP, a.Adresse_2 AS BAIRRO, a.Specialite_client AS SEGMENTO, 
                                CASE a.Code_representant 
                                WHEN 0 THEN 'BRAYTON'
                                WHEN 30225 THEN 'DOUGLAS'
                                WHEN 30327 THEN 'FURLAN'
                                WHEN 30431 THEN 'ALLAN'
                                WHEN 30318 THEN 'RUFINO'
                                WHEN 30140 THEN 'GURIAN'
                                END AS 'REPRESENTANTE',
                                SUM(c.montant_ht) AS 'PREÇO PROD',
                                SUM(c.montant_ht - d.vl_icms) AS 'PREÇO LIQ',
                                SUM(d.vl_ipi) AS 'VALOR IPI',
                                SUM(d.vl_icms) AS 'VALOR ICMS',
                                SUM(d.vl_pis) AS 'VALOR PIS',
                                SUM(d.vl_cofins) AS 'VALOR COFINS'
                                FROM client AS a
                                LEFT JOIN  operateur AS b
                                ON a.Code_representant = b.code
                                LEFT JOIN commande AS c
                                ON a.code_client = c.code_cl
                                LEFT JOIN BRAYTONAUX.PedidoVendaAUX AS d
                                ON c.affaire = d.cd_pedido
                                LEFT JOIN client2 AS e
                                ON a.code_client = e.code
                                WHERE c.date_creat BETWEEN CONVERT(@inicio, SQL_DATE) AND CONVERT(@fim, SQL_DATE)
                                GROUP BY a.code_client, a.raison_sociale, a.Adresse_1, a.Ville, e.etat, a.code_postal_siege, a.Adresse_2, a.Specialite_client, a.Code_representant

Answer 1

By default Pervasive uses the symbol ? to identify the parameter, instead of @ in the query.

So that’s how it was:

SELECT a.code_client AS CODIGO, a.raison_sociale AS 'RAZÃO SOCIAL', a.Adresse_1 AS ENDEREÇO, a.Ville AS CIDADE, e.etat AS 'ESTADO', a.code_postal_siege AS CEP, a.Adresse_2 AS BAIRRO, a.Specialite_client AS SEGMENTO, 
                            CASE a.Code_representant 
                            WHEN 0 THEN 'BRAYTON'
                            WHEN 30225 THEN 'DOUGLAS'
                            WHEN 30327 THEN 'FURLAN'
                            WHEN 30431 THEN 'ALLAN'
                            WHEN 30318 THEN 'RUFINO'
                            WHEN 30140 THEN 'GURIAN'
                            END AS 'REPRESENTANTE',
                            SUM(c.montant_ht) AS 'PREÇO PROD',
                            SUM(c.montant_ht - d.vl_icms) AS 'PREÇO LIQ',
                            SUM(d.vl_ipi) AS 'VALOR IPI',
                            SUM(d.vl_icms) AS 'VALOR ICMS',
                            SUM(d.vl_pis) AS 'VALOR PIS',
                            SUM(d.vl_cofins) AS 'VALOR COFINS'
                            FROM client AS a
                            LEFT JOIN  operateur AS b
                            ON a.Code_representant = b.code
                            LEFT JOIN commande AS c
                            ON a.code_client = c.code_cl
                            LEFT JOIN BRAYTONAUX.PedidoVendaAUX AS d
                            ON c.affaire = d.cd_pedido
                            LEFT JOIN client2 AS e
                            ON a.code_client = e.code
                            WHERE c.date_creat BETWEEN CONVERT(?, SQL_DATE) AND CONVERT(?, SQL_DATE)
                            GROUP BY a.code_client, a.raison_sociale, a.Adresse_1, a.Ville, e.etat, a.code_postal_siege, a.Adresse_2, a.Specialite_client, a.Code_representant

The declaration of parameters being so:

DBCmd.Parameters.Add("@inicio", PsqlDbType.Date).Value = date_inicio;
DBCmd.Parameters.Add("@fim", PsqlDbType.Date).Value = date_fim;