Skip site navigation (1) Skip section navigation (2)

Re: Search content within a bytea field

From: dmp <danap(at)ttc-cmc(dot)net>
To: Damiano Bolzoni <damiano(dot)bolzoni(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Search content within a bytea field
Date: 2009-08-31 13:39:19
Message-ID: 4A9BD287.9080000@ttc-cmc.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
>
>
>Hi all,
>I'm trying to search for some content within a bytea field. My
>PreparedStatement looks like this:
>
>SELECT id FROM mytable WHERE myfield LIKE ?
>
>Now, when I execute a
>
>ps.setString(1, "%" + value + "%")
>
>where 'value' is the value I want to search for, the PS becomes
>
>SELECT id FROM mytable WHERE myfield LIKE %VALUE%
>
>and the query clearly fails ("operator does not exist: bytea ~~
>character varying"). Now, my question is....why? What am I doing
>wrong?
>
>Thanks for you help!
>

The problem I believe is in your prepare statement ps.setString(). The 
statement
using a string, but your field is bytea, try a cast.

>SELECT myfield FROM mytable WHERE myfield LIKE '%hello%'
>
>and 'myfield' is a bytea field I get the result I expected? What's the
>"trick" the client uses behind the curtains?
>

Of course this work, example given below, because you are not using a 
prepare
statement. PostgreSQL expects the exact type to be used in prepare 
statements.
These fields can be searched, but if the content is binary I don't think 
this general
approach is going to work. The content below for the dump is octal I 
believe, but
does contain the text "find" in the id = 2. The select statement for 
search in
MyJSQLView also shown, that does successfully find the row.

danap.

--
-- MyJSQLView SQL Dump
-- Version: 3.03
-- WebSite: http://myjsqlview.org
--
-- Host: 127.0.0.1
-- Generated On: 2009.08.31 AD at 07:20:14 MDT
-- SQL version: PostgreSQL 8.3.3
-- Database: postgresql_datatypes
--

-- ------------------------------------------

--
-- Table structure for table "public"."bytea_types"
--

DROP TABLE IF EXISTS "public"."bytea_types";
CREATE TABLE "public"."bytea_types" (
    "data_type_id" serial NOT NULL,
    "bytea_type" bytea DEFAULT NULL,
    PRIMARY KEY ("data_type_id")
);

--
-- Dumping data for table "public"."bytea_types"
--

LOCK TABLE "public"."bytea_types";
INSERT INTO "public"."bytea_types" ("data_type_id", "bytea_type") 
VALUES('1', E'\\054\\054\\041\\041\\041\\043\\043');
INSERT INTO "public"."bytea_types" ("data_type_id", "bytea_type") 
VALUES('2', 
E'\\152\\165\\163\\164\\040\\163\\157\\155\\145\\040\\155\\157\\162\\145\\040\\146\\151\\156\\144\\040\\150\\145\\154\\154\\157');

TableTabPanel_PostgreSQL actionPerformed() Connection Created
SELECT "data_type_id" FROM "public"."bytea_types" WHERE 
"bytea_type"::TEXT LIKE '%find%' ORDER BY "data_type_id" ASC LIMIT 50 
OFFSET 0
TableTabPanel_PostgreSQL actionPerformed() Connection Closed


In response to

pgsql-jdbc by date

Next:From: Tom LaneDate: 2009-08-31 14:11:11
Subject: Re: Search content within a bytea field
Previous:From: Sylvain LerouxDate: 2009-08-31 07:38:49
Subject: Re: Search content within a bytea field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group