Thread: Re: [GENERAL] MS Access to PostgreSQL
--- William Shatner <shatner.william@gmail.com> wrote: > I have recently migrated from MS Access to > PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = > aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM > \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the > format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level > of the > hieracarchy and below, this was achieved using the > '%' in Access, this > however doesn't seem to work in PostgreSQL, it > doesn't error out but > it just seems to see the '%' as a normal character. > > How can this be done in PostgreSQL? > I suspect that, unlike Access, PostgreSQL will intrepret C-style escape sequences (ie: \r, \n, \t) so you'll need to properly escape the backslash in aPath like so: folder1\\folder2\\folder3 Regards, Shelby Cain __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Hi Shelby, I don't think the problem isn't with the backslashes. They are escaped and seem to be working fine e.g. to insert a backslash in Access I had to use one escape character ('\\') whereas in PostgreSQL four backslashes ('\\\\') are required. The line that inserts the % is as follows... String aPath = group.getPath() + aOldGroupName + "\\%"; It just doesn't seem to be having the same effect in PostgreSQL as in Access. B -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Shelby Cain Sent: 11 March 2005 17:32 To: William Shatner; pgsql-novice@postgresql.org; pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] [GENERAL] MS Access to PostgreSQL --- William Shatner <shatner.william@gmail.com> wrote: > I have recently migrated from MS Access to > PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = > aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM > \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the > format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level > of the > hieracarchy and below, this was achieved using the > '%' in Access, this > however doesn't seem to work in PostgreSQL, it > doesn't error out but > it just seems to see the '%' as a normal character. > > How can this be done in PostgreSQL? > I suspect that, unlike Access, PostgreSQL will intrepret C-style escape sequences (ie: \r, \n, \t) so you'll need to properly escape the backslash in aPath like so: folder1\\folder2\\folder3 Regards, Shelby Cain __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
On Fri, 2005-03-11 at 17:52 +0000, Brian Gunning wrote: [rearranged quoting] > From: pgsql-jdbc-owner@postgresql.org > --- William Shatner <shatner.william@gmail.com> wrote: > > I have recently migrated from MS Access to > > PostgreSQL.Previously I had > > a SQL command > > > > ResultSet aGroupResultSet = > > aGroupPathStmt.executeQuery( > > "SELECT \"groupID\",\"fullpath\" FROM > > \"groups\" WHERE > > \"fullpath\" Like '" + > > aPath + "'"); > > > > > > where aPath was equal to 'folder\another folder\%'. > > > I don't think the problem isn't with the backslashes. They are escaped and > seem to be working fine e.g. to insert a backslash in Access I had to use > one escape character ('\\') whereas in PostgreSQL four backslashes ('\\\\') > are required. The line that inserts the % is as follows... > > String aPath = group.getPath() + aOldGroupName + "\\%"; are the backslashes in group.getPath() + aOldGroupName escaped ? does aGroupPathStmt.executeQuery() escape backslashes ? did you print the SQL statement to screen or logfile to make sure what is actually sent to executeQuery() ? does your language support placeholders ? this kind of problems are usually easier to deal with with them. gnari