Query raw run throws the following error ```incorrect binary data format in bind parameter 3```

See original GitHub issue

Bug description

I’m trying to run a raw query casting the types of some attributes but getting the error describe at the title. I have run the query at the database console and it is working.

How to reproduce

const deliveryPriceCalculated =
      await prisma.$queryRaw<ZipCodeRange>`SELECT * FROM zip_ranges_brazil WHERE
      weight = ${weight}
      AND number_service = ${number_service}
      AND (${zip_origin})::INTEGER BETWEEN zip_origin_start::INTEGER AND zip_origin_end::INTEGER
      AND (${zip_destination})::INTEGER BETWEEN zip_destination_start::INTEGER AND zip_destination_end::INTEGER`

Expected behavior

Run the query and give me the expected data.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model ZipCodeRange {
  id                    String   @id @default(uuid())
  number_service        String   @db.VarChar(20)
  region                String   @db.VarChar(100)
  delivery_time         Int
  weight                Float
  value                 Float
  zip_origin_start      String   @db.VarChar(50)
  zip_origin_end        String   @db.VarChar(50)
  zip_destination_start String   @db.VarChar(50)
  zip_destination_end   String   @db.VarChar(50)
  zip_origin_ref        String   @db.VarChar(50)
  zip_destination_ref   String   @db.VarChar(50)
  created_at            DateTime @default(now())
  updated_at            DateTime @default(now())

  @@map("zip_ranges_brazil")
}

Environment & setup

  • OS: Ubuntu 20
  • Database: Postgres
  • Node.js version: 15.4.0

Prisma Version

2.22.1

Issue Analytics

  • State:closed
  • Created 2 years ago
  • Reactions:2
  • Comments:15 (6 by maintainers)

github_iconTop GitHub Comments

5reactions
Weakkycommented, May 5, 2022

Hey,

This issue was fixed by https://github.com/prisma/prisma-engines/pull/2847. It will be available in the next release under the improvedQueryRaw feature flag.

Beware that enabling improvedQueryRaw is a breaking change. Release notes will contain information as to how to upgrade (so will the documentation).

Thanks for reporting 🙏

4reactions
celbercommented, Jul 8, 2021

Here you got the very exact query i run:

SELECT
         id, name, data, lat, lng, city, "categoryId", "userId",
         (select name from hekete."User" where id="userId") as "userName"
 FROM xxx."Advertisement"
 WHERE
         ST_DistanceSphere(ST_SetSRID(ST_MakePoint(17.03533,51.10773),4326), ST_SetSRID(ST_MakePoint(lng,lat),4326)) / 1000 < 25
         AND
         1=1
 LIMIT 50
 OFFSET 0;

Works OK with pg adapter.

Read more comments on GitHub >

github_iconTop Results From Across the Web

"incorrect binary data format in bind parameter" while ... - ERROR
While running a workflow with Oracle as the source and Postgre as target, data is not inserted into the target. The following error...
Read more >
How to handle conditional prepared statements using prisma ...
... I got the following error: Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`....
Read more >
type-graphql/prisma2 - Gitter
When i run a graphql query, i'm getting an error message of "incorrect binary data format in bind parameter 3", anyone have any...
Read more >
Prisma 3.14.0 Release - GitClear
queryRaw`SELECT ${1.5}::int as int`; // Before: db error: ERROR: incorrect binary data format in bind parameter 1 // After: [{ int: 2 }]....
Read more >
Prisma Postgres $queryRaw butchering numeric types - Reddit
... Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1` const [{ id }] =...
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found