×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using Sql Server AlwaysEncrypted back-end for VFP application
2

Using Sql Server AlwaysEncrypted back-end for VFP application

Using Sql Server AlwaysEncrypted back-end for VFP application

(OP)
We're investigating the possibility of using Sql Server 2017's AlwaysEncrypted functionality in our VFP 9 based application.

Enabling this involves swapping out the connection string form Driver={Sql Server} for something newer which supports Always Encrypted, like Sql Server Native Client 11.0 which is ultimately using an ODBC 17 connection.

This works fine, but we are of course running into the varchar(max) issue described in thread184-1776880: Microsoft SQL Server Native Client 11.0.

We've even tried the Devart adapter referenced in that thread, but it does not appear to support the Trusted_Connection=yes; connection attribute, which is a non-starter due to how our application is structured. Even if it did, it doesn't appear to support the ColumnEncryption=Enabled; connection attribute anyhow.

Thus, this post.

Has anyone had any success with a more modern driver which supports the following?
  1. varchar(max)
  2. Trusted_Connection=Yes
  3. Column_Encryption=Enabled
Any help would be appreciated!

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

Nqramjets,

VFP Advanced, a patched version of the VFP runtimes by Chuanbing Chen, claims to have fixed the Varchar(Max) interoperability problem with the Native Client drivers.

Into at http://www.baiyujia.com/vfpadvanced/f_vfpa_about.a...

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

According to this (client development / Using Always Encrypted with the ODBC Driver) https://docs.microsoft.com/en-us/sql/connect/odbc/...
You will need to use the most modern ODBC driver series Microsoft also made for PHP: ODBC Driver 13 or 17. I have SQL Server 2018 and can connect with the ODBC Driver 13 for SQL Server. The version 17 driver fails with an error message about SQLAllocHandle.

Haven't investigated further, but at least you get a connection with the version 13 driver and that's also capable to work with Always Encrypted.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

An alternative would be to call the ODBC API directly, in particular, if getting Varchar(MAX) columns could be isolated in a separate statement from the rest of columns.

This requires further development, but for now, it just tries to prove the base concept:

CODE --> VFP

* complete / replace as appropriate
m.hODBC = SQLSTRINGCONNECT("Driver={ODBC Driver 13 for SQL Server};Server=####;Database=####;UID=####;PWD=####"))

* first, the standard behavior returning a C(0) result
USE IN SELECT("curTeste")

SQLEXEC(m.hODBC, "SELECT CAST('Hello, world' AS Varchar(MAX)) AS colVarCharMax;", "curTeste")

SELECT curTeste
BROWSE

MESSAGEBOX(LEN(curTeste.colVarCharMax))

* we are going to use the SQL API directly
SQLAPI()

USE IN SELECT("curTeste2")

? SQLGETVARMAX(m.hODBC, "SELECT CAST('Hello, world' AS Varchar(MAX)) AS colVarCharMax;", "curTeste2")

SELECT curTeste2
BROWSE

MESSAGEBOX(LEN(curTeste2.colVarCharMax))

SQLDISCONNECT(m.hODBC)


#define	SQL_SUCCESS					0
#define	SQL_SUCCESS_WITH_INFO	1
#define	SQL_NO_DATA					100
#define	SQL_ERROR					-1
#define	SQL_SUCCEEDED				(INLIST(m.ReturnCode, SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA))
#define	SQL_HANDLE_STMT			3
#define	SQL_C_DEFAULT				99					
#define	SQL_MAX_VARCHAR			(2^24-32)

