SQLEXEC and ODBC String function REPLACE 
Author Message
 SQLEXEC and ODBC String function REPLACE

Hi Everyone,

I've been trying to get the ODBC String function REPLACE to work in a
SQL statement as described in the Microsoft KnowledgeBASE articles
Q147731 and Q103142. My back end is MSSQL Server 6.5 (SP4).  The front end is
VFP 5.0a.  I've tried two different ODBC drivers, Microsofts SQL Server driver
2.65.0240 and Intersolv' 32 bit SQL Server driver 3.01. The following works
using the ODBC LENGTH function:

cSQL = "SELECT {fn LENGTH(projectwbs)} FROM fproj where projectid > 730"
result = SQLEXEC(nHandle, cSQL)

But I can't get the REPLACE function to work.  I've tried:

cSQL = 'SELECT {fn REPLACE((projectwbs), '.000', 'xxx')} FROM fproj where
projectid > 730  '
Which gives a syntax error in VFP

cSQL = 'SELECT {fn REPLACE((projectwbs), ' + '.000' + ',xxx' + ')} FROM fproj
where projectid > 730  '
result = SQLEXEC(nHandle, cSQL)
Which doesn't give a VFP syntax error.  But result returns -1 with an error
message from the ODBC driver that the statement contains an unsupported
escape clause. The REPLACE function syntax is: REPLACE(string_exp1,string_exp2,
string_exp3) Which replaces all occurrences of STRING_EXP2 in STRING_EXP1 with
STRING_EXP3.  
It isn't the VFP REPLACE function which updates table records, it is an ODBC
function.  

I believe the problem I'm having is because the ODBC REPLACE takes string
arguments, and the single quotes around the string arguments is screwing it up.  
I can't figure out how to pass the string arguments without making the whole
statement fail. I've tried as many other variations as I could think of with no
success.  

Can anyone help?

thx!

Fiona



Mon, 28 Aug 2000 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. SQLEXEC and ODBC String function REPLACE

2. SQLEXEC and the ODBC function REPLACE

3. HELP!!! SQLEXEC AND ODBC API

4. HELP!!! SQLExec and ODBC API

5. Fox 7 , ODBC , SQL Server, connection problems with sqlexec()

6. ODBC, sqlexec(..'insert-sql'), and general fields

7. ODBC - hangs in sqlexec

8. Enabling Replace menu and function....

9. replace descending with own function

10. functions in the replace command

11. String function Problems

12. Sql and String function

 

 
Powered by phpBB® Forum Software