SQL parametrized statement

Xbase++ 2.0 Build 554 or later
Post Reply
Message
Author
Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

SQL parametrized statement

#1 Post by Victorio »

Hi,

I am sitting on this third day and still have not solution.

I need SQL statement with parameter for join with relation.
here
cStatementpolp := 'SELECT C_PROC,NAZPTK FROM RAUKN_OLP.DBF ORDER BY C_PROC WHERE (NAZPTK LIKE "'+pomtext3a+cislolv+pomtext3b+'" ESCAPE "'+pomtext4+'")'

when I define cislolv:="123", this works

Now I want to use parameter with question mark "?" but I do not know how to write quotation mark and apostrophe to make it work. I need replace cislolv with ?

This and many other version not work:
cStatementpolp := 'SELECT C_PROC,NAZPTK FROM RAUKN_OLP.DBF ORDER BY C_PROC WHERE (NAZPTK LIKE "'+pomtext3a+'?'+pomtext3b+'" ESCAPE "'+pomtext4+'")'

Note : I need create string where :
pomtext3a:="LVCH\_"
pomtext3b:="\_%"
and for example cislolv="123" , this must create string
LVCH_123_12131233...

ESCAPE I use because I have in table string field used character underline and for SQL this is wildcard for any one character, by ESCAPE i ignore it as wildcard.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: SQL parametrized statement

#2 Post by rdonnay »

I'm probably on the wrong track here with what you are trying to do, but I always use the eXpress++ DC_ApplySQLParams() function for parameterization.

Code: Select all

#INCLUDE "dcdialog.ch"

FUNCTION Main()

LOCAL pomtext3a := 'LVCH', ;
      pomtext3b := '_', ;
      pomtext4 := '12131233..', ;
      cislolv := '123', ;
      cSql

TEXT INTO cSql WRAP

SELECT C_PROC,NAZPTK FROM RAUKN_OLP.DBF
ORDER BY C_PROC
WHERE (NAZPTK LIKE ?)

ENDTEXT

cSql := DC_ApplySqlParams( cSQL, { pomtext3a + pomtext3b + cislolv + pomtext3b + pomtext4 })

wtf cSql pause

RETURN nil
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: SQL parametrized statement

#3 Post by Victorio »

I do not know fnc DC_ApplySqlParams() , interesting, will try.

Thank you very much.

Now I am trying create new field to store part of field NAZPTK , but this need modify structure and update this field with records.
But what you advice, I am sure can use on more functions.

Have a nice day :)

Post Reply