Thread: keyword LIMIT in SQL-statement, psqlodbc workaround?
Hi, I'm using a commercial application. Well, apart from the problem that it crashes my psqlodbc.dll (which I'm trying to debug) it uses the keyword LIMIT. Like: CREATE ITEMS (NAME VARCHAR(10), LIMIT NUMERIC... To my knowledge it worked with PostgreSQL 6.4. Why not with 7.x? I thought it should be possible. Did I misinterpret SQL-92/98? MS-SQL and ORACLE are accepting it. Since I can't persuade my application to put LIMIT in quotes, I need another solution. Any ideas, workarounds how to do it in psqlodbc? regards Johann
Johann Zuschlag writes: > I'm using a commercial application. Well, apart from the problem > that it crashes my psqlodbc.dll (which I'm trying to debug) > it uses the keyword LIMIT. > > Like: CREATE ITEMS (NAME VARCHAR(10), LIMIT NUMERIC... > > To my knowledge it worked with PostgreSQL 6.4. Why not with 7.x? Not sure what was going on in 6.4 but LIMIT is a key word at least since 7.0. The reason that this is so is the difficulty parsing commands like this: SELECT 'xxx' && LIMIT 5; ^ When the parser is there it does not know whether && is an infix or postfix operator, in other words whether LIMIT is an operand (as it could potentially be if your LIMIT column ever needs to be selected) or a separate clause. This could perhaps be fixed with more than one token lookahead and some sweat, but it hasn't been done. > I thought it should be possible. Did I misinterpret SQL-92/98? > MS-SQL and ORACLE are accepting it. It's not part of SQL, but ORACLE has a similar clause IIRC. > Since I can't persuade my application to put LIMIT in quotes, > I need another solution. Any ideas, workarounds how to do it in > psqlodbc? You could try rebuilding PostgreSQL with LIMIT renamed to something like XYZLIMIT (look into src/backend/parser), but that strikes me as a brute force hack. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Hi,
now my initial problem:
I'm using a commercial application which is crashing while trying to
insert sets into tables (using psqlodbc.dll).
I did some tracing:
It crashes in module convert.c / copy_statement_with_parameters
in function:
convert_special_chars(char *si, char *dst, int used)
The access violation appears at:
if (si[i] == '\r' && i+1 < strlen(si) && si[i+1] == '\n')
si = 0x00000001
used = -103
It seems that it runs into an access violation, since 'si' has
been assigned a wrong value. Also 'used = -103' doesn't
seem to be healthy value. 'dst' seems to be alright.
convert_special_chars was called in
copy_statement_with_parameters:
convert_special_chars(buf, &new_statement[npos], used);
'buf' seems to be the same like 'buffer':
So 'used' and 'buffer' where assigned at (in copy_statement_with_parameters):
/* Assign correct buffers based on data at exec param or not */ if (
stmt->parameters[param_number].data_at_exec){ used = stmt->parameters[param_number].EXEC_used ?
*stmt->parameters[param_number].EXEC_used: SQL_NTS; buffer = stmt->parameters[param_number].EXEC_buffer; }
else{ used = stmt->parameters[param_number].used ? *stmt->parameters[param_number].used : SQL_NTS; buffer
=stmt->parameters[param_number].buffer; }
Well, here my knowledge ends. Something seems to be wrong with the ODBC parameters
supplied by my application, but:
Same application works flawless with MS-SQL-ODBC and ORACLE-ODBC.
So IMHO there must be a problem in psqlodbc.dll. Even though I admit that the buggy
call might be accepted in MS-ODBC due to another bug. :-)
Could somebody point me in the correct direction? Any hints? Patches?
I really would like to use Postgres for my app.
regards
Johann
Try 7.1 beta1 and see if the bug has been fixed already.
[ Charset ISO-8859-1 unsupported, converting... ]
> Hi,
>
> now my initial problem:
>
> I'm using a commercial application which is crashing while trying to
> insert sets into tables (using psqlodbc.dll).
>
> I did some tracing:
>
> It crashes in module convert.c / copy_statement_with_parameters
> in function:
>
> convert_special_chars(char *si, char *dst, int used)
>
> The access violation appears at:
>
> if (si[i] == '\r' && i+1 < strlen(si) && si[i+1] == '\n')
>
> si = 0x00000001
> used = -103
>
> It seems that it runs into an access violation, since 'si' has
> been assigned a wrong value. Also 'used = -103' doesn't
> seem to be healthy value. 'dst' seems to be alright.
>
> convert_special_chars was called in
> copy_statement_with_parameters:
>
> convert_special_chars(buf, &new_statement[npos], used);
>
> 'buf' seems to be the same like 'buffer':
>
> So 'used' and 'buffer' where assigned at (in copy_statement_with_parameters):
>
> /* Assign correct buffers based on data at exec param or not */
> if ( stmt->parameters[param_number].data_at_exec) {
> used = stmt->parameters[param_number].EXEC_used ? *stmt->parameters[param_number].EXEC_used : SQL_NTS;
> buffer = stmt->parameters[param_number].EXEC_buffer;
> }
> else {
> used = stmt->parameters[param_number].used ? *stmt->parameters[param_number].used : SQL_NTS;
> buffer = stmt->parameters[param_number].buffer;
> }
>
> Well, here my knowledge ends. Something seems to be wrong with the ODBC parameters
> supplied by my application, but:
>
> Same application works flawless with MS-SQL-ODBC and ORACLE-ODBC.
>
> So IMHO there must be a problem in psqlodbc.dll. Even though I admit that the buggy
> call might be accepted in MS-ODBC due to another bug. :-)
>
> Could somebody point me in the correct direction? Any hints? Patches?
> I really would like to use Postgres for my app.
>
> regards
>
> Johann
>
>
>
>
-- 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,
Pennsylvania19026
Hi Bruce,
Thanks for your reply.
I tried to build psqlodbc.dll using 7.1 beta1.
Sorry, still the same exception.
Do you have any other ideas, hints,etc.?
Unfortunately I don't know enough about ODBC.
regards
Johann
On Tue, 2 Jan 2001 02:44:58 -0500 (EST), Bruce Momjian wrote:
>Try 7.1 beta1 and see if the bug has been fixed already.
>
>> Hi,
>>
>> now my initial problem:
>>
>> I'm using a commercial application which is crashing while trying to
>> insert sets into tables (using psqlodbc.dll).
>>
>> I did some tracing:
>>
>> It crashes in module convert.c / copy_statement_with_parameters
>> in function:
>>
>> convert_special_chars(char *si, char *dst, int used)
>>
>> The access violation appears at:
>>
>> if (si[i] == '\r' && i+1 < strlen(si) && si[i+1] == '\n')
>>
>> si = 0x00000001
>> used = -103
>>
>> It seems that it runs into an access violation, since 'si' has
>> been assigned a wrong value. Also 'used = -103' doesn't
>> seem to be healthy value. 'dst' seems to be alright.
>>
>> convert_special_chars was called in
>> copy_statement_with_parameters:
>>
>> convert_special_chars(buf, &new_statement[npos], used);
>>
>> 'buf' seems to be the same like 'buffer':
>>
>> So 'used' and 'buffer' where assigned at (in copy_statement_with_parameters):
>>
>> /* Assign correct buffers based on data at exec param or not */
>> if ( stmt->parameters[param_number].data_at_exec) {
>> used = stmt->parameters[param_number].EXEC_used ? *stmt->parameters[param_number].EXEC_used : SQL_NTS;
>> buffer = stmt->parameters[param_number].EXEC_buffer;
>> }
>> else {
>> used = stmt->parameters[param_number].used ? *stmt->parameters[param_number].used : SQL_NTS;
>> buffer = stmt->parameters[param_number].buffer;
>> }
>>
>> Well, here my knowledge ends. Something seems to be wrong with the ODBC parameters
>> supplied by my application, but:
>>
>> Same application works flawless with MS-SQL-ODBC and ORACLE-ODBC.
>>
>> So IMHO there must be a problem in psqlodbc.dll. Even though I admit that the buggy
>> call might be accepted in MS-ODBC due to another bug. :-)
>>
>> Could somebody point me in the correct direction? Any hints? Patches?
>> I really would like to use Postgres for my app.
>>
>> regards
>>
>> Johann
>>
>
>--
> 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
No ideas, sorry. I don't know much about ODBC. Can you send a
backtrace of the crash? Not sure how to do that from odbc though.
[ Charset ISO-8859-1 unsupported, converting... ]
> Hi Bruce,
>
> Thanks for your reply.
> I tried to build psqlodbc.dll using 7.1 beta1.
> Sorry, still the same exception.
>
> Do you have any other ideas, hints,etc.?
>
> Unfortunately I don't know enough about ODBC.
>
> regards
>
> Johann
>
>
> On Tue, 2 Jan 2001 02:44:58 -0500 (EST), Bruce Momjian wrote:
>
> >Try 7.1 beta1 and see if the bug has been fixed already.
> >
> >> Hi,
> >>
> >> now my initial problem:
> >>
> >> I'm using a commercial application which is crashing while trying to
> >> insert sets into tables (using psqlodbc.dll).
> >>
> >> I did some tracing:
> >>
> >> It crashes in module convert.c / copy_statement_with_parameters
> >> in function:
> >>
> >> convert_special_chars(char *si, char *dst, int used)
> >>
> >> The access violation appears at:
> >>
> >> if (si[i] == '\r' && i+1 < strlen(si) && si[i+1] == '\n')
> >>
> >> si = 0x00000001
> >> used = -103
> >>
> >> It seems that it runs into an access violation, since 'si' has
> >> been assigned a wrong value. Also 'used = -103' doesn't
> >> seem to be healthy value. 'dst' seems to be alright.
> >>
> >> convert_special_chars was called in
> >> copy_statement_with_parameters:
> >>
> >> convert_special_chars(buf, &new_statement[npos], used);
> >>
> >> 'buf' seems to be the same like 'buffer':
> >>
> >> So 'used' and 'buffer' where assigned at (in copy_statement_with_parameters):
> >>
> >> /* Assign correct buffers based on data at exec param or not */
> >> if ( stmt->parameters[param_number].data_at_exec) {
> >> used = stmt->parameters[param_number].EXEC_used ? *stmt->parameters[param_number].EXEC_used :
SQL_NTS;
> >> buffer = stmt->parameters[param_number].EXEC_buffer;
> >> }
> >> else {
> >> used = stmt->parameters[param_number].used ? *stmt->parameters[param_number].used : SQL_NTS;
> >> buffer = stmt->parameters[param_number].buffer;
> >> }
> >>
> >> Well, here my knowledge ends. Something seems to be wrong with the ODBC parameters
> >> supplied by my application, but:
> >>
> >> Same application works flawless with MS-SQL-ODBC and ORACLE-ODBC.
> >>
> >> So IMHO there must be a problem in psqlodbc.dll. Even though I admit that the buggy
> >> call might be accepted in MS-ODBC due to another bug. :-)
> >>
> >> Could somebody point me in the correct direction? Any hints? Patches?
> >> I really would like to use Postgres for my app.
> >>
> >> regards
> >>
> >> Johann
> >>
> >
> >--
> > 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
>
>
>
>
-- 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,
Pennsylvania19026