SQLEXEC and the ODBC function REPLACE 
Author Message
 SQLEXEC and the ODBC function REPLACE

I'm not familiar with that syntax.  What is your datasource back end?  What
is it the replace() command is intended to do?  If you wish to replace the
contents of a particular column, why not just use the UPDATE ... SET ... SQL
command?

Hanz.

Quote:

>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.  The following works:

>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', '')} FROM fproj where
>projectid > 730  '
>Which gives a syntax error in VFP

>cSQL = 'SELECT {fn REPLACE((projectwbs), ' + '.000' + ')} 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.

>I've tried as many other variations as I could think of with no success.
>Can anyone help?

>thx!

>Fiona




Sun, 27 Aug 2000 03:00:00 GMT  
 SQLEXEC and the ODBC function REPLACE

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.  The following works:

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', '')} FROM fproj where
projectid > 730  '
Which gives a syntax error in VFP

cSQL = 'SELECT {fn REPLACE((projectwbs), ' + '.000' + ')} 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.

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  
 SQLEXEC and the ODBC function REPLACE

Hi Hanz,

Thanks for your response.  I guess I should have suplied more info.  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.  The Microsoft KB articles I mention in
my original posting discuss the use of these ODBC functions.  My datasource
back end is Microsoft SQL Server 6.5 (SP4).  The front end application 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.  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.  Does this help clarify the situation?

thx,

Fiona



Mon, 28 Aug 2000 03:00:00 GMT  
 SQLEXEC and the ODBC function REPLACE

Fiona,

You said:

Quote:
>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.

And:

Quote:
>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.

Quote:
>cSQL = 'SELECT {fn REPLACE((projectwbs), '.000', '')} FROM fproj where

projectid > 730 '

Quote:
>Which gives a syntax error in VFP.

I don't have any experience with SQL Server, and I've never seen the
above syntax.  However, could you cut out the following command and
paste it into your application to see if it works :

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

I think you want to send double quotes to SQL Server - so you have to
surround the whole string with single quotes.

Please advise if that helps.

--

Matt McQueen
Matt McQueen Software Ltd.
http://www.netcomuk.co.uk/~mmcqueen/



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

 Relevant Pages 

1. SQLEXEC and ODBC String function REPLACE

2. SQLEXEC and ODBC String 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. ODBC error numbers returned by VFP (AERROR() function)

12. Connectivity error:[Microsoft ODBC Driver Manager]Function sequence error

 

 
Powered by phpBB® Forum Software