FUNCTION SQLGETVARMAX (ConnectionHandle AS Integer, Statement AS String, CursorName AS String) AS Integer

	LOCAL ReturnCode AS Integer
	LOCAL hStmt AS Integer
	LOCAL ReturnVarchar AS String
	LOCAL ReturnLen AS Integer

	IF PCOUNT() < 2
		m.CursorName = "curResult"
	ENDIF

	m.ReturnVarchar = REPLICATE(CHR(0), SQL_MAX_VARCHAR)

	m.hStmt = 0
	m.ReturnCode = SQLAPI_AllocStmt(SQLGETPROP(m.ConnectionHandle, "ODBChdbc"), @m.hStmt)
	IF !SQL_SUCCEEDED
		RETURN SQL_ERROR
	ENDIF

	m.ReturnCode = SQLAPI_ExecDirect(m.hStmt, m.Statement, LEN(m.Statement))
	IF SQL_SUCCEEDED

		m.ReturnCode = SQLAPI_Fetch(m.hStmt)
		IF SQL_SUCCEEDED

			CREATE CURSOR (m.CursorName) (colVarcharMax Memo)

			IF m.ReturnCode = SQL_NO_DATA
				SQLAPI_FreeHandle(SQL_HANDLE_STMT, m.hStmt)
				RETURN SQL_SUCCESS
			ENDIF

			m.ReturnLen = 0
			m.ReturnCode = SQLAPI_GetData(m.hStmt, 1, SQL_C_DEFAULT, @m.ReturnVarchar, SQL_MAX_VARCHAR, @m.ReturnLen)

			IF SQL_SUCCEEDED
				INSERT INTO (m.CursorName) VALUES (LEFT(m.ReturnVarchar, m.ReturnLen))
			ENDIF
		ENDIF
	ENDIF

	SQLAPI_FreeHandle(SQL_HANDLE_STMT, m.hStmt)
	RETURN IIF(SQL_SUCCEEDED, SQL_SUCCESS, SQL_ERROR)

ENDFUNC

FUNCTION SQLAPI ()

	DECLARE SHORT SQLAllocStmt IN odbc32 AS SQLAPI_AllocStmt ;
		INTEGER ConnHandle, ;
		INTEGER @ StatementHandle

	DECLARE SHORT SQLFreeHandle IN odbc32 AS SQLAPI_FreeHandle ;
		SHORT HandleType, ;
		INTEGER Handle

	DECLARE SHORT SQLExecDirect IN odbc32 AS SQLAPI_ExecDirect ;
		INTEGER StatementHandle, ;
		STRING StatementText, ;
		INTEGER TextLength

	DECLARE SHORT SQLFetch IN odbc32 AS SQLAPI_Fetch ;
		INTEGER StatementHandle

	DECLARE SHORT SQLGetData IN odbc32 AS SQLAPI_GetData ;
		INTEGER StatementHandle, ;
		SHORT ColParamNum, ;
		SHORT TargetType, ; 
		STRING @ TargetValuePtr, ;
		INTEGER BufferLength, ;
		INTEGER @ LenIndPtr

ENDFUNC 

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

No, you just define your result structure with cursoradapter. I'm more worried why driver 17 doesn't work and how long driver 13 will be sufficient, not even knowing what you get from the encryption features. I also don't see how native client 11.0 is ultimately using an ODBC Version 17 connection. Native Client is its own driver series.

But the varchar max problem is solved with cursoradapters CursorSchema and UseCursorSchema, of course:

CODE

Local lnH
lnH = SQLStringConnect("Driver={ODBC Driver 13 for SQL Server};Server=(local);Trusted_Connection=yes;")
If lnH<0
   AError(laError)
   Set Step On 
EndIf 

Local loCUA as CursorAdapter 

loCUA = CreateObject("cursoradapter")
loCUA.DataSourceType = "ODBC" 
loCUA.DataSource = lnH
loCUA.CursorSchema = "col1 M"
loCUA.UseCursorSchema = .T.
loCUA.SelectCmd = "SELECT CAST('Hello, world' AS Varchar(MAX)) as col1;"
loCUA.Alias = "crsResult"
Use in Select(loCUA.Alias)
loCUA.cursorfill()
Browse nowait

SQLDisconnect(lnH) 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

Olaf, this is so great to know.

I tried with ODBC 17* and it's working fine. Can you post a snippet that can produce the error you're experiencing?~

* Update: but targetting an SQL 2012, not an SQL 2018...

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

The Cursorschema solution is not new.

The error I get with Microsoft SQL Server 2017 (14.0.2027.2) and comes up connecting to that server when I just replace 13 with 17, no other code changes. AERROR() reports SQLSTATE IM004, SQLAllocHandle on SQL_HANDLE_ENV failed, as stated already (less detailed). There's nothing more to it.

The driver I installed is MSODBC17.DLL (32bit) Version 2917.174.01.01 from 07/24/2019, I just installed this a few days ago. And I'm on Win 10 (Home) build 1903 and use VFP 09.00.0000.7423 (SP2, latest hotfix).

Bye. Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Using Sql Server AlwaysEncrypted back-end for VFP application

Hello,

For the varchar(max) problem I remember :
- Using cast(varcharfield as text)
- using SQLEXECex from vfp2c32

Best regards
tom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close