Thread: SQL server application porting headache
Hi,
I have a Windows application (not written by myself) that uses ODBC to
connect to the database. It was developed using MS Access and SQL Server
as the database. However, we would like to run it using PostgreSQL
as database engine. While trying to do this I have run into some
problems:
1. The PostgreSQL ODBC driver from odbc.postgresql.org doesn't
seem to support dynasets (which I believe require keyset-driven
cursors). Is there any work on supporting this?
Using the driver from openlinksw.com I was able to get it up and
running. But this driver is not open. It is expensive however.
2. With the openlink ODBC driver I succesfully got the application
to connect to the database. But now I run into case-sensitivity
problems. The first query that fail look like this:
SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0;
^^^^^^^^^^
notice no quotes here
Since the table in question was created using the following
statement:
CREATE TABLE "IntParams" ("OperatorID" int4);
the query above obviously fails on the second reference to
OperatorID, claiming that "Attribute operatorid not found."
I'm using PostgreSQL 7.2.1. Is there a way around this problem?
I don't think it is possible to change the application, since
it is a rather special tool written by another company as closed
source. I suppose that if one could make identifiers case-
insensitive even when quoting them, it would work. Is there a
way to do that?
regards,
Oskar
Oskar Berggren <beo@sgs.o.se> writes:
> 2. With the openlink ODBC driver I succesfully got the application
> to connect to the database. But now I run into case-sensitivity
> problems. The first query that fail look like this:
> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0;
> ^^^^^^^^^^
> notice no quotes here
They expect that to work? AFAICS, that will be broken in any database
that is anywhere remotely close to SQL spec compliant. If the quoted
form works then the stored column name must be mixed-case, and that
means that it can *not* be accessed with an unquoted reference.
regards, tom lane
Tom Lane wrote: > Oskar Berggren <beo@sgs.o.se> writes: > >> 2. With the openlink ODBC driver I succesfully got the application >> to connect to the database. But now I run into case-sensitivity >> problems. The first query that fail look like this: > > >> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0; >> ^^^^^^^^^^ >> notice no quotes here > > > They expect that to work? AFAICS, that will be broken in any database > that is anywhere remotely close to SQL spec compliant. If the quoted > form works then the stored column name must be mixed-case, and that > means that it can *not* be accessed with an unquoted reference. > Maybe so, but it obviously work with MS Access and MS SQL Server. Though I am not surprised that those products are less specific concerning case. Normally I don't condone such behavior, but in this case I need to get this working without changing/correcting the client application. I have been looking at the parser somewhat and I have located the conversion to lowercase in the flex source code. It seems fairly easy to do this conversion even in the case of a quoted identifier. In fact, I'm just about to install a modified version of PostgreSQL. Or is there some other reason this won't work? regards, Oskar
Oskar Berggren wrote: > I have been looking at the parser somewhat and I have located the > conversion to lowercase in the flex source code. It seems fairly > easy to do this conversion even in the case of a quoted identifier. In > fact, I'm just about to install a modified version of PostgreSQL. Or > is there some other reason this won't work? > Turns out things weren't so easy after all. Now apparently the ODBC driver tries to find the table using a case-sensitive match which of course fail, due to the fact that the table name is now lowercase. Would it be difficult to make PostgreSQL case insensitive for real when comparing identifiers? That is, the actual comparison is case insensitive rather than having the parser convert everything to lowercase. Would I have to make modifications to lots of different places or are the comparisons in question contained in some (small) part of the source code? regards, Oskar -- /-----------------------+---------------------------+------------------\ | SGS Datanätgrupp | www.sgs.studenthem.gu.se | Office Hours | | Utlandagatan 24 | E-mail: dng@sgs.o.se | Mon-Thur 17-19 | | 412 80 Göteborg | Phone: 031-7081335 | | \-----------------------+---------------------------+------------------/
On Sun, 23 Jun 2002, Oskar Berggren wrote: > Tom Lane wrote: > >> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0; > >> ^^^^^^^^^^ > >> notice no quotes here > > > > They expect that to work? AFAICS, that will be broken in any database > > that is anywhere remotely close to SQL spec compliant. If the quoted > > form works then the stored column name must be mixed-case, and that > > means that it can *not* be accessed with an unquoted reference. > > Maybe so, but it obviously work with MS Access and MS SQL Server. It works with MS SQL Server? If so, I'm surprised. Are you sure the driver isn't doing something funny in this case? ISTR that SQL Server did the proper thing with quotes, or something pretty close to it. > I have been looking at the parser somewhat and I have located the > conversion to lowercase in the flex source code. It seems fairly > easy to do this conversion even in the case of a quoted identifier. Well, I'm almost certain now that MS SQL Server does *not* do that sort of thing. I can probably dig up the documentation references tomorrow if you're really curious about it. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
Oskar Berggren <beo@sgs.o.se> writes:
> Would it be difficult to make PostgreSQL case insensitive for real when
> comparing identifiers?
You could try altering the name comparison functions --- don't forget
the btree 3-way comparator, too. And something would have to be done
with the hash function for names. And I'm not sure what else; but I
suspect there are a lot of strcmp() calls that would have to be changed.
Curt Sampson remarks nearby that he doesn't believe SQL Server acts
the way you suggest. Are you sure you have diagnosed your problem
correctly?
regards, tom lane
On Mon, 24 Jun 2002, Curt Sampson wrote:
> On Sun, 23 Jun 2002, Oskar Berggren wrote:
>
> > >> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0;
> > >> ^^^^^^^^^^
> > >> notice no quotes here
Well, it's worse than I thought. Using SQL Server 7.0, I created a table
test1 with a column name "ColOne". In the Query Analyzer:
SELECT "ColOne" FROM test1 WHERE "ColOne" = 7
All right so far. But removing either set of quotes also works. Until
you change case, where it breaks even without quotes. (I guess I should
have tested with fully uppercase and fully lowercase column names, too,
but subsequent events addled me so much I lost the thought.)
So after this I move to isql, where doing it without quotes also works,
so long as I get the case right. (Wrong case without quotes still
fails.) But better yet, you can quote "ColOne" in the SELECT part of
the statement, but if you try to quote "ColOne" in the WHERE portion of
the statement, it fails with some error message about being unable to
convert a varchar to a column type or something. So you can't quote in
the WHERE clause at all, except with [] instead of "".
At this point I started to realise why the application designers may
have done their queries in the way shown above. I then poured myself a
very large Suntory whisky, booted back into NetBSD, and left it at that.
Congratuations; this is the first time I've booted Windows in months,
and it was as pleasurable as ever.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
Curt Sampson wrote: > On Mon, 24 Jun 2002, Curt Sampson wrote: > > >>On Sun, 23 Jun 2002, Oskar Berggren wrote: >> >> >>>>> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0; >>>>> ^^^^^^^^^^ >>>>> notice no quotes here >>>> > > Well, it's worse than I thought. Using SQL Server 7.0, I created a table > test1 with a column name "ColOne". In the Query Analyzer: > > SELECT "ColOne" FROM test1 WHERE "ColOne" = 7 > > All right so far. But removing either set of quotes also works. Until > you change case, where it breaks even without quotes. (I guess I should > have tested with fully uppercase and fully lowercase column names, too, > but subsequent events addled me so much I lost the thought.) > > So after this I move to isql, where doing it without quotes also works, > so long as I get the case right. (Wrong case without quotes still > fails.) But better yet, you can quote "ColOne" in the SELECT part of > the statement, but if you try to quote "ColOne" in the WHERE portion of > the statement, it fails with some error message about being unable to > convert a varchar to a column type or something. So you can't quote in > the WHERE clause at all, except with [] instead of "". Thanks for your research! I appreciate it. Now at least I know why it is like it is. Perhaps this is sufficient argument to get them to correct their queries. > At this point I started to realise why the application designers may > have done their queries in the way shown above. I then poured myself a > very large Suntory whisky, booted back into NetBSD, and left it at that. Tempting, although I'm not that fond of whisky myself. But I was out drinking beer all night long so perhaps I've had my fun for this weekend. :) regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.o.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Hi, I want CREATE TABLES WHICH HAVE a name length more than 31. I change NAMEDATALEN in src/include/postgres_ext.h from 32 to 100 and recompile the source code. I init the database and create a test base but the maximum identifier length is still 31. Can someone explain me what I must do to change this. Thanks. Mourad.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, I had similar problem porting an old application from MySQL, ~ solved quoting table/field indentifiers in the create statements, then made the attached patch. The patch alter the pgsql query parsing so that you can use insensitive case names in selects, but the server give you resultsets with the case you used when creating tables. try it on pgsql 7.2, should work.... Of course I'm interested in improving it. bye PS the patch also adds the ability to exclude objects from pg_dump with regular expression match. - -- - ------------------------------------------------------- Giuseppe Tanzilli g.tanzilli@gruppocsf.com CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy Oskar Berggren wrote: | Oskar Berggren wrote: | |> I have been looking at the parser somewhat and I have located the |> conversion to lowercase in the flex source code. It seems fairly |> easy to do this conversion even in the case of a quoted identifier. In |> fact, I'm just about to install a modified version of PostgreSQL. Or |> is there some other reason this won't work? |> | | Turns out things weren't so easy after all. Now apparently the ODBC | driver tries to find the table using a case-sensitive match which | of course fail, due to the fact that the table name is now lowercase. | | Would it be difficult to make PostgreSQL case insensitive for real when | comparing identifiers? That is, the actual comparison is case | insensitive rather than having the parser convert everything to | lowercase. Would I have to make modifications to lots of different | places or are the comparisons in question contained in some (small) | part of the source code? | | | regards, | Oskar | -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9FtA9XpUYkD1KGJ4RAiABAJ4xqWUdl9RBduGJLoTbcmzJvp5NFACfT7AY 30b3Go7SahQjgXJn5kkQ9MQ= =JPty -----END PGP SIGNATURE-----
Attachment
Curt Sampson wrote: > On Sun, 23 Jun 2002, Oskar Berggren wrote: > > >>Thanks for your research! I appreciate it. Now at least I know why it >>is like it is. Perhaps this is sufficient argument to get them to >>correct their queries. > > > Well, my research may be indicating that they are doing their queries > this "stupid" way for a very good reason: they can't do them the correct > way. (Well, they could probably use [] instead of "" in the WHERE > clause, but that's hardly compatable with postgres!) True. What I meant was for them to correct their queries in such a way as to always use the correct capitalization. If you combine that with a slightly hacked Postgres that is case sensitive even when not using qoutes (which I hope won't have any bad unexpected side effects) it Should(TM) work. But now another idea comes to mind... Would it be hard to make the parser recognize [] in the same way as ""? That way you don't have to meddle with the case sensitivity. But it still require modification of all the queries. > The correct solution is probably not to use mixed-case (or any-case) > table names, so that no quoting is necessary. And I have a funny feeling > I know what your developers are going to say when you ask them to change > all of their table names.... The problem is even bigger; the developers aren't mine. This is software from another company and I'm afraid they will say exactly what you think they will say. > > BTW, I've asked the Windows admin at my company if we don't happen to > have a copy of SQL Server kicking around, and maybe a server we could > install it on. If that is the case, I'll have no problem doing more > playing like this. If not, it's a bit tough, since I lose all access to > e-mail and everything when I boot up windows on my machine. I'm getting ready to give up and recommend to my bosses that we hire a consultant to run the darn thing on Windows and SQL Server. Which I suppose means that Microsoft has succeeded with their little strange case-sensitivity trick. Anyway, I will talk to the head developer at the company that made the application tomorrow and we'll see what happens. regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.studenthem.gu.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Does PostgreSQL support aliases? If so, you could create aliases for each of the names that gets used.
Oskar Berggren wrote: > > Hi, > > I have a Windows application (not written by myself) that uses ODBC to > connect to the database. It was developed using MS Access and SQL Server > as the database. However, we would like to run it using PostgreSQL > as database engine. While trying to do this I have run into some > problems: > > 1. The PostgreSQL ODBC driver from odbc.postgresql.org doesn't > seem to support dynasets (which I believe require keyset-driven > cursors). Is there any work on supporting this? Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ with checking the *(Trial) Updatable cursors* option. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/
[Repost due to bounce...someone playing with the mailing list server?] On Sun, 23 Jun 2002, Oskar Berggren wrote: > Thanks for your research! I appreciate it. Now at least I know why it > is like it is. Perhaps this is sufficient argument to get them to > correct their queries. Well, my research may be indicating that they are doing their queries this "stupid" way for a very good reason: they can't do them the correct way. (Well, they could probably use [] instead of "" in the WHERE clause, but that's hardly compatable with postgres!) The correct solution is probably not to use mixed-case (or any-case) table names, so that no quoting is necessary. And I have a funny feeling I know what your developers are going to say when you ask them to change all of their table names.... BTW, I've asked the Windows admin at my company if we don't happen to have a copy of SQL Server kicking around, and maybe a server we could install it on. If that is the case, I'll have no problem doing more playing like this. If not, it's a bit tough, since I lose all access to e-mail and everything when I boot up windows on my machine. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
Tom Lane wrote: > Oskar Berggren <beo@sgs.o.se> writes: > >>Would it be difficult to make PostgreSQL case insensitive for real when >>comparing identifiers? > > > You could try altering the name comparison functions --- don't forget > the btree 3-way comparator, too. And something would have to be done > with the hash function for names. And I'm not sure what else; but I > suspect there are a lot of strcmp() calls that would have to be changed. Thank you for your help, but this seems to be a bit too much work that almost certainly will lead to stability problems of some sort. I am now working on a another theory instead. If I can get the application developers to correct all their query strings so that they always use the exact same capitalization, and then make PostgreSQL be always case sensitive, quoted or not. This I believe would only require that I comment out a few rows in scan.l. Provided that everything that tries to talk to the database always use the correct capitalization. I have tried this and it seems to work for the query i mentioned in my original posting, which is as expected since that one actually use correct capitalization. However, shortly afterwards the application issue the query: SELECT "PassWord" FROM "Operators" WHERE Name='...' AND Password='...'; Since the column is called PassWord, you can see why I'm feeling I bit down right now... regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.studenthem.gu.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Oskar Berggren <beo@sgs.o.se> writes:
> But now another idea comes to mind... Would it be hard to make the
> parser recognize [] in the same way as ""?
Only if you're willing to lose array support ... which might be an
acceptable tradeoff for a server dedicated to running this broken
app. Not sure if anything internal depends on it, but I can't think
of anything offhand except the pg_stats view.
In any case, to go over to the [] approach would still require getting
the app developers to change their code, no?
regards, tom lane
"Mourad EL HADJ MIMOUNE" <mimoune@ensma.fr> writes:
> I want CREATE TABLES WHICH HAVE a name length more than 31.
> I change NAMEDATALEN in src/include/postgres_ext.h from 32 to 100 and
> recompile the source code. I init the database and create a test base but
> the maximum identifier length is still 31.
Did you actually recompile everything? (As in "gmake clean", "gmake all"
in the top-level directory?) Did you remember to reinstall the modified
binaries ("gmake install")?
regards, tom lane
Mourad EL HADJ MIMOUNE wrote: > Hi, > I want CREATE TABLES WHICH HAVE a name length more than 31. > I change NAMEDATALEN in src/include/postgres_ext.h from 32 to 100 and > recompile the source code. I init the database and create a test base but > the maximum identifier length is still 31. > Can someone explain me what I must do to change this. That is very strange. It should have worked and has worked for others in the past. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Oskar,
Instead of changing the application or changing PostgreSQL internals, can't
you just change the table names to match the queries that are being used?
Or is the app creating tables as it runs or something?
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Oskar Berggren
Sent: Sunday, June 23, 2002 12:31 PM
To: Curt Sampson
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL server application porting headache
Curt Sampson wrote:
> On Sun, 23 Jun 2002, Oskar Berggren wrote:
>
>
>>Thanks for your research! I appreciate it. Now at least I know why it
>>is like it is. Perhaps this is sufficient argument to get them to
>>correct their queries.
>
>
> Well, my research may be indicating that they are doing their queries
> this "stupid" way for a very good reason: they can't do them the correct
> way. (Well, they could probably use [] instead of "" in the WHERE
> clause, but that's hardly compatable with postgres!)
True. What I meant was for them to correct their queries in such a
way as to always use the correct capitalization. If you combine
that with a slightly hacked Postgres that is case sensitive even when
not using qoutes (which I hope won't have any bad unexpected side
effects) it Should(TM) work.
But now another idea comes to mind... Would it be hard to make the
parser recognize [] in the same way as ""? That way you don't have
to meddle with the case sensitivity. But it still require modification
of all the queries.
> The correct solution is probably not to use mixed-case (or any-case)
> table names, so that no quoting is necessary. And I have a funny feeling
> I know what your developers are going to say when you ask them to change
> all of their table names....
The problem is even bigger; the developers aren't mine. This is software
from another company and I'm afraid they will say exactly what you
think they will say.
>
> BTW, I've asked the Windows admin at my company if we don't happen to
> have a copy of SQL Server kicking around, and maybe a server we could
> install it on. If that is the case, I'll have no problem doing more
> playing like this. If not, it's a bit tough, since I lose all access to
> e-mail and everything when I boot up windows on my machine.
I'm getting ready to give up and recommend to my bosses that we hire
a consultant to run the darn thing on Windows and SQL Server. Which
I suppose means that Microsoft has succeeded with their little strange
case-sensitivity trick. Anyway, I will talk to the head developer
at the company that made the application tomorrow and we'll see what
happens.
regards,
Oskar
--
/----------------------------------------------------------------------\
| Oskar Berggren beo@sgs.studenthem.gu.se |
| Network and Software Engineer SGS Datanätgrupp |
| Gothenburg, Sweden |
\----------------------------------------------------------------------/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Oskar Berggren sez:
[...]
} I'm getting ready to give up and recommend to my bosses that we hire
} a consultant to run the darn thing on Windows and SQL Server. Which
} I suppose means that Microsoft has succeeded with their little strange
} case-sensitivity trick. Anyway, I will talk to the head developer
} at the company that made the application tomorrow and we'll see what
} happens.
Hm. Perhaps you could mess with the ODBC driver (and install it under a
different name, I expect) so that it lowercases everything not in single
quotes, and strips double quotes. It's a hack, but it sounds like it would
work plausibly at a small performance cost. An appropriate function:
/* assumes inbuf is null-terminated and outbuf is allocated at least as
* large as inbuf; inbuf *must* be the entire query else quotes might get
* unbalanced; ignores all i18n considerations; outbuf will be filled with
* the stripped and sanitized null-terminated string, no larger than inbuf
*/
void
strip_and_sanitize(const char *inbuf, char *outbuf) {
/* these are boolean flags */
int escmode = 0;
int quotemode = 0;
char *iptr = inbuf;
char *optr = outbuf;
while (*iptr) {
char ochar = *iptr;
if (escmode) {
escmode = 0;
} else {
switch (ochar) {
case '\\':
escmode = 1;
break;
case '"';
if (!quotemode)
ochar = 0;
break;
case '\'':
quotemode ^= 1; /* toggle */
break;
default:
if (!quotemode)
ochar = tolower(ochar);
}
}
if (ochar)
*(optr++) = ochar;
++iptr;
}
*optr = 0;
}
} regards,
} Oskar
--Greg
On Sun, Jun 23, 2002 at 08:44:50PM +0200, Oskar Berggren wrote: > I have tried this and it seems to work for the query i mentioned in > my original posting, which is as expected since that one actually > use correct capitalization. However, shortly afterwards the > application issue the query: > > SELECT "PassWord" FROM "Operators" WHERE Name='...' AND Password='...'; > > Since the column is called PassWord, you can see why I'm feeling > I bit down right now... Would it not be easier if the code simply lowercased everything, including quoted identifiers. This is totally wrong but will produce the effect you want (unless ofcourse, the names of the output columns important). -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Gregory Seidman wrote: > Oskar Berggren sez: > [...] > } I'm getting ready to give up and recommend to my bosses that we hire > } a consultant to run the darn thing on Windows and SQL Server. Which > } I suppose means that Microsoft has succeeded with their little strange > } case-sensitivity trick. Anyway, I will talk to the head developer > } at the company that made the application tomorrow and we'll see what > } happens. > > Hm. Perhaps you could mess with the ODBC driver (and install it under a > different name, I expect) so that it lowercases everything not in single > quotes, and strips double quotes. It's a hack, but it sounds like it would > work plausibly at a small performance cost. Perhaps, but only if I can use the open driver. Right now I'm using a commercial driver from openlinksw.com because I couldn't get the open one to support dynasets. regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.o.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Peter Darley wrote: > Oskar, > Instead of changing the application or changing PostgreSQL internals, can't > you just change the table names to match the queries that are being used? > Or is the app creating tables as it runs or something? > Thanks, > Peter Darley No, since the application isn't consistent in its identifiers casing, nor in its use of quotes. For example it issues the following query: SELECT "PassWord" FROM "Operators" WHERE Password='...'; There is no way PostgreSQL is gonna accept that without modifying either PostgreSQL or the application, or possibly, as was suggested elsewhere, make the ODBC driver lowercase every identifier, quoted or not. regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.o.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Hiroshi Inoue wrote: > Oskar Berggren wrote: > >>Hi, >> >>I have a Windows application (not written by myself) that uses ODBC to >>connect to the database. It was developed using MS Access and SQL Server >>as the database. However, we would like to run it using PostgreSQL >>as database engine. While trying to do this I have run into some >>problems: >> >> 1. The PostgreSQL ODBC driver from odbc.postgresql.org doesn't >> seem to support dynasets (which I believe require keyset-driven >> cursors). Is there any work on supporting this? > > > Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ > with checking the *(Trial) Updatable cursors* option. > I already have; it didn't work. This was with the ODBC3 driver included in the package from odbc.postgresql.org. Perhaps the snapshot you are mentioning are much never? If so, I'll be willing to test it again. regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.o.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Martijn van Oosterhout wrote: > On Sun, Jun 23, 2002 at 08:44:50PM +0200, Oskar Berggren wrote: > >>I have tried this and it seems to work for the query i mentioned in >>my original posting, which is as expected since that one actually >>use correct capitalization. However, shortly afterwards the >>application issue the query: >> >> SELECT "PassWord" FROM "Operators" WHERE Name='...' AND Password='...'; >> >>Since the column is called PassWord, you can see why I'm feeling >>I bit down right now... > > > Would it not be easier if the code simply lowercased everything, including > quoted identifiers. This is totally wrong but will produce the effect you > want (unless ofcourse, the names of the output columns important). > I thought so too and tried it, since this is very easy to do. However, then for some reason the application couldn't find a table - again. It turns out the ODBC driver is doing some name comparising too, which of course fails if the table is really named using any other case than what the application specifies. I'm using the closed-source driver from openlinksw.com. Not sure if you can make it ignore case. Perhaps I will look into this. regards, Oskar -- /----------------------------------------------------------------------\ | Oskar Berggren beo@sgs.o.se | | Network and Software Engineer SGS Datanätgrupp | | Gothenburg, Sweden | \----------------------------------------------------------------------/
Oskar Berggren wrote: > > Hiroshi Inoue wrote: > > Oskar Berggren wrote: > > > >>Hi, > >> > >>I have a Windows application (not written by myself) that uses ODBC to > >>connect to the database. It was developed using MS Access and SQL Server > >>as the database. However, we would like to run it using PostgreSQL > >>as database engine. While trying to do this I have run into some > >>problems: > >> > >> 1. The PostgreSQL ODBC driver from odbc.postgresql.org doesn't > >> seem to support dynasets (which I believe require keyset-driven > >> cursors). Is there any work on supporting this? > > > > > > Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ > > with checking the *(Trial) Updatable cursors* option. > > > > I already have; it didn't work. This was with the ODBC3 driver > included in the package from odbc.postgresql.org. Perhaps the > snapshot you are mentioning are much never? Possibly. Keyset-driven cursors were implemented recently. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/
Hi,
Thanks.
I tried what you said ("gmake clean", "gmake all" and then "gmake install")
without success.
If you have more precision to do these please inform me?!
Best regards,
Mourad.
> "Mourad EL HADJ MIMOUNE" <mimoune@ensma.fr> writes:
> > I want CREATE TABLES WHICH HAVE a name length more than 31.
> > I change NAMEDATALEN in src/include/postgres_ext.h from 32 to 100 and
> > recompile the source code. I init the database and create a test base
but
> > the maximum identifier length is still 31.
>
> Did you actually recompile everything? (As in "gmake clean", "gmake all"
> in the top-level directory?) Did you remember to reinstall the modified
> binaries ("gmake install")?
>
> regards, tom lane
Hi Mourad,
I saw your message on the postgres group. I'm not terribly
familiar with postgres, but my experience with other
software packages is that sometimes I find myself running a
different executable than the one I just built. I'm
assuming your working on a unix OS? (I don't know if this is
a good assumption).
One thing you can do is check "which psql" and "which
initdb" to see which version of postgres executables are
being found in your path. You can even "ls -la `which
psql`" to see the date on the file. Is this the copy you
recently built? Echo $PATH to see if there are other
versions in your path. On some flavors of unix you can type
"where psql" or "where postmaster" to list all locations of
the executable in your path. Modify your PATH or move older
versions to a subdirectory to take them out of your path if
you find out you're running a version that's not the new
version.
And if this advice doesn't help, delete this message
quickly!
I hope this helps.
Sarah
smarie@ekno.com
Mourad EL HADJ MIMOUNE wrote:
>
> Hi,
>
> Thanks.
>
> I tried what you said ("gmake clean", "gmake all" and then "gmake install")
> without success.
>
> If you have more precision to do these please inform me?!
>
> Best regards,
>
> Mourad.
>
> > "Mourad EL HADJ MIMOUNE" <mimoune@ensma.fr> writes:
> > > I want CREATE TABLES WHICH HAVE a name length more than 31.
> > > I change NAMEDATALEN in src/include/postgres_ext.h from 32 to 100 and
> > > recompile the source code. I init the database and create a test base
> but
> > > the maximum identifier length is still 31.
> >
> > Did you actually recompile everything? (As in "gmake clean", "gmake all"
> > in the top-level directory?) Did you remember to reinstall the modified
> > binaries ("gmake install")?
> >
> > regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html