Обсуждение: 09.03.0100 cursor failures on various architectures
Hi, I've uploaded 09.03.0100 to Debian, but the build daemons for various architectures are reporting regression test failures for the "cursors" test, e.g. on mipsel: /usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/lib/postgresql/9.3/bin' --launcher=./launcher --dbname=contrib_regression sampletables connect stmthandlesselect commands multistmt getresult prepare params notice arraybinding insertreturning dataatexecution boolsascharcvtnulldate alter quotes cursors positioned-update catalogfunctions (using postmaster on localhost, port 5432) ============== dropping database "contrib_regression" ============== HINWEIS: Datenbank „contrib_regression“ existiert nicht, wird übersprungen DROP DATABASE ============== creating database "contrib_regression" ============== CREATE DATABASE ALTER DATABASE ============== running regression test queries ============== [...] test cursors ... FAILED [...] ======================= 1 of 20 tests failed. ======================= regression.diffs: *** /home/myon/psqlodbc-09.03.0100/test/expected/cursors.out 2013-12-17 15:16:00.000000000 +0100 --- /home/myon/psqlodbc-09.03.0100/test/results/cursors.out 2014-02-12 11:33:34.787112560 +0100 *************** *** 1,7 **** \! ./src/cursors-test connected ! SQL_CURSOR_COMMIT_BEHAVIOR: SQL_CB_PRESERVE ! SQL_CURSOR_ROLLBACK_BEHAVIOR: SQL_CB_PRESERVE foo1 foo2 foo3 foo4 foo5 foo6 foo7 foo8 foo9 foo10 foo11 foo12 foo13 foo14 foo15 foo16 foo17 foo18 foo19 foo20 ... foo3207foo3208 foo3209 foo3210 Fetched 3210 rows altogether --- 1,7 ---- \! ./src/cursors-test connected ! SQL_CURSOR_COMMIT_BEHAVIOR: unknown (728104962) ! SQL_CURSOR_ROLLBACK_BEHAVIOR: unknown (728104962) foo1 foo2 foo3 foo4 foo5 foo6 foo7 foo8 foo9 foo10 foo11 foo12 foo13 foo14 foo15 foo16 foo17 foo18 foo19 foo20 ... foo3207foo3208 foo3209 foo3210 Fetched 3210 rows altogether The problem is the same on mips, mipsel, powerpc, s390x, and sparc. (With different numbers in brackets after the "unknown".) s390x has that problem, plus an additional diff for positioned-update: *** /«PKGBUILDDIR»/test/expected/positioned-update.out Tue Dec 17 14:16:00 2013 --- /«PKGBUILDDIR»/test/results/positioned-update.out Wed Feb 5 17:09:31 2014 *************** *** 13,19 **** 2 2 3 3 4 4 ! 105 5 7 7 8 8 9 9 --- 13,19 ---- 2 2 3 3 4 4 ! 5 5 7 7 8 8 9 9 The full build logs can be seen on https://buildd.debian.org/status/package.php?p=psqlodbc (ia64 is also failing, but that looks like a unixodbc problem, and ia64 will be going away in Debian soonish anyway.) Does anyone have an idea what's the problem here? The package was building basically fine before; some builds were somewhat fragile due to unixodbc problems and had to be retried manually, but these failures are new and are reproducible. Mit freundlichen Grüßen, Christoph Berg -- Senior Berater, Tel.: +49 (0)21 61 / 46 43-187 credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209 Hohenzollernstr. 133, 41061 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer pgp fingerprint: 5C48 FE61 57F4 9179 5970 87C6 4C5A 6BAB 12D2 A7AE
Вложения
On 02/12/2014 12:45 PM, Christoph Berg wrote: > I've uploaded 09.03.0100 to Debian, but the build daemons for various > architectures are reporting regression test failures for the "cursors" > test, e.g. on mipsel: >[...] > The problem is the same on mips, mipsel, powerpc, s390x, and sparc. It was a bug in the cursors test case. It was passing pointer to an SQLUINTEGER variable to SQLGetInfo(..., SQL_CURSOR_COMMIT_BEHAVIOR, ...), but that property is a SQLUSMALLINT. On a little-endian system it didn't make a difference, as SQLGetInfo set the low 2 bytes, but on big-endian it's more visible. Fixed, thanks for the report! > s390x has that problem, plus an additional diff for positioned-update: > > *** /«PKGBUILDDIR»/test/expected/positioned-update.out Tue Dec 17 14:16:00 2013 > --- /«PKGBUILDDIR»/test/results/positioned-update.out Wed Feb 5 17:09:31 2014 > *************** > *** 13,19 **** > 2 2 > 3 3 > 4 4 > ! 105 5 > 7 7 > 8 8 > 9 9 > --- 13,19 ---- > 2 2 > 3 3 > 4 4 > ! 5 5 > 7 7 > 8 8 > 9 9 Hmm. I'll take a look.. - Heikki
On 02/12/2014 12:45 PM, Christoph Berg wrote:
> s390x has that problem, plus an additional diff for positioned-update:
>
> *** /«PKGBUILDDIR»/test/expected/positioned-update.out Tue Dec 17 14:16:00 2013
> --- /«PKGBUILDDIR»/test/results/positioned-update.out Wed Feb 5 17:09:31 2014
> ***************
> *** 13,19 ****
> 2 2
> 3 3
> 4 4
> ! 105 5
> 7 7
> 8 8
> 9 9
> --- 13,19 ----
> 2 2
> 3 3
> 4 4
> ! 5 5
> 7 7
> 8 8
> 9 9
This is also an issue related to endianess and mismatched datatypes. The
problem is with the SQLBindCol call. Positioned-update test does this:
long colvalue;
...
rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &colvalue, 0, &indColvalue);
And SQLBindCol does this:
case SQL_C_SLONG:
case SQL_C_LONG:
len = 4;
if (bind_size > 0)
*((SQLINTEGER *) rgbValueBindRow) = atol(neut_str);
else
*((SQLINTEGER *) rgbValue + bind_row) = atol(neut_str);
break;
So, SQLBindPos assumes that the target variable is of type SQLINTEGER,
when the caller indicated that it's SQL_C_LONG. My gut reaction is that
that's bogus - if the caller said that it's of C-type long, by passing
SQL_C_LONG, we should believe that, rather than assume that SQL_C_LONG
means SQLINTEGER. I found a brief thread on this on the unixodbc-dev
mailing list:
http://mailman.unixodbc.org/pipermail/unixodbc-dev/2005-March/000396.html
The same author raised the issue also on the psqlodbc mailing list:
http://www.postgresql.org/message-id/flat/4224F80D.2080103@kkcsm.net#4224F80D.2080103@kkcsm.net
Microsoft has a table of SQL_C_* codes and which C types they correspond
to (http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx),
but that's not taking into account other operating systems where the
widths of C integer types are different. We didn't explicitly discuss in
that psqlodbc mailing list thread if it's sane that SQL_C_LONG means a
32-bit integer regardless of how wide the C "long" type actually is.
While I think the way the code currently works is wrong, it's probably
too late to change that. It would be interesting to know how other ODBC
drivers have interpreted that.
Conclusion: I've committed a patch to change the test case to use
SQLINTEGER instead of long as the variable's datatype. I also added an
explicit test case for SQLBindCol.
- Heikki
Re: Heikki Linnakangas 2014-02-14 <52FE1220.9050205@vmware.com> > Conclusion: I've committed a patch to change the test case to use > SQLINTEGER instead of long as the variable's datatype. I also added > an explicit test case for SQLBindCol. Oh cool. Thanks for looking into this :) I had already uploaded your first fix a few minutes before you posted the second message, and the results look promising, with s390x (and ia64) still failing. https://buildd.debian.org/status/package.php?p=psqlodbc Will try the other patch as well now. Christoph -- cb@df7cb.de | http://www.df7cb.de/
Вложения
Re: To Heikki Linnakangas 2014-02-14 <20140214135452.GC15607@msgid.df7cb.de> > Will try the other patch as well now. Works, thanks again for the patches. Christoph -- cb@df7cb.de | http://www.df7cb.de/
Вложения
(2014/02/14 21:54), Heikki Linnakangas wrote: > On 02/12/2014 12:45 PM, Christoph Berg wrote: >> s390x has that problem, plus an additional diff for positioned-update: >> >> *** /«PKGBUILDDIR»/test/expected/positioned-update.out Tue Dec 17 >> 14:16:00 2013 >> --- /«PKGBUILDDIR»/test/results/positioned-update.out Wed Feb 5 >> 17:09:31 2014 >> *************** >> *** 13,19 **** >> 2 2 >> 3 3 >> 4 4 >> ! 105 5 >> 7 7 >> 8 8 >> 9 9 >> --- 13,19 ---- >> 2 2 >> 3 3 >> 4 4 >> ! 5 5 >> 7 7 >> 8 8 >> 9 9 > > This is also an issue related to endianess and mismatched datatypes. The > problem is with the SQLBindCol call. Positioned-update test does this: > > long colvalue; > ... > rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &colvalue, 0, &indColvalue); > > And SQLBindCol does this: > > case SQL_C_SLONG: > case SQL_C_LONG: > len = 4; > if (bind_size > 0) > *((SQLINTEGER *) rgbValueBindRow) = atol(neut_str); > else > *((SQLINTEGER *) rgbValue + bind_row) = atol(neut_str); > break; > > So, SQLBindPos assumes that the target variable is of type SQLINTEGER, > when the caller indicated that it's SQL_C_LONG. My gut reaction is that > that's bogus - if the caller said that it's of C-type long, by passing > SQL_C_LONG, we should believe that, rather than assume that SQL_C_LONG > means SQLINTEGER. I found a brief thread on this on the unixodbc-dev > mailing list: > > http://mailman.unixodbc.org/pipermail/unixodbc-dev/2005-March/000396.html > > The same author raised the issue also on the psqlodbc mailing list: > > http://www.postgresql.org/message-id/flat/4224F80D.2080103@kkcsm.net#4224F80D.2080103@kkcsm.net > > Microsoft has a table of SQL_C_* codes and which C types they correspond > to (http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx), > but that's not taking into account other operating systems where the > widths of C integer types are different. We didn't explicitly discuss in > that psqlodbc mailing list thread if it's sane that SQL_C_LONG means a > 32-bit integer regardless of how wide the C "long" type actually is. I don't think so. Where do you find references to OS native C types in ODBC's spec? ODBC uses its own SQL types in its spec and each SQL_C_xxxxxxx stands for one of them not a native one. For 32bit integers, use SQL_C_LONG (SQLINTEGER). For 64bit integers, use SQL_C_BIGINT (SQLBIGINT). There's no type named SQLxxLONGxx. I can see the following line in sqlext.h provided by Microsoft or unixODBC. #define SQL_C_LONG SQL_INTEGER /* INTEGER */ It means that those driver manager think SQL_C_LONG means SQLINTEGER. What meaning does it have to take different way from the driver manager whose APIs ODBC applications call? regards, Hiroshi Inoue -- I am using the free version of SPAMfighter. SPAMfighter has removed 5285 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len Do you have a slow PC? Try a Free scan http://www.spamfighter.com/SLOW-PCfighter?cid=sigen
On 02/25/2014 06:09 AM, Inoue, Hiroshi wrote: > (2014/02/14 21:54), Heikki Linnakangas wrote: >> Microsoft has a table of SQL_C_* codes and which C types they correspond >> to (http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx), >> but that's not taking into account other operating systems where the >> widths of C integer types are different. We didn't explicitly discuss in >> that psqlodbc mailing list thread if it's sane that SQL_C_LONG means a >> 32-bit integer regardless of how wide the C "long" type actually is. > > I don't think so. > Where do you find references to OS native C types in ODBC's spec? > ODBC uses its own SQL types in its spec and each SQL_C_xxxxxxx > stands for one of them not a native one. See the page I linked to above. It has a table with a one-to-one-to-one relationship between the SQL_C_xxx, ODBC C typedef, and the native C type. For example, for SQL_C_LONG, it says that the corresponding ODBC type is SQLINTEGER, which equals to C "long int". Such a one-to-one-to-one relationship is clear as far as Microsoft is concerned, as in Windows "long int" is always a 32-bit integer. From the SQL_C_xxx names it's quite clear that SQL_C_LONG refers to the native C type, "long". And as long as you're working in a Microsoft environment, there's no confusion because sizeof(long) == sizeof(SQLINTEGER). But on other platforms, it's a different story. Looking at my draft copy of the SQL 2011 standard, "Part 3: Call-Level Interface (SQL/CLI)", I don't see any mention of the SQL_C_* data types. It was probably left out of the standard, precisely because it would've caused confusion on non-Microsoft platforms. > For 32bit integers, use SQL_C_LONG (SQLINTEGER). > For 64bit integers, use SQL_C_BIGINT (SQLBIGINT). > There's no type named SQLxxLONGxx. > > I can see the following line in sqlext.h provided by Microsoft or unixODBC. > > #define SQL_C_LONG SQL_INTEGER /* INTEGER > */ > > It means that those driver manager think SQL_C_LONG means SQLINTEGER. > What meaning does it have to take different way from the driver manager > whose APIs ODBC applications call? Yeah, that's how unixODBC has resolved the situation, but it's not obvious. In fact, I'd argue that most applications are *not* written that way. For example, look at this code example from Microsoft: http://technet.microsoft.com/en-us/library/aa177876%28v=sql.80%29.aspx It does this: long lEmpID; ... SQLBindCol(hStmt, 1, SQL_C_LONG, (SQLPOINTER) &lEmpID, sizeof(long), &pIndicators[0]); That sample code will *not* work with unixodbc on a big-endian platform where sizeof(long) == 8. See also http://mailman.unixodbc.org/pipermail/unixodbc-support/2008-June/001756.html. According to that post, while unixODBC takes the stance that SQL_C_LONG corresponds to a 32-bit SQLINTEGER, iodbc has taken a different view. On iodbc, SQL_C_LONG means C native type "long", whether it's 32-bits or 64-bits wide. That's reasonable; thanks to that decision, the above sample code would work on iodbc without modifications. (However, now that I actually look at the iODBC code in their git repository, ISTM they're also defining SQL_C_LONG to bit 32-bits, ie. the same as unixODBC. Weird, maybe they changed their mind at some point..) Just to be clear, I agree with the decision that SQL_C_LONG means a 32-bit type, regardless of sizeof(long). But it's not obvious! That decision will break applications. In summary: 1. Most applications are written with the assumption that SQL_C_LONG means the native C data type "long". That's clearly what SQL_C_LONG was supposed to mean when the ODBC specification was written, and that's what Microsoft's sample code does. 2. That's *not* how psqlODBC or unixODBC interprets it. With unixODBC, you should use the C data type "SQLINTEGER" with SQL_C_LONG. 3. For maximum portability, an application should avoid using SQL_C_xx. Instead, always use SQL_INTEGER or SQL_BIGINT, with an SQLINTEGER or SQLBIGINT variable, and cast to native C types. We should document this somewhere, like in the FAQ.. - Heikki
(2014/02/25 20:05), Heikki Linnakangas wrote: > On 02/25/2014 06:09 AM, Inoue, Hiroshi wrote: >> (2014/02/14 21:54), Heikki Linnakangas wrote: >>> Microsoft has a table of SQL_C_* codes and which C types they correspond >>> to (http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx), >>> but that's not taking into account other operating systems where the >>> widths of C integer types are different. We didn't explicitly discuss in >>> that psqlodbc mailing list thread if it's sane that SQL_C_LONG means a >>> 32-bit integer regardless of how wide the C "long" type actually is. >> >> I don't think so. >> Where do you find references to OS native C types in ODBC's spec? >> ODBC uses its own SQL types in its spec and each SQL_C_xxxxxxx >> stands for one of them not a native one. > > See the page I linked to above. It has a table with a one-to-one-to-one > relationship between the SQL_C_xxx, ODBC C typedef, and the native C > type. For example, for SQL_C_LONG, it says that the corresponding ODBC > type is SQLINTEGER, which equals to C "long int". > > Such a one-to-one-to-one relationship is clear as far as Microsoft is > concerned, as in Windows "long int" is always a 32-bit integer. From the > SQL_C_xxx names it's quite clear that SQL_C_LONG refers to the native C > type, "long". And as long as you're working in a Microsoft environment, > there's no confusion because sizeof(long) == sizeof(SQLINTEGER). But on > other platforms, it's a different story. > > Looking at my draft copy of the SQL 2011 standard, "Part 3: Call-Level > Interface (SQL/CLI)", I don't see any mention of the SQL_C_* data types. > It was probably left out of the standard, precisely because it would've > caused confusion on non-Microsoft platforms. > >> For 32bit integers, use SQL_C_LONG (SQLINTEGER). >> For 64bit integers, use SQL_C_BIGINT (SQLBIGINT). >> There's no type named SQLxxLONGxx. >> >> I can see the following line in sqlext.h provided by Microsoft or >> unixODBC. >> >> #define SQL_C_LONG SQL_INTEGER /* INTEGER >> */ >> >> It means that those driver manager think SQL_C_LONG means SQLINTEGER. >> What meaning does it have to take different way from the driver manager >> whose APIs ODBC applications call? > > Yeah, that's how unixODBC has resolved the situation, but it's not > obvious. ISTM unixODBC team was only bothering about what SQLINTEGER is. They were not bothering about what ODBC data type SQL_C_LONG corresponds to. In fact, I'd argue that most applications are *not* written > that way. For example, look at this code example from Microsoft: We sometimes see such inappropriate examples in Mircorsoft document. ODBC applications should use ODBC data type variables to insulate the app from platform-specific requirements. > http://technet.microsoft.com/en-us/library/aa177876%28v=sql.80%29.aspx > > It does this: > > long lEmpID; > ... > SQLBindCol(hStmt, 1, SQL_C_LONG, (SQLPOINTER) &lEmpID, sizeof(long), > &pIndicators[0]); > > That sample code will *not* work with unixodbc on a big-endian platform > where sizeof(long) == 8. > > See also > http://mailman.unixodbc.org/pipermail/unixodbc-support/2008-June/001756.html. > According to that post, while unixODBC takes the stance that SQL_C_LONG > corresponds to a 32-bit SQLINTEGER, iodbc has taken a different view. On > iodbc, SQL_C_LONG means C native type "long", whether it's 32-bits or > 64-bits wide. Didn't SQL_C_LONG stand for SQLINTEGER anyway? > That's reasonable; thanks to that decision, the above > sample code would work on iodbc without modifications. (However, now > that I actually look at the iODBC code in their git repository, ISTM > they're also defining SQL_C_LONG to bit 32-bits, ie. the same as > unixODBC. Weird, maybe they changed their mind at some point..) > > Just to be clear, I agree with the decision that SQL_C_LONG means a > 32-bit type, regardless of sizeof(long). But it's not obvious! That > decision will break applications. > > In summary: > > 1. Most applications are written with the assumption that SQL_C_LONG > means the native C data type "long". That's clearly what SQL_C_LONG was > supposed to mean when the ODBC specification was written, and that's > what Microsoft's sample code does. > > 2. That's *not* how psqlODBC or unixODBC interprets it. With unixODBC, > you should use the C data type "SQLINTEGER" with SQL_C_LONG. > > 3. For maximum portability, an application should avoid using SQL_C_xx. > Instead, always use SQL_INTEGER or SQL_BIGINT, with an SQLINTEGER or > SQLBIGINT variable, and cast to native C types. > > We should document this somewhere, like in the FAQ.. Unfortunately I'm not happy with your summary. My point is simple. Use ODBC data type variables instead of native C data ones when calling ODBC APIs. regards, Hiroshi Inoue
On 02/25/2014 05:13 PM, Hiroshi Inoue wrote:
>> >1. Most applications are written with the assumption that SQL_C_LONG
>> >means the native C data type "long". That's clearly what SQL_C_LONG was
>> >supposed to mean when the ODBC specification was written, and that's
>> >what Microsoft's sample code does.
>> >
>> >2. That's*not* how psqlODBC or unixODBC interprets it. With unixODBC,
>> >you should use the C data type "SQLINTEGER" with SQL_C_LONG.
>> >
>> >3. For maximum portability, an application should avoid using SQL_C_xx.
>> >Instead, always use SQL_INTEGER or SQL_BIGINT, with an SQLINTEGER or
>> >SQLBIGINT variable, and cast to native C types.
>> >
>> >We should document this somewhere, like in the FAQ..
> Unfortunately I'm not happy with your summary.
>
> My point is simple.
> Use ODBC data type variables instead of native C data ones when calling
> ODBC APIs.
Hmm, isn't that the same as my point 3?
I was thinking of something like below for the docs:
----
Don't use SQL_C_xxx. They are not part of the SQL/CLI specification, and
hence not reliably portable across platforms. Instead, use the ODBC
variable data types, like SQLINTEGER and SQLBIGINT, and the
corresponding type codes (e.g. SQL_INTEGER and SQL_BIGINT).
For example, the following code is not portable:
long empID;
...
SQLBindCol(stmt, 1, SQL_C_LONG, (SQLPOINTER) &empID,
sizeof(long), &indicator);
Instead, do this:
SQLINTEGER empID;
...
SQLBindCol(stmt, 1, SQL_INTEGER, (SQLPOINTER) &empID,
sizeof(SQLINTEGER), &indicator);
And if necessary, cast to the C type:
long lEmpID = (long) empID;
SQL_C_LONG, SQL_C_SLONG and SQL_C_ULONG in particular are known to be a
problem, as the width of the C datatype "long" varies, but unixODBC and
the psqlODBC driver always maps SQL_C_LONG to a 32-bit data type,
regardless of the width of "long" on the platform. An application using
SQL_C_LONG and "long" will not work correctly on a platform where
sizeof(long) == 8. It is easily seen on big-endian systems, where the
values returned will be off by 2^32, but can also lead to difficult to
find bugs on little-endian systems, as the driver will not initialize
the high 4 bytes.
----
- Heikki
(2014/02/26 1:56), Heikki Linnakangas wrote: > On 02/25/2014 05:13 PM, Hiroshi Inoue wrote: >>> >1. Most applications are written with the assumption that SQL_C_LONG >>> >means the native C data type "long". That's clearly what SQL_C_LONG was >>> >supposed to mean when the ODBC specification was written, and that's >>> >what Microsoft's sample code does. >>> > >>> >2. That's*not* how psqlODBC or unixODBC interprets it. With unixODBC, >>> >you should use the C data type "SQLINTEGER" with SQL_C_LONG. >>> > >>> >3. For maximum portability, an application should avoid using SQL_C_xx. >>> >Instead, always use SQL_INTEGER or SQL_BIGINT, with an SQLINTEGER or >>> >SQLBIGINT variable, and cast to native C types. >>> > >>> >We should document this somewhere, like in the FAQ.. >> Unfortunately I'm not happy with your summary. >> >> My point is simple. >> Use ODBC data type variables instead of native C data ones when calling >> ODBC APIs. > > Hmm, isn't that the same as my point 3? > > I was thinking of something like below for the docs: > > ---- > Don't use SQL_C_xxx. They are not part of the SQL/CLI specification, and > hence not reliably portable across platforms. Why does using SQL_C_XXXX lose portablity? Even though SQL/CLI is a standard, ODBC also has been a (defact?) standard which is older than CLI and widely used. They are different standard. We should begin a project e.g. pgsqlcli when we are to stop using SQL_C_XXXX. > Instead, use the ODBC > variable data types, like SQLINTEGER and SQLBIGINT, and the > corresponding type codes (e.g. SQL_INTEGER and SQL_BIGINT). > > For example, the following code is not portable: > > long empID; > ... > SQLBindCol(stmt, 1, SQL_C_LONG, (SQLPOINTER) &empID, > sizeof(long), &indicator); > > Instead, do this: > > SQLINTEGER empID; > ... > SQLBindCol(stmt, 1, SQL_INTEGER, (SQLPOINTER) &empID, > sizeof(SQLINTEGER), &indicator); Though the code works well, the code is wrong as an ODBC application in principle. The code works as expected simply because SQL_INTEGER has the same value as SQL_C_LONG has fortunately. > And if necessary, cast to the C type: > > long lEmpID = (long) empID; > > > SQL_C_LONG, SQL_C_SLONG and SQL_C_ULONG in particular are known to be a > problem, I see no problem except the naming which should have been SQL_C_INTEGER. regards, Hiroshi Inoue -- I am using the free version of SPAMfighter. SPAMfighter has removed 5330 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len Do you have a slow PC? Try a Free scan http://www.spamfighter.com/SLOW-PCfighter?cid=sigen
On 02/26/2014 03:19 AM, Inoue, Hiroshi wrote:
> (2014/02/26 1:56), Heikki Linnakangas wrote:
>> I was thinking of something like below for the docs:
>>
>> ----
>> Don't use SQL_C_xxx. They are not part of the SQL/CLI specification, and
>> hence not reliably portable across platforms.
>
> Why does using SQL_C_XXXX lose portablity?
SQL_C_xxx is a Microsoft-only extension, so it's not guaranteed to work
with all SQL/CLI implementations. In practice, unixODBC defines
SQL_C_xxx too, so in practice an application using them will work.
I think you're advocating for this:
SQLINTEGER lEmpID;
...
SQLBindCol(hStmt, 1, SQL_C_LONG, (SQLPOINTER) &lEmpID, sizeof(SQLINTEGER),
&pIndicators[0]);
From a readability point of view, IMHO that's just bizarre. The
SQLBindCall call is claiming to the driver that the variable is of type
"long", by specifying SQL_C_LONG. But in reality, the variable is of
type SQLINTEGER. You can argue that SQL_C_LONG doesn't mean that the
variable is of type "long", but "SQLINTEGER", but that's just bizarre.
The name SQL_C_LONG very clearly says "long", just like SQL_C_SHORT says
"short".
It works on Windows, because sizeof(long) == sizeof(SQLINTEGER) on
Windows, and it works with unixODBC because unixODBC defines SQL_C_LONG
as 32-bits regardless of the actual width of "long". But it's not a good
way to write an application. Why use the ODBC extension SQL_C_LONG
instead of the SQL_INTEGER type code specified by SQL/CLI? SQL_INTEGER
is more clear, and as a bonus, it also complies with SQL/CLI.
So while the combination of SQL_C_LONG type code and SQLINTEGER variable
works, I would not recommend that. The SQL_INTEGER type code and
SQLINTEGER variable combination is more clear.
> Even though SQL/CLI is a standard, ODBC also has been a (defact?)
> standard which is older than CLI and widely used.
> They are different standard.
> We should begin a project e.g. pgsqlcli when we are to stop
> using SQL_C_XXXX.
ODBC is a superset of SQL/CLI [1]. An application written for SQL/CLI
works with ODBC libraries and drivers. But not the other way round; an
application that's written to the ODBC spec does not necessarily work
with other SQL/CLI implementations.
The driver already works with both ODBC and SQL/CLI, so there's no need
to start a new project for that.
Of course, if you don't care about portability of your application, you
can write it relying purely on ODBC, and use SQL_C_LONG type code and
"long" variables. But if you want to maximize portability, you should
stick to the SQL/CLI spec and use SQL_INTEGER type code and SQLINTEGER
variables.
> > Instead, use the ODBC
>> variable data types, like SQLINTEGER and SQLBIGINT, and the
>> corresponding type codes (e.g. SQL_INTEGER and SQL_BIGINT).
>>
>> For example, the following code is not portable:
>>
>> long empID;
>> ...
>> SQLBindCol(stmt, 1, SQL_C_LONG, (SQLPOINTER) &empID,
>> sizeof(long), &indicator);
>>
>> Instead, do this:
>>
>> SQLINTEGER empID;
>> ...
>> SQLBindCol(stmt, 1, SQL_INTEGER, (SQLPOINTER) &empID,
>> sizeof(SQLINTEGER), &indicator);
>
> Though the code works well, the code is wrong as an ODBC application
> in principle.
> The code works as expected simply because SQL_INTEGER has the same
> value as SQL_C_LONG has fortunately.
That is not an accident. The ODBC specification is a superset of the
SQL/CLI specification. An application written for the SQL/CLI
specification also works with ODBC, so for maximum portability you
should use the SQL/CLI type codes (SQL_INTEGER etc.).
[1] http://msdn.microsoft.com/en-us/library/ms712622%28v=vs.85%29.aspx
- Heikki
(2014/02/26 17:51), Heikki Linnakangas wrote:
> On 02/26/2014 03:19 AM, Inoue, Hiroshi wrote:
>> (2014/02/26 1:56), Heikki Linnakangas wrote:
>>> I was thinking of something like below for the docs:
>>>
>>> ----
>>> Don't use SQL_C_xxx. They are not part of the SQL/CLI specification, and
>>> hence not reliably portable across platforms.
>>
>> Why does using SQL_C_XXXX lose portablity?
>
> SQL_C_xxx is a Microsoft-only extension, so it's not guaranteed to work
> with all SQL/CLI implementations. In practice, unixODBC defines
> SQL_C_xxx too, so in practice an application using them will work.
>
> I think you're advocating for this:
>
> SQLINTEGER lEmpID;
> ...
> SQLBindCol(hStmt, 1, SQL_C_LONG, (SQLPOINTER) &lEmpID, sizeof(SQLINTEGER),
> &pIndicators[0]);
>
> From a readability point of view, IMHO that's just bizarre. The
> SQLBindCall call is claiming to the driver that the variable is of type
> "long", by specifying SQL_C_LONG. But in reality, the variable is of
> type SQLINTEGER. You can argue that SQL_C_LONG doesn't mean that the
> variable is of type "long", but "SQLINTEGER", but that's just bizarre.
> The name SQL_C_LONG very clearly says "long",just like SQL_C_SHORT says
> "short".
> It works on Windows, because sizeof(long) == sizeof(SQLINTEGER) on
> Windows, and it works with unixODBC because unixODBC defines SQL_C_LONG
> as 32-bits regardless of the actual width of "long".
The ODBC spec clearly mentiones that SQL_C_LONG corresponds to
SQLINTEGER from the first. Why do you often skip SQLINTEGER?
It's a result that Microsoft, unixODBC or iODBC defines SQLINTEGER
as 32-bit integers. The problem for unixODBC or iODBC was whether
SQLINTEGER means 32-bits or 64-bits.
> But it's not a good
> way to write an application. Why use the ODBC extension SQL_C_LONG
> instead of the SQL_INTEGER type code specified by SQL/CLI? SQL_INTEGER
> is more clear, and as a bonus, it also complies with SQL/CLI.
Aren't you forgetting ODBC is very old?
Though ODBC and sql/cli are closely related, ODBC is older than
sql/cli. When ODBC was introduced more than 20 years ago,
Microsoft didn't have 32-bit OS yet. That's why SQLINTEGER was
was denoted as *long int* not *int*. It meant 32-bit integrs for
both their working 16-bit OS and coming 32-bit OS.
Of cource the spec was only for 16/32bit. There was no guarantee
how the 64-bit spec would be in the future. At the time pretty many
people expected that int in future 64-bit OSes would be 64-bit integers.
So I don't surprize even if SQLINTEGER means 64-bit integers sometime
somewhere. But I don't believe SQL_C_LONG stands for the type other than
SQLINTEGER. Please tell me where I can find such a fact.
Now int still means 32-bit integers in many 64-bit platforms. People
still needs 32-bit integers as well as 64-bit integers. So ODBC also
needed 32-bit integers.
> So while the combination of SQL_C_LONG type code and SQLINTEGER variable
> works, I would not recommend that. The SQL_INTEGER type code and
> SQLINTEGER variable combination is more clear.
>
>> Even though SQL/CLI is a standard, ODBC also has been a (defact?)
>> standard which is older than CLI and widely used.
>> They are different standard.
>> We should begin a project e.g. pgsqlcli when we are to stop
>> using SQL_C_XXXX.
>
> ODBC is a superset of SQL/CLI [1]. An application written for SQL/CLI
> works with ODBC libraries and drivers. But not the other way round; an
> application that's written to the ODBC spec does not necessarily work
> with other SQL/CLI implementations.
Though you emphasize ODBC is a superset, ODBC was not a superset
of sql/cli from the first. In the first place SQL/CLI didn't exist
when ODBC was introduced. Of cource SQL_C_XXXX was not an extension
of sql/cli spec then. You can't find SQL_C_XXXX because SQL_C_XXXX
is a C-specific concept and maybe sql/cli shrinked the spec so as
to avoid language-specific concept. As a result the spec uses the
same notations SQL_xxxx for different kind of concepts. ODBC uses
different notations for different kind of concepts.
Which do you prefer?
When ODBC was introduced more than 20 years ago, working OSs of
Mirosoft was 16-bit ones. 32-bit integers must have been denoted
as *long int*. They used SQL_C_LONG because *long* was the most
portable data type which means 32-bit integers then.
Now I'm examining what SQLINTEGER means in SQL/CLI spec though
it may be an old one.
Hmmm, could you please tell me SQLINTEGER is a 32-bit integer,
64-bit one or platform-dependent one on 64-bit platforms?
I find a line
SQLINTEGER long Length of buffers for column data and ...
Is it right in the latest spec?
regards,
Hiroshi Inoue
On 02/27/2014 01:30 PM, Hiroshi Inoue wrote: > (2014/02/26 17:51), Heikki Linnakangas wrote: >> It works on Windows, because sizeof(long) == sizeof(SQLINTEGER) on >> Windows, and it works with unixODBC because unixODBC defines SQL_C_LONG >> as 32-bits regardless of the actual width of "long". > > The ODBC spec clearly mentiones that SQL_C_LONG corresponds to > SQLINTEGER from the first. It also clearly says that the native C type corresponding SQL_C_LONG and SQL_INTEGER is "long". That's all true in the Microsoft world, but on a platform where sizeof(long) == 8, one of the equivalences must be broken. SQL_C_LONG has to correspong to either SQLINTEGER, or "long". The ODBC spec naturally doesn't say which one, because it's not concerned with other platforms. > > But it's not a good >> way to write an application. Why use the ODBC extension SQL_C_LONG >> instead of the SQL_INTEGER type code specified by SQL/CLI? SQL_INTEGER >> is more clear, and as a bonus, it also complies with SQL/CLI. > > Aren't you forgetting ODBC is very old? > Though ODBC and sql/cli are closely related, ODBC is older than > sql/cli. When ODBC was introduced more than 20 years ago, > Microsoft didn't have 32-bit OS yet. That's why SQLINTEGER was > was denoted as *long int* not *int*. It meant 32-bit integrs for > both their working 16-bit OS and coming 32-bit OS. > Of cource the spec was only for 16/32bit. There was no guarantee > how the 64-bit spec would be in the future. At the time pretty many > people expected that int in future 64-bit OSes would be 64-bit integers. Yeah, I understand the history. > So I don't surprize even if SQLINTEGER means 64-bit integers sometime > somewhere. > But I don't believe SQL_C_LONG stands for the type other than > SQLINTEGER. Please tell me where I can find such a fact. Surely, in the early ODBC versions, SQL_C_LONG clearly meant "long", not SQLINTEGER. That's why it's called SQL_C_*LONG*. When 64-bit operating systems arrived, that became confusing because it was no longer a given that "long" is 32-bits. It was not a problem for Microsoft and the ODBC spec, because on Windows, "long" is indeed 32-bits. So in the ODBC spec, SQL_C_LONG stands for both SQLINTEGER and "long", because they are the same. But for the cross-platform CLI specification, that was not good enough. To resolve the situation, they didn't bring in the SQL_C_* type codes. Instead, you use the SQL_INTEGER type code, and it refers to an SQLINTEGER variable. >>> Even though SQL/CLI is a standard, ODBC also has been a (defact?) >>> standard which is older than CLI and widely used. >>> They are different standard. >>> We should begin a project e.g. pgsqlcli when we are to stop >>> using SQL_C_XXXX. >> >> ODBC is a superset of SQL/CLI [1]. An application written for SQL/CLI >> works with ODBC libraries and drivers. But not the other way round; an >> application that's written to the ODBC spec does not necessarily work >> with other SQL/CLI implementations. > > Though you emphasize ODBC is a superset, ODBC was not a superset > of sql/cli from the first. In the first place SQL/CLI didn't exist > when ODBC was introduced. Of cource SQL_C_XXXX was not an extension > of sql/cli spec then. You can't find SQL_C_XXXX because SQL_C_XXXX > is a C-specific concept and maybe sql/cli shrinked the spec so as > to avoid language-specific concept. As a result the spec uses the > same notations SQL_xxxx for different kind of concepts. ODBC uses > different notations for different kind of concepts. Yeah. > Which do you prefer? I would recommend applications to not use SQL_C_xxx type codes. I would recommend using a SQLINTEGER variable with SQL_INTEGER type code. That's the most readable, and most portable option. (I actually like the idea of separate SQL_C_xxx type codes, to refer to the native types. I agree it's slightly confusing to use the same SQL_INTEGER code to refer to the SQL data type, and the C variable's datatype. But alas, if SQL_C_LONG doesn't in fact mean "long", then that's even more confusing, and it's better to stay away from that. With SQL_INTEGER, at least it's clear that it refers to an SQLINTEGER variable. I wish unixOdbc had chosen differently, and decided that SQL_C_LONG != SQL_INTEGER, and it refers to "long", whatever "long" is on your platform. IMHO that would've been more clear. But it's too late to worry about that.) > When ODBC was introduced more than 20 years ago, working OSs of > Mirosoft was 16-bit ones. 32-bit integers must have been denoted > as *long int*. They used SQL_C_LONG because *long* was the most > portable data type which means 32-bit integers then. > > Now I'm examining what SQLINTEGER means in SQL/CLI spec though > it may be an old one. > Hmmm, could you please tell me SQLINTEGER is a 32-bit integer, > 64-bit one or platform-dependent one on 64-bit platforms? > I find a line > SQLINTEGER long Length of buffers for column data and ... > Is it right in the latest spec? Hmm, I don't see that line in my copy. But it does include a sample sqlcli.h header file that has this in Annex H: typedef long SQLINTEGER; typedef long long SQLBIGINT; That probably assumes that "long" is 32-bits wide. The Annex has been marked as "informative", so it's only meant as an example, though. It's worth noting that if you use SQL_INTEGER type code and SQLINTEGER variable consistently, and avoid SQL_C_LONG and "long", it doesn't matter whether SQLINTEGER is 32- or 64-bits wide. It also doesn't matter if SQL_C_LONG == SQL_INTEGER. Your application will work regardless of that. - Heikki
(2014/02/28 18:53), Heikki Linnakangas wrote: > On 02/27/2014 01:30 PM, Hiroshi Inoue wrote: >> (2014/02/26 17:51), Heikki Linnakangas wrote: >>> It works on Windows, because sizeof(long) == sizeof(SQLINTEGER) on >>> Windows, and it works with unixODBC because unixODBC defines SQL_C_LONG >>> as 32-bits regardless of the actual width of "long". >> >> The ODBC spec clearly mentiones that SQL_C_LONG corresponds to >> SQLINTEGER from the first. > > It also clearly says that the native C type corresponding SQL_C_LONG and > SQL_INTEGER is "long". It's foramtive as you taught me about SQLINTEGER in SQL/CLI. Microsoft gave an example which can be used in their working 16-bit OSs and comimg 32-bit OSs. Please note that at that time neither SQL/CLI nor ODBC guaranteed that the spec would be valid under 64-bit paltforms. It was not so long ago the spec of 64-bit ODBC was determined. >> Though you emphasize ODBC is a superset, ODBC was not a superset >> of sql/cli from the first. In the first place SQL/CLI didn't exist >> when ODBC was introduced. Of cource SQL_C_XXXX was not an extension >> of sql/cli spec then. You can't find SQL_C_XXXX because SQL_C_XXXX >> is a C-specific concept and maybe sql/cli shrinked the spec so as >> to avoid language-specific concept. As a result the spec uses the >> same notations SQL_xxxx for different kind of concepts. ODBC uses >> different notations for different kind of concepts. > > Yeah. > >> Which do you prefer? > > I would recommend applications to not use SQL_C_xxx type codes. I would > recommend using a SQLINTEGER variable with SQL_INTEGER type code. That's > the most readable, and most portable option. > (I actually like the idea of separate SQL_C_xxx type codes, to refer to > the native types. Unfortunately such a use is inappropriate in database programing. For example I can find the following in SQL/CLI spec. 2.2.2 Data type for C For binary compatibility of object modules, applications must use symbolic data types defined ... It's very important and you are saying the opposite of SQL/CLI recommendation from the first. What you have to discuss with is the Open Group not unixODBC. >> Now I'm examining what SQLINTEGER means in SQL/CLI spec though >> it may be an old one. >> Hmmm, could you please tell me SQLINTEGER is a 32-bit integer, >> 64-bit one or platform-dependent one on 64-bit platforms? >> I find a line >> SQLINTEGER long Length of buffers for column data and ... >> Is it right in the latest spec? > > Hmm, I don't see that line in my copy. But it does include a sample > sqlcli.h header file that has this in Annex H: > > typedef long SQLINTEGER; > typedef long long SQLBIGINT; > > That probably assumes that "long" is 32-bits wide. It's very important whether SQLINTEGER means 32-bit or 64-bit in 64-bit platforms. If SQLINTEGER means 64-bit integers in SQL/CLI, I have to tell members of this list not to use it. > The Annex has been > marked as "informative", so it's only meant as an example, though. Yes it's formative. And so is for ODBC. As you emphasized many times ODBC became a superset of SQL/CLI conseqently. regards, Hiroshi Inoue