Web Connection
Create SQL Server Tables error
Gravatar is a globally recognized avatar based on your email address. Create SQL Server Tables error
  Richard Kaye
  All
  May 30, 2020 @ 07:18am

Hi Rick,

I was playing around with the console on 7.13 and when using the option to create SQL tables I got the following error:

[SQL Server]Column, parameter, or variable #3: Cannot find data type varchar(50). [1526:2715]

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  May 30, 2020 @ 05:05pm

Looks like it's this block that needs to be fixed:

CREATE TABLE [dbo].[wwsession](
	[SessionID] [varchar](17) NOT NULL DEFAULT '',
	[UserId] [varchar](15) NOT NULL DEFAULT '',
	[firston] [datetime] NOT NULL default getdate(),
	[laston] [datetime] NOT NULL default getdate(),
	[vars] [text] NOT NULL DEFAULT '',
	[browser] [varchar](155) NOT NULL DEFAULT '',
	[ip] [varchar](20) NOT NULL DEFAULT '',
	[hits] [int] NOT NULL DEFAULT 0,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

While at it I simplified the entire script removing the explicit constraints:

/****** Object:  StoredProcedure [dbo].[sp_ww_GetNextEvent]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  StoredProcedure [dbo].[sp_ww_IsSession]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_ww_IsSession]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE  [dbo].[sp_ww_IsSession]
  @lcSessionId varchar(20), 
  @ldTimeout DateTime,
  @llIsSession bit OUTPUT
 AS

SELECT SessionId FROM wwSession (NOLOCK) 
   WHERE SessionId= @lcSessionId AND LastOn > @ldTimeout

if @@ROWCOUNT > 0
  Select @llIsSession = 1
else
  Select @llIsSession = 0
' 
END
GO
/****** Object:  StoredProcedure [dbo].[sp_ww_LocateSession]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_ww_LocateSession]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE  [dbo].[sp_ww_LocateSession]
  @lcSessionId varchar(20), 
  @ldTimeout DateTime
 AS

SELECT * FROM wwSession (NOLOCK) 
   WHERE SessionId= @lcSessionId AND 
         LastOn > @ldTimeout


' 
END
GO
/****** Object:  StoredProcedure [dbo].[sp_ww_NewID]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_ww_NewID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[sp_ww_NewID]
  @cName char(30),
  @nRetval int OUTPUT
AS
UPDATE wws_Id 
  SET id = id + 1,
      @nRetval = id + 1
WHERE TableName = @cName

if @@ROWCOUNT < 1
BEGIN
	insert into wws_id (Tablename,Id,pkfield,pktype,pkwidth) values (@cName,1,'','',0)
		set @nRetval = 1
END
' 
END
GO
/****** Object:  Table [dbo].[wwrequestlog]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[wwrequestlog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[wwrequestlog](
	[time] [datetime] NOT NULL DEFAULT getdate(),
	[reqid] [varchar](25) NOT NULL DEFAULT '',
	[script] [varchar](50) NOT NULL DEFAULT '',
	[querystr] [varchar](1024) NOT NULL DEFAULT '',
	[verb] [varchar](10) NOT NULL DEFAULT '',
	[duration] [numeric](7, 3) NOT NULL DEFAULT 0,
	[remoteaddr] [varchar](16) NOT NULL DEFAULT '',
	[memused] [varchar](15) NOT NULL DEFAULT '',
	[error] [bit] NOT NULL DEFAULT 0,
	[reqdata] [text] NOT NULL DEFAULT '',
	[browser] [varchar](255) NOT NULL DEFAULT '',
	[referrer] [varchar](255) NOT NULL DEFAULT '',
	[result] [text] NOT NULL DEFAULT '',
	[account] [varchar](50) NOT NULL DEFAULT ''
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[wws_id]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[wws_id]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[wws_id](
	[tablename] [varchar](50) NOT NULL,
	[id] [int] NOT NULL,
	[pkfield] [varchar](50) NOT NULL DEFAULT '',
	[pkwidth] [int] NOT NULL DEFAULT 0,
	[pktype] [varchar](5) NOT NULL DEFAULT ''
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[wwsession]    Script Date: 12/26/2012 7:38:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[wwsession]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[wwsession](
	[SessionID] [varchar](17) NOT NULL DEFAULT '',
	[UserId] [varchar](15) NOT NULL DEFAULT '',
	[firston] [datetime] NOT NULL default getdate(),
	[laston] [datetime] NOT NULL default getdate(),
	[vars] [text] NOT NULL DEFAULT '',
	[browser] [varchar](155) NOT NULL DEFAULT '',
	[ip] [varchar](20) NOT NULL DEFAULT '',
	[hits] [int] NOT NULL DEFAULT 0,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [SessionID]    Script Date: 12/26/2012 7:38:27 PM ******/
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[wwsession]') AND name = N'SessionID')
CREATE CLUSTERED INDEX [SessionID] ON [dbo].[wwsession]
(
	[SessionID] ASC
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
/****** Object:  Index [LastOn]    Script Date: 12/26/2012 7:38:27 PM ******/
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[wwsession]') AND name = N'LastOn')
CREATE NONCLUSTERED INDEX [LastOn] ON [dbo].[wwsession]
(
	[laston] ASC
) ON [PRIMARY]
GO
Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  May 30, 2020 @ 07:14pm

Is that a replacement for the entire script?

BTW the text datatype is deprecated. You should use varchar(max) or nvarchar(max) if you want to support doublebyte languages.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  May 31, 2020 @ 02:16am

The script replaces the old one - it just inlines the defaults but it ends up doing the same thing in the database.

Ok switched the text fields to varchar(max). I don't see any issue with this here, but I've heard that some of the older ODBC drivers don't work with with this. And the newer drivers have some other issues with some other of the SQL Server special types.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  May 31, 2020 @ 08:51am

Thanks, Rick. I've been using ODBC Driver 17 for SQL Server in all my apps for a while now. We don't do anything fancy with SQL so haven't run into any issues and with newer security protocols in place, the original SQL Server ODBC driver actually can't connect because it doesn't understand TLS 1.2. As far as the text datatype is concerned, MS could remove support for it whenever they feel like it.

I will try out the updated script and let you know if I run into any other issues.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  May 31, 2020 @ 09:16am

One more comment on the script. Unlike what I believe you do with the VFP tables, the script will not update the objects if they exist. I realize their might not be a lot of demand for adding an update in place path so I'll just remove the existing objects manually before I run the updated script.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  May 31, 2020 @ 09:21am

Are wwasyncwebrequest and sp_ww_GetNextEvent deprecated?

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  May 31, 2020 @ 02:30pm

That hasn't been in the SQL template for some time now... had to check my change log though πŸ˜ƒ Didn't remove it in this round of updates - that's been removed in 7.10.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 2, 2020 @ 10:31am

No good deed goes unpunished. πŸ˜ƒ

I kinda knew this from my own desktop application but had forgotten. Turns out that VFP munges the varchar(max) via ODBC and varchar(max) fields need to be cast so it is treated as a memo (text) datatype. So when wwsession.Save runs this code: SCATTER NAME THIS.oData MEMO, the vars data is null. And since the oData object comes from an SP, I'm not sure what options there are with VFP's SQL pass-through to get the results back as a VFP memo field. I'm going to change the vars column back to text for now.

I don't know if this impacts the 2 fields in wwRequestLog as I don't know how thy are used in the framework.

Finally, I also noticed that the TSQLQuery cursor appears to be left open after wwSQL.Execute runs. Not sure if that's by design but thought I'd mention it.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 2, 2020 @ 12:09pm

I'm not seeing that here. I checked both the session and wwRequestLog tables here with varchar(max) and it's working - writing the data into the tables and also getting it back out.

If I recall the issue is that you need a recent ODBC driver. If you have the stock OS drivers it won't work with varchar(max) which I think was one of the reasons I stayed with text originally. Oh well, not changing it back - I suppose people should be using the more recent drivers anyway.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 2, 2020 @ 02:49pm

I'm the guy who wanted you to make it varchar(max).. πŸ˜ƒ

What driver are you using? I'm using ODBC Driver 17 for SQL Server.

Driver={ODBC Driver 17 for SQL Server};server=....;uid=.....;pwd=......;database=....;Network Library=DBMSSOCN

According to the ODBC Manager, the version I have installed is 2017.175.01.01.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 2, 2020 @ 07:08pm

Yeah I know - I'll remember that, man! Next time I see you!!! πŸ˜„

It's working for me - I'm getting SQL data into all the varchar(max) fields in a new DB I set up with the Wizard, so clearly it's possible. But it's ODBC hell because there some features that work with one driver and not another and vice versa. Lots of issues - a few of them described on the Fox Wiki I think.

IAC... I think I'm using the same driver as you, although I have two of them installed (13 and 17). Could be that the one I'm running is 13 but I'll explicitly try specifying the version and see what happens.

Note for some of the fields in the request table don't get set unless some compiler flags are set for extended logging.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 2, 2020 @ 07:27pm

So I can't actually get any of the other ODBC drivers to work. For me only the default {SQL Server} driver is working to make a connection.

Running these 3 commands:

loSql = CREATEOBJECT("wwSql")
? loSql.Connect("driver={SQL Server Native Client 11.0};server=(local);database=WestWind;integrated security=true")
? loSql.Connect("driver={ODBC Driver 17 for SQL Server};server=(local);database=WestWind;integrated security=true")
? loSql.Connect("driver={SQL Server};server=(local);database=WestWind;integrated security=true")

Only the last one works even though I have various drivers installed:

No idea why the other ones aren't working.

IAC - I'm using the plain SQL Server variant and that's what's working with the varchar(max) data.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Rick Strahl
  Jun 2, 2020 @ 07:34pm

Ok looks like this works:

* Native Client 11
? loSql.Connect("Provider=SQLNCLI11;server=(local);database=WestWind;integrated security=true")

And that seems to work too with the varchar(max) values.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 3, 2020 @ 06:19am

Well I was hoping to see you in November at SWFOX but that will have to wait for 2021. πŸ˜ƒ

I think I have a better explanation for why it's not working with varchar(max) and will test it out shortly and let you know.

What version of SQL Server are you testing against? As mentioned, I've been using ODBC Driver 17 in my apps for a while. I have found that it requires TLS 1.2 and it will balk if deprecated versions of the TLS protocol are in use.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Tore Bleken
  Rick Strahl
  Jun 3, 2020 @ 06:58am

I seem to remember that the earlier MSSQL drivers which worked with varchar(max) had problems with another field type (datetime??). I will run some tests when I have the time.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Richard Kaye
  Jun 3, 2020 @ 12:19pm

It's weird that the SQL Server driver, which was released with SQL 7, iirc, handles the conversion from varchar(max) to memo. The native client (11) is also pretty old but it has OLEDB built into it so perhaps that's why it works.

In my environment, only TLS 1.2 is enabled on the SQL Server so the original client is being rejected because it does not know the right handshake. Still poking around at this. Playing with setting MapVarchar via CURSORSETPROP seems to get the right data type but no data.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 3, 2020 @ 12:22pm

And looking at that "Provider=" syntax I think that means it is using OLEDB and not ODBC.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Richard Kaye
  Jun 3, 2020 @ 03:24pm

Ok, I've run some tests on SQL Server 2017 using 3 different ODBC drivers and VFP.

Drivers:

  • SQL Server Native Client 11.0
  • ODBC Driver 13 for SQL Server
  • SQL Server

I created the table with various datatypes, but certainly not all that VFP would never be able to consume.

`CREATE TABLE [dbo].[odbc_test](
[biginteger] [bigint] IDENTITY(1,1) NOT NULL,
[date_1] [date] NULL,
[datetime2_1] datetime2 NULL,
[datetime_1] [datetime] NULL,
[text_1] [text] NULL,
[v_max] varchar NULL,
[v_900] varchar NULL,
[v_800] varchar NULL,
[c_10] char NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[odbc_test] ADD CONSTRAINT [DF_Table_1_date1] DEFAULT (getdate()) FOR [date_1]
GO
ALTER TABLE [dbo].[odbc_test] ADD CONSTRAINT [DF_odbc_test_datetime2_1] DEFAULT (getdate()) FOR [datetime2_1]
GO
ALTER TABLE [dbo].[odbc_test] ADD CONSTRAINT [DF_odbc_test_datetime_1] DEFAULT (getdate()) FOR [datetime_1]
GO

insert into odbc_test (text_1,v_max,v_800,v_900,c_10)
values
(
'this is a text column',
'this is a varchar(max) column',
'this is a varchar(800) column',
'this is a varchar(900) column',
'char(10)'
)`

The results (you will have to read to understand what works and what does not work. Basically, bigint does not work at all. Date and Datetime2 might or might not work. Varchar(max) works only with the oldest driver 'SQL Server'.

NOTE: I did not perform any inserts or updates. I assume they would work. I only did a SQL Select statement.

SQL Server Native Client 11.0

Structure for table:
Number of data records: 5
Date of last update: / /
Memo file block size: 64
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 BIGINTEGER Character 20 No
2 DATE_1 Date 8 Yes
3 DATETIME2_1 DateTime 8 Yes
4 DATETIME_1 DateTime 8 Yes
5 TEXT_1 Memo 4 Yes
6 V_MAX Character 0 Yes
7 V_900 Memo 4 Yes
8 V_800 Memo 4 Yes
9 C_10 Character 10 Yes

TYPE, NAME, VALUE

C biginteger 3
D date_1 06/03/20
T datetime2_1 06/03/20 02:19:33 PM
T datetime_1 06/03/20 02:23:53 PM
C text_1 this is a text column
C v_max EMPTY C v_800 this is a varchar(800) column
C v_900 this is a varchar(900) column
C c_10 char(10)

ODBC Driver 13 for SQL Server

Structure for table:
Number of data records: 5
Date of last update: / /
Memo file block size: 64
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls
1 BIGINTEGER Character 20 No
2 DATE_1 Date 8 Yes
3 DATETIME2_1 DateTime 8 Yes
4 DATETIME_1 DateTime 8 Yes
5 TEXT_1 Memo 4 Yes
6 V_MAX Character 0 Yes
7 V_900 Memo 4 Yes
8 V_800 Memo 4 Yes
9 C_10 Character 10 Yes

TYPE, NAME, VALUE

C biginteger 3
D date_1 06/03/20
T datetime2_1 06/03/20 02:19:33 PM
T datetime_1 06/03/20 02:23:53 PM
C text_1 this is a text column
C v_max EMPTY
C v_800 this is a varchar(800) column
C v_900 this is a varchar(900) column
C c_10 char(10)

Sql Server

Structure for table:
Number of data records: 5
Date of last update: / /
Memo file block size: 64
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls
1 BIGINTEGER Character 20 No
2 DATE_1 Character 10 Yes
3 DATETIME2_1 Character 27 Yes
4 DATETIME_1 DateTime 8 Yes
5 TEXT_1 Memo 4 Yes
6 V_MAX Memo 4 Yes
7 V_900 Memo 4 Yes
8 V_800 Memo 4 Yes
9 C_10 Character 10 Yes

TYPE, NAME, VALUE

C biginteger 3
C date_1 2020-06-03
C datetime2_1 2020-06-03 14:19:33.0933333
T datetime_1 06/03/20 02:23:53 PM
C text_1 this is a text column
C v_max this is a varchar(max) column
C v_800 this is a varchar(800) column
C v_900 this is a varchar(900) column
C c_10 char(10)

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Jeff L
  Jun 3, 2020 @ 06:52pm

Jeff,

Thanks for taking the time to try this out - this would be great to wrap up into some sort of small FoxUnit test suite so this could be easily be re-run with different providers. This seems highly useful given all the incompatibilities.

So I can only get the Sql Server and Native provider to work on my end. But both of these work with varchar(max) both for insert and SELECT results.

if I run this where vars is defined as varchar(max):

loSql = CREATEOBJECT("wwSql")
? loSql.Connect("Provider=SQLNCLI11;server=(local);database=WestWind;integrated security=true")

PRIVATE pcId
pcId = "312132231"

loSQL.Execute("select * from wwSession where SessionId = ?pcId")
IF (loSql.nAffectedRecords < 1)
	loSql.Execute([INSERT INTO wwSession(sessionId,vars) VALUES (?pcId,'<value1>1312</value1><value2>Some long string of text</value2>')])
	? loSql.cErrorMsg
ENDIF
	
loSQL.Execute("select * from wwSession where SessionId = ?pcId")

BROWSE nowait

I get:

Notice that vars is turned into a memo even though the result is less than 255 chars so the driver seems to understand the varchar(max) mapping.

Oddly I can't seem to reference my ODBC drivers by name other than for Sql Server - the others I have to use Provider Name for.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 3, 2020 @ 06:55pm

And looking at that "Provider=" syntax I think that means it is using OLEDB and not ODBC.

I don't think so - SQL Passthrough can't use OleDb. I think the DataAdapter is the or the raw OleDb COM interfaces is the only way you can use the OleDb driver in VFP.

Provider is just another, non-descriptive and more concise identifier for the driver. Although I don't know where you can actually find that value - I looked it up on connectionstrings.com.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Rick Strahl
  Jun 3, 2020 @ 07:50pm

I'll see what I can dig up for tomorrow.

Keep in mind that I did a 'list structure' to show what VFP maps to via ODBC and that in both cases other than 'SQL Server' (very old driver) they both mapped varchar(max) as a VFP character with 0 width, while 'SQL Server' driver mapped as memo. Wow!

Yeah, don't know about that 'Provider' naming in the connection string.

Here's my connection strings:

connstr1 = "Driver={SQL Server Native Client 11.0};Server=.;Database=junk;Trusted_Connection=yes;"
connstr2 = "Driver={ODBC Driver 13 for SQL Server};Server=.;Database=junk;Trusted_Connection=yes;"
connstr3 = "Driver={SQL Server};Server=.;Database=junk;Trusted_Connection=yes;"

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Jeff L
  Jun 4, 2020 @ 12:02am

Retried this and seeing same as you. Only the Sql Server driver seems to be able to read the varchar(max) value as a memo - the others return an empty string.

This makes it work, but that sucks for a lot of things.

loSQL.Execute("select CAST(vars as Memo) as Vars2 from wwSession where SessionId = ?pcId")

I guess I've been using the Sql Server driver and keeping the data models as simple as possible, but for best compatibility it seems the old driver is probably the best choice unless you're explicitly accessing some of the new features. And even then maybe those can be wrapped into stored procs.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Tore Bleken
  Rick Strahl
  Jun 4, 2020 @ 02:24am

I just ran e series of tests and can confirm the same observation. I updated my base MSSQL class to use Driver = SQL Server, and all my forms ran without any problems, both with Text and Varchar(max) fields. Now my next step is to change all my Text fields into Varchar(max).

Thanks lot for your time on this matter. This has been in the back of my head for years, since I knew that sooner or later support for Text fields will be removed.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Tore Bleken
  Jun 4, 2020 @ 07:12am

A bit of a drag that MS decided that the code that maps varchar(max) to memo got dropped from their current drivers but that's life in the forgotten land of VFP. Also it's been my experience that the original SQL driver will not be able to connect to newer SQL instances if older deprecated security protocols are disabled, as they should be.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 4, 2020 @ 07:28am

I'm making assumptions because I'm a charter member of the Words Have Meaning(R) club. πŸ˜ƒ ODBC uses "drivers" and OLEDB uses "providers".

I just stepped through your connect method. You're actually inserting the driver directive with the original SQL Server driver when it's not in the connect string. As best I can tell the Provider directive is just being ignored. However, this will work if you use the actual driver name: Driver={SQL Server Native Client 11.0};server=my server name;database=my session db;Trusted_Connection=yes Note that I changed integrated security to Trusted_Connection to allow Window to handle the auth but it also connects using uid/pwd.

As a side note, I have used the provider directive in a classic ASP environment which is using ADO and therefore speaks OLEDB.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Rick Strahl
  Jun 4, 2020 @ 08:09am

Using the oldest driver 'SQL Server' gives the advantage of being able to use varchar(max), but Date and Datetime2 will come down as Character and not as actual date types.

It's a hard choice to make, but I would prefer for most cases to be able to use Date and Datetime2. We went with SQL 'Text' instead of varchar(max). Yes, SQL 'Text' will someday be deprecated.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Jeff L
  Jun 4, 2020 @ 08:52am

Yes, the path of least resistance for me is to also stick with text for now.

A related question, in wwsql there's this little snippet:

IF wwVFPVERSION > 8  
	*** Map binary fields to BLOB/VarBinary
	CURSORSETPROP("MapBinary",.T.,0)  
ENDIF  

According to the VFP help, there is also a property directive for MapVarchar basically using the same syntax. Perhaps that should be added to this codeblock? I do have that in my testing code, but the length defined varchar fields are being properly mapped in the VFP cursor regardless of that setting so not sure if there's any reason to add it. I don't use blobs or binaries in mhy DBs so really have no idea if MapNBinary does anything either.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 4, 2020 @ 11:22am

MapBinary definitely works with binary data - otherwise you have all sorts of stuff you have to do convert the data which is slow on the FoxPro end. MapBinary directly maps to a FoxPro blob field.

Not sure what MapVarChar does. Never actually used it but as you point out it doesn't seem to have an affect on the varchar(max) mapping issue as it probably pre-dates that data type (varchar on SQL Server used to be limited to 4000 characters or 2000 unicode characters).

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 4, 2020 @ 12:09pm

OK. I checked this again after making sure my environment was clean and I was wrong about it not seeming to do anything. In a clean environment, I executed the standard SP against my SQL session table with MapVarchar set to the default of .F.. I took that one record and used COPY TO to create a DBF on disk and did a MODIFY STRUCTURE. Here's what that looks like:

Then I restarted and did the same thing with MapVarChar set to .T..

So I would say it should be set in the same place where MapBinary gets set.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Richard Kaye
  Jun 4, 2020 @ 02:09pm

One thing to watch out for when using MapVarchar = .t. is you probably want to make sure the data in SQL in varchar columns is trimmed, so when it comes down to VFP you can take advantage of the trimmed varchar field. Same with putting trimmed data into a VFP varchar field and sending it up to SQL or whatever DB you're using. Basically, you want consistency and trimmed strings when working with varchars or there's no real sense in using them.

Keep in mind that VFP varchars don't save any diskspace like in SQL Server. If your VFP varchar definition is 50 wide and you have 10 characters of data it will use 50 bytes of diskspace. VFP's records are always fixed length.

But, I always used VFP varchar as much as possible. I liked the fact that you didn't need to trim all the time.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Jeff L
  Jun 4, 2020 @ 02:19pm

MapVarchar actually seems to handle that, Jeff. In the tests I ran earlier, my test SQL data had a 10 character sessionID. WIth MapVarchar set to .T., VFP returned 10 for LEN(results.sessionID) of that field. With it set to .F., it returns the full field width of 17.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Richard Kaye
  Jun 4, 2020 @ 02:32pm

I don't think that this really matters unless your SQL data come from Fox data that has spaces in it originally.

I don't think it has any effect on the varchar(max) issue since that has to map into a memo. It looks like FoxPro correctly creates the target field but the data is not being loaded into it unless you explicitly CAST() the field to a Memo.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Jun 4, 2020 @ 02:43pm

Correct. Not directly related to the text/varchar(max) problem. But it does highlight that VFP treats varchar as varchar and not char when mapping is enabled and to Jeff's point he does not have to trim text fields when sending them back to SQL.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Richard Kaye
  Jun 4, 2020 @ 03:15pm

It's been awhile since I've done VFP coding and I am really rusty on my Set Varcharmapping and could not understand why my SQL PassThrough Selects of tables in SQL with varchars were coming down as Character fields in VFP cursors.

Ok, after becoming woke, I realized it is the cursorsetprop("MapVarchar",.t.,0) with the following from HELP:

MapVarchar is read/write for nWorkArea set to 0, read-only for SQL Pass-Through cursors, and invalid for table cursors (nWorkArea equal to or greater than 1).

And then my SQL Server cursors had been mapped to varchars as expected.

If your WC sessionid was trimmed then Rick's code that creates it or puts it into SQL was responsible.

But, where I used to work sometime ago I switched the cursorsetprop("MapVarchar",.t.,0) (and it's probably datasession specific) and we had some problems with untrimmed legacy data that I believe I trimmed.

BTW, I'd ask Rick what kind of problems might be encountered in WC if you turn MapVarchar on via cursorsetprop().

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Jeff L
  Jun 4, 2020 @ 06:57pm

I'm not going to change that setting because this is sure to break people as it changes behavior of strings returned in cursors (basically trimming strings). It's easy enough to set this manually or subclass wwSql to add that if your application prefers this as a default. If your SQL data is varchar in the first place (ie. not padded out) there isn't going to be any difference in behavior anyway. So unless you're dealing with FoxPro legacy data in a SQL database this won't really make a difference.

This is totally unrelated to the issue we're discussing here anyway as this property set only affects how string data is generated into the cursor/table from SQL queries - either as char() or varchar(). But it doesn't affect how fields are mapped and certainly doesn't help with the varchar(max) issue.

If I were starting with this today that would be a good idea. In fact it would be a good idea globally to use varchar, but at this time this is likely problematic to change the default.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Rick Strahl
  Nov 24, 2020 @ 07:17am

Just ran across this again, and thought I'd add a tidbit for the historical record. I wanted to add a SQL trigger to populate another table from a table that has text columns. It turns out that triggers in current versions of SQL do not support the use of text columns in its inserted/deleted temp objects, and it would not allow me to add my trigger. No problem says I, I'll just change all my text columns to varchar(max). But when I did that, I broke my update into the table I was adding the trigger to because of the messed up datatype mapping using driver 17.

DO WHILE NOT concussed  
  BANG HEAD ON TABLE  
ENDDO  

Send flowers to...

The moral of the story is text columns are being deprecated. Slowly. And not always in immediately obvious ways...

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Jeff L
  Richard Kaye
  Nov 24, 2020 @ 02:27pm

Wow, that is very interesting that you cannot use TEXT in a trigger and that's how MS is beginning to deprecate the TEXT datatype.

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Rick Strahl
  Jeff L
  Nov 24, 2020 @ 03:33pm

Yeah that's sneaky as heck...

I'd argue that this sort of behavior is almost worse than downright removing the functionality altogether.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Richard Kaye
  Jeff L
  Nov 24, 2020 @ 04:19pm

Well, it's apparently been this way since SQL 2000 (no text, ntext or image types) so I may have overstated the case. But varchar(max) and nvarchar(max) are supported. So the real PITA here is MS dropping the proper datatype mapping in their newest drivers. Ah well...

Gravatar is a globally recognized avatar based on your email address. re: Create SQL Server Tables error
  Daniel Gramunt
  Rick Strahl
  Jan 4, 2024 @ 07:07am

Very old thread but I thought I'd share some experience with SQL Server ODBC drivers. Like most of you, I have been using the old legacy driver "SQL Server". It handled varchar(max) correctly and I could live with the issue of date and datetime2.

However, recently I started noticing extremely slow performance with SQL Server 2022 and the legacy driver. When playing around with the newer drivers from Microsoft there was no performance issue. However, like you all noticed, there is the issue with varchar(max) which is a show stopper.

I did some research and came across an ODBC driver from Devart. I used their evaluation version and noticed that this driver handles varchar(max) as well as date and datetime2 correctly. Performance is also on par with the latest MS drivers.

I share below the outcome of three different drivers I tested.

I bought the Devart driver and so far am very happy with it. FWIW, they currently offer 30% discount until Jan 11th.

Devart ODBC driver for SQL Server

Here is the test data I used:

      CREATE TABLE ##OdbcTest (nPkId INT NOT NULL IDENTITY(1,1), 
                                    cDescription VARCHAR(100),
                                    dDate DATE,
                                    tTime TIME,
                                    tDateTime2 DATETIME2,
                                    tDateTimeOffset DATETIMEOFFSET,
                                    nBigint bigint,
                                    cVarcharMax VARCHAR(max), 
                                    bVarbinaryMax VARBINARY(max), 
                                    CONSTRAINT _ODBCTest_nPkId PRIMARY KEY (nPkId));

*-------------------------------------------------------------------------------------------------- *-- 1) Legacy SQL Server ODBC driver *--------------------------------------------------------------------------------------------------

Alias: _SQL_SERVER Structure for table: C:\TEMP\0001GMX500VT.TMP Number of data records: 10 Date of last update: . . Memo file block size: 64 Code Page: 1252

   FIELD FIELD_NAME      FIELD_TYPE FIELD_LEN FIELD_DEC FIELD_NULL FIELD_NOCP
       1 NPKID           I                  4         0 .F.        .F.       
       2 CDESCRIPTION    C                100         0 .T.        .F.       
       3 DDATE           C                 10         0 .T.        .F.
       4 TTIME           C                 16         0 .T.        .F.       
       5 TDATETIME2      C                 27         0 .T.        .F.       
       6 TDATETIMEOFFSET C                 34         0 .T.        .F.       
       7 NBIGINT         C                 20         0 .T.        .F.       
       8 CVARCHARMAX     M                  4         0 .T.        .F.       
       9 BVARBINARYMAX   G                  4         0 .T.        .T.       

*-------------------------------------------------------------------------------------------------- *-- SQLGetProp() *-------------------------------------------------------------------------------------------------- Asynchronous [r-w] = .F. (Default = .F.) BatchMode [r-w] = .T. (Default = .T.) ConnectBusy [r-o] = .F. ConnectString [r-o] = DRIVER=SQL Server;SERVER=ServerName;UID=UserName;PWD=XXX;APP=Microsoft Visual FoxPro;DATABASE=DatabaseName ConnectTimeOut [r-w] = 5 (Default = 15) DataSource [r-w] = DispLogin [r-w] = 3 (Default = 1) DispWarnings [r-w] = .F. (Default = .F.) IdleTimeout [r-w] = 0 (Default = 0) ODBChdbc [r-o] = 44086528 ODBChstmt [r-o] = 44283328 PacketSize [r-w] = 4096 (Default = 4096) Password [r-o] = QueryTimeOut [r-w] = 0 (Default = 0) Transactions [r-w] = 1 (Default = 1) UserId [r-o] = WaitTime [r-w] = 100 (Default = 100)

*-------------------------------------------------------------------------------------------------- *-- 2) ODBC Driver 18 for SQL Server *--------------------------------------------------------------------------------------------------

Alias: _ODBC_DRIVER_18_FOR_SQL_SERVER Structure for table: C:\TEMP\0001GMX500WH.TMP Number of data records: 10 Date of last update: . . Memo file block size: 64 Code Page: 1252

   FIELD FIELD_NAME      FIELD_TYPE FIELD_LEN FIELD_DEC FIELD_NULL FIELD_NOCP
       1 NPKID           I                  4         0 .F.        .F.       
       2 CDESCRIPTION    C                100         0 .T.        .F.       
       3 DDATE           D                  8         0 .T.        .F.       
       4 TTIME           C                 16         0 .T.        .F.       
       5 TDATETIME2      T                  8         0 .T.        .F.       
       6 TDATETIMEOFFSET C                 34         0 .T.        .F.       
       7 NBIGINT         C                 20         0 .T.        .F.       
       8 CVARCHARMAX     C                  0         0 .T.        .F.       => Show stopper (Field Len = 0)
       9 BVARBINARYMAX   M                  4         0 .T.        .T.       

*-------------------------------------------------------------------------------------------------- *-- SQLGetProp() *-------------------------------------------------------------------------------------------------- Asynchronous [r-w] = .F. (Default = .F.) BatchMode [r-w] = .T. (Default = .T.) ConnectBusy [r-o] = .F. ConnectString [r-o] = DRIVER=ODBC Driver 18 for SQL Server;SERVER=ServerName;UID=UserName;PWD=XXX;APP=Microsoft Visual FoxPro;DATABASE=DatabaseName;Encrypt=No; ConnectTimeOut [r-w] = 5 (Default = 15) DataSource [r-w] = DispLogin [r-w] = 3 (Default = 1) DispWarnings [r-w] = .F. (Default = .F.) IdleTimeout [r-w] = 0 (Default = 0) ODBChdbc [r-o] = 44186648 ODBChstmt [r-o] = 44189064 PacketSize [r-w] = 4096 (Default = 4096) Password [r-o] = QueryTimeOut [r-w] = 0 (Default = 0) Transactions [r-w] = 1 (Default = 1) UserId [r-o] = WaitTime [r-w] = 100 (Default = 100)

*-------------------------------------------------------------------------------------------------- *-- 3) Devart ODBC Driver for SQL Server *--------------------------------------------------------------------------------------------------

