Re: [SQL] How to excute dynamically a generated SQL command?

[Adrian Klaver]

On Saturday 03 January 2009 5:57:32 pm John Zhang wrote:
> Hi the list,
>
> Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing Dynamic
> Commands ", the command for executing a dynamic command is:
> EXECUTE command-string [ INTO [STRICT] target ];
>
>
> I am to execute an sql statement created dynamically, which is represented
> in a variable sSql.
> Here is an example:
> sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
> ('.tif',  lo_import( E''C:\\HM\\Data\\Flightmap.tif'');'

You are missing a ')'  Should be
VALUES ('.tif',  lo_import( E''C:\\HM\\Data\\Flightmap.tif''));';

> EXECUTE sSQL;
>
> It raises the error as:
> ERROR:  syntax error at end of input
> LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
>   ^
>
> I would appreciate a lot if you offer your input. Thanks a lot.
>
> John



-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to excute dynamically a generated SQL command?

[Steve Midgley]

At 10:20 AM 1/4/2009, pgsql-sql-ow...@postgresql.org wrote:
Message-ID: 
<618950b80901031757l15109658kdae1cdb0814d3...@mail.gmail.com>

Date: Sat, 3 Jan 2009 17:57:32 -0800
From: "John Zhang" 
To: postgis-us...@postgis.refractions.net
Subject: How to excute dynamically a generated SQL command?
X-Archive-Number: 200901/2
X-Sequence-Number: 32084

Hi the list,

Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing 
Dynamic Commands ", the command for executing a dynamic command is:

EXECUTE command-string [ INTO [STRICT] target ];


I am to execute an sql statement created dynamically, which is 
represented in a variable sSql.

Here is an example:
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES 
('.tif',  lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';

EXECUTE sSQL;

It raises the error as:
ERROR:  syntax error at end of input
LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
  ^

I would appreciate a lot if you offer your input. Thanks a lot.

John



John: You're not escaping all your strings. That error message is a 
tip-off, I think. Try this line:


sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES 
(''.tif'',  lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';


The part I changed was: ''.tif''

I'm not sure what language you're working in, but it's remotely 
possibly (depending on the execution stack) that you have to doubly 
escape your backslashes also, in which case:


sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES 
(''.tif'',  lo_import( E''C:HMDataFlightmap.tif'');';


I suffer on Windows wishing we could have "/" path separators by 
default. Note that these days Windows generally does support "/" 
instead of "\" for paths if you're careful. If you put them in quotes, 
it works even on the command line, which is helpful. You can type this 
directly into the CMD prompt now:


dir "c:/temp"

All new programs I write on Windows (in Ruby) use forward slashes for 
paths, and it works just fine. Not sure about VB or C#, but I'd guess 
you can make it work. Might be simpler than all the escaping work..


Best,

Steve


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql