Re: How to excute dynamically a generated SQL command?

At 10:20 AM 1/4/2009, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Message-ID:
><618950b80901031757l15109658kdae1cdb0814d33fc(at)mail(dot)gmail(dot)com>
>Date: Sat, 3 Jan 2009 17:57:32 -0800
>From: "John Zhang" <johnzhang06(at)gmail(dot)com>
>To: postgis-users(at)postgis(dot)refractions(dot)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:\\\\HM\\\\Data\\\\Flightmap.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