Alias: _DEVART_ODBC_DRIVER_FOR_SQL_SERVER Structure for table: C:\TEMP\0001GMX500WB.TMP Number of data records: 10 Date of last update: . . Memo file block size: 64 Code Page: 1252

   FIELD FIELD_NAME      FIELD_TYPE FIELD_LEN FIELD_DEC FIELD_NULL FIELD_NOCP
       1 NPKID           I                  4         0 .F.        .F.       
       2 CDESCRIPTION    C                100         0 .T.        .F.       
       3 DDATE           D                  8         0 .T.        .F.       
       4 TTIME           T                  8         0 .T.        .F.       
       5 TDATETIME2      T                  8         0 .T.        .F.       
       6 TDATETIMEOFFSET T                  8         0 .T.        .F.       
       7 NBIGINT         C                 20         0 .T.        .F.       
       8 CVARCHARMAX     M                  4         0 .T.        .F.       
       9 BVARBINARYMAX   G                  4         0 .T.        .T.       

*-------------------------------------------------------------------------------------------------- *-- SQLGetProp() *-------------------------------------------------------------------------------------------------- Asynchronous [r-w] = .F. (Default = .F.) BatchMode [r-w] = .T. (Default = .T.) ConnectBusy [r-o] = .F. ConnectString [r-o] = DRIVER=Devart ODBC Driver for SQL Server;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=XXX;Auto Translate=False ConnectTimeOut [r-w] = 5 (Default = 15) DataSource [r-w] = DispLogin [r-w] = 3 (Default = 1) DispWarnings [r-w] = .F. (Default = .F.) IdleTimeout [r-w] = 0 (Default = 0) ODBChdbc [r-o] = 44184928 ODBChstmt [r-o] = 44185768 PacketSize [r-w] = 4096 (Default = 4096) Password [r-o] = QueryTimeOut [r-w] = 0 (Default = 0) Transactions [r-w] = 1 (Default = 1) UserId [r-o] = WaitTime [r-w] = 100 (Default = 100

© 1996-2024