Hi there, thank you all for the responses on the problem. The problem was that I missed ')' for the VALUES (..). Thank you for pointing that out.
Now, another problem is raised: ERROR: INSERT is not allowed in a non-volatile function. The language I am using is LANGUAGE 'plpgsql' IMMUTABLE STRICT. What should I change to make INSERT allowable? Thanks a lot John On Mon, Jan 5, 2009 at 9:57 AM, Steve Midgley <[email protected]> wrote: > At 10:20 AM 1/4/2009, [email protected] wrote: > >> Message-ID: <[email protected]> >> Date: Sat, 3 Jan 2009 17:57:32 -0800 >> From: "John Zhang" <[email protected]> >> To: [email protected] >> 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 > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
