[egenix-users] Empty strings and executedirect

[Tommi Auvinen]

I dug a bit deeper and enabled the ODBC driver trace, and noticed that
all three data lengths are 0 when handling empty string. I tried
changing these values (so that buf len is at least 1) so that
terminating NULL goes to the driver as well, but it did no help. But by
doing the following (just to test, not complete or even right
solution...) just before SQLBindParameters helped:

if (var->ctype==SQL_C_CHAR && var->sqllen==0) {
	var->sqltype=SQL_VARCHAR;
}

So it seems that CHAR(0) cannot be interpreted (as one would expect) by
ODBC driver.

In fact, the column description is still 8000 characters (in schema the
column lengths are only some tens of chars), but now the actual data is
OK, and SQL Server can do the varchar-->char conversions:
exec sp_executesql N'INSERT INTO Testi (C, VC, NC, NVC, I) VALUES
(@P1, at P2, at P3, at P4, at P5)',N'@P1 varchar(8000), at P2 varchar(8000), at P3
varchar(8000), at P4 varchar(8000), at P5 int','','','','',0

R:TAu

PS: The table def:
/****** Object:  Table [dbo].[Testi]    Script Date: 05/16/2007 15:34:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Testi](
	[C] [char](10) NULL,
	[NC] [nchar](10) NULL,
	[VC] [varchar](50) NULL,
	[NVC] [nvarchar](50) NULL,
	[TimeStamp] [timestamp] NULL,
	[I] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com] 
Sent: 15. toukokuuta 2007 0:40
To: Tommi Auvinen
Cc: egenix-users at egenix.com
Subject: Re: [egenix-users] Empty strings and executedirect

On 2007-05-14 16:01, Tommi Auvinen wrote:
> Environment: Windows client and SQLServer. Python 2.43 and mxODBC
2.0.7.
> 
>  
> 
> When I do the following insert using execute direct (C = char,
> VC=varchar etc.)
> 
> "INSERT INTO Testi (C, VC, NC, NVC) VALUES (?,?,?,?)"
> 
>  
> 
> using parameters ("", "",""; "")
> 
>  
> 
> the resulting insert is as follows
> 
> exec sp_executesql N'INSERT INTO Testi (C, VC, NC, NVC) VALUES
> (@P1, at P2, at P3, at P4)',N'@P1 char(8000), at P2 char(8000), at P3 char(8000), at P4
> char(8000) ...
> 
> ...and the data for each fields really is 8000 spaces.
> 
>  
> 
> The same using strings with one space (" ", " "," "; " ") works as
> expected: 
> 
> exec sp_executesql N'INSERT INTO Testi (C, VC, NC, NVC) VALUES
> (@P1, at P2, at P3, at P4)',N'@P1 char(1), at P2 char(1), at P3 char(1), at P4
char(1)','
> ',' ',' ',' '
> 
>  
> 
> So, am I missing something and or is this a bug? 

This looks a lot like a bug in the ODBC driver.

It seem to convert the empty data to a char(8000) column (which
is padded with spaces in SQL Server and many other database backends).

OTOH, you could also view it as bug in the way the one character
data is handled. Depends on the table schema. If this is indeed
a char(8000) column, then the one character data should also be
padded to 8000 characters.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 14 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611