[BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
The following bug has been logged online: Bug reference: 1775 Logged by: Marcus Raphelt Email address: [EMAIL PROTECTED] PostgreSQL version: latest Operating system: WinXP (dev), Win2000 (server) Description:UTF8 to ISO does not convert the german "sharp s" (ß) Details: Hi, I currently have a iso_8859_1 app and included the opengeodb tables to have a set of german cities and zipcodes. http://opengeodb.sourceforge.net/index.php?FrontPage_en I downloaded this dump: http://prdownloads.sourceforge.net/opengeodb/opengeodb-0.2.3c-UTF8-postgres. zip?download They offer a Postgresql dump that can be imported without hassle, but is in UTF8. I did several attempts using convert() to convert the city names to iso. It works, and it also works fine on german umlauts (äöü), but it seems like convert() does NOT convert the german "Ã" / sharp s / ß... cheers, marcus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #1776: Unable to insert datetime with fractional seconds in postgreSQL database using ODBC api
The following bug has been logged online:
Bug reference: 1776
Logged by: sankar p
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6
Operating system: RHEL WS 4.0
Description:Unable to insert datetime with fractional seconds in
postgreSQL database using ODBC api
Details:
Hi,
Cause :
Unable to insert datetime with fractional seconds in postgreSQL database
(using timestamp escape sequence).
Environment : RHEL WS 4.0
ODBC DM : libiodbc-3.52.1.tar
1) Table is created using the following statement.
create table product(solddt timestamp);
2) inserted one record using the following statment.
insert into product values('1986-01-12 05:22:10:82375'). The timestamp
values is inserted successfully without any issue. I confirmed that the
timestamp datatype is working without any problem.
3) I have tried to insert using the following C-sample program. In this
sample, I am using ODBC escape sequences to insert TIMESTAMP value.
for example:- "{ts '1986-01-12 05:22:10'}")
4) When executing the C-sample program, I am getting the following error
message in the ODBC log.
Error Message in the ODBC log:-
Error while executing the query (non-fatal);
ERROR: date/time field value out of range: "-00-00 00:00:00"
C- Sample Program:-
#include
#include
#include
#include
#include
#include
#define MAXNAME 250
#define MAXBUFLEN 250
int main(int argc, char* argv[])
{
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
char szConnectString[]="DSN=postgres;UID=postgres;PWD=postgres";
SQLSMALLINT nODBCIsolationLevel=0;
short int lConnectStringLen;
char szConnectStringOut[250];
char szSQLStatement[250];
RETCODE retcode;
// SQLBindCol variables
SQLCHAR szName[MAXNAME+1];
SQLINTEGER cbName;
SQLTIMESTAMP tmStamp;
SQLINTEGER Len;
SQLCHAR inData[20];
SQL_TIMESTAMP_STRUCT stTimeStamp;
int length;
char strDate[26];
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if (retcode != 0)
printf("Allocate Env failed \n");
else
printf("Allocate Env Pass \n");
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)
SQL_OV_ODBC3, SQL_IS_INTEGER);
// Allocate an ODBC connection and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
if (retcode != 0)
{
printf("Allocate ODBC Handle failed \n");
}
printf("Allocate ODBC Handle passed \n");
//retcode = SQLConnect(hdbc1,"mysql", SQL_NTS,"mysql", SQL_NTS,"mysql",
SQL_NTS);
retcode=SQLDriverConnect(hdbc1,NULL,(SQLCHAR*)szConnectString,SQL_NTS,(SQLCH
AR*)szConnectStringOut,sizeof(szConnectStringOut),
&lConnectStringLen,SQL_DRIVER_NOPROMPT);
if (retcode != 0)
{
printf("Connection failed \n");
}
else
printf("Connection Successful \n");
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);
if (retcode != 0)
{
printf("Allocate Statement failed \n");
}
strcpy(szSQLStatement, "INSERT INTO PRODUCT(SOLDDT) VALUES(?)");
printf("Executing : %s \n", szSQLStatement);
retcode = SQLPrepare(hstmt1,(unsigned char *)szSQLStatement, SQL_NTS);
/*
stTimeStamp.day =12;
stTimeStamp.month=01;
stTimeStamp.year =2000;
stTimeStamp.hour =5;
stTimeStamp.minute =22;
stTimeStamp.second =10;
stTimeStamp.fraction =511212;
*/
memset(strDate, 0, strlen(strDate));
//not working
strcpy(strDate, "{ts '1986-01-12 05:22:10'}");
//working
// strcpy(strDate, "1986-01-12 05:22:10.82375");
/* retcode = SQLBindParameter( hstmt1,
1,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP,
SQL_TYPE_TIMESTAMP,
SQL_DESC_DATETIME_INTERVAL_PRECISION,
0,
&stTimeStamp,sizeof(stTimeStamp),
NULL);
*/
retcode = SQLBindParameter( hstmt1,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_TIMESTAMP,
19,
0,
strDate,sizeof(strDate),
NULL);
retcode = SQLExecute(hstmt1);
if ( retcode == 0 )
printf("SQLExecute - Success \r\n");
else
printf("SQLExecute - Not success \r\n");
/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
odbc trace log:
odbctimeB7FEAA00 ENTER SQLPrepare
SQLHSTMT 0x8b9a7d8
SQLCHAR * 0xbfe30500
| INSERT INTO PRODUCT(SOLDDT) VALUES(?)|
SQLINTEGER-3 (SQL_NTS)
odbctimeB7FEAA00 EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x8b9a7d8
SQLCHAR * 0xbfe30500
Re: [BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
> Bug reference: 1775 > Logged by: Marcus Raphelt > Email address: [EMAIL PROTECTED] > PostgreSQL version: latest > Operating system: WinXP (dev), Win2000 (server) > Description:UTF8 to ISO does not convert the german "sharp s" > (ß) > Details: > > Hi, > > I currently have a iso_8859_1 app and included the opengeodb > tables to have a set of german cities and zipcodes. > > http://opengeodb.sourceforge.net/index.php?FrontPage_en > I downloaded this dump: > > http://prdownloads.sourceforge.net/opengeodb/opengeodb-0.2.3c- > UTF8-postgres. > zip?download > > They offer a Postgresql dump that can be imported without > hassle, but is in UTF8. I did several attempts using > convert() to convert the city names to iso. It works, and it > also works fine on german umlauts (äöü), but it seems like > convert() does NOT convert the german "ß" / sharp s / ß... PostgreSQL 8.0 does not support UTF8 on Win32. You will need to convert the file before you load it into the database - look at for example the GNU iconv program to do this. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
Magnus Hagander wrote: > PostgreSQL 8.0 does not support UTF8 on Win32. You will need to > convert the file before you load it into the database - look at for > example the GNU iconv program to do this. PostgreSQL does not support UTF8 locales on Windows, but the character conversion should work the same on all platforms, shouldn't it? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
> > PostgreSQL 8.0 does not support UTF8 on Win32. You will need to > > convert the file before you load it into the database - look at for > > example the GNU iconv program to do this. > > PostgreSQL does not support UTF8 locales on Windows, but the > character conversion should work the same on all platforms, > shouldn't it? I thought that was also handled in the locale code. But now that you say it, it's probably not - it should be the same. I'll withdraw my comment then, clearly wrong. Thanks for clearing that up. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1775: UTF8 to ISO does not convert the german "sharp s" (ß)
Magnus Hagander Wrote: > > > PostgreSQL 8.0 does not support UTF8 on Win32. You will need to > > > convert the file before you load it into the database - > look at for > > > example the GNU iconv program to do this. > > > > PostgreSQL does not support UTF8 locales on Windows, but > the character > > conversion should work the same on all platforms, shouldn't it? > > I thought that was also handled in the locale code. But now > that you say it, it's probably not - it should be the same. > I'll withdraw my comment then, clearly wrong. Thanks for > clearing that up. Currently, it is very much so handled by the systems locale support, hence it is handled differently depending on platform. ICU support should make it to 8.1 I hear, which will make the bahaviour uniform. ... John > > > //Magnus > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1775: UTF8 to ISO does not convert the german
> Magnus Hagander wrote: > > PostgreSQL 8.0 does not support UTF8 on Win32. You will need to > > convert the file before you load it into the database - look at for > > example the GNU iconv program to do this. > > PostgreSQL does not support UTF8 locales on Windows, but the character > conversion should work the same on all platforms, shouldn't it? Right. BTW the original bug report sounds interesting to me. The conversion between UTF-8 and ISO 8859-1 is handled by a program logic, not by conversion tables. So if german umlauts are converted fine, there's no reason the conversion for german sharp s does not work. Marcus, Can you give me the exact error message from PostgreSQL when the conversio failed? -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
