String Formatting not working in Builder

Aruna_Tata

Hi,

In my builder code, I have a statement as below:

sSQL = "Select * from dtree where createdate > to_date(inputdate, 'dd-mon-yy')"

CAPI.Exec(connect, sSQL).

However the sSQL always evaluates as "Select from dtree where createdate > to_date(inputdate, '\dd-mon-yy\')

How do I get rid of the '\' character? I've tried double quotes, Str.Quote etc but nothing seems to work here!!

Thanks,

Aruna

Find more posts tagged with

Comments

Appu_Nair

Do you need to use the Oracle function to_date to convert a string date.I use oscript equivalents like this snippet.Also use of Bind Vars in oscript or any programming language should be practiced

There should be ways to do the to_date I will check later ands ee if I can post an update

//    This is the method subclasses implement to provide their functionality.

function Dynamic Execute( Dynamic ctxIn, Dynamic ctxOut, Record r )

//echo(this.fPrgCtx.fDSession.fUserID)

//.fhtmlfile="deleltype.html"

    Object            prgCtx = .prgSession()
    Object            dbConnect = prgCtx.fDBConnect
    CAPICONNECT        cconn = prgCtx.fDbConnect.fConnection
    String query
    Recarray recordset
    Date theDate
    String dateString = "January 05, 1993"
    theDate = Date.StringToDate( dateString, "%B %d, %Y" )

                              query = "Select * from dtree where createdate > :A1"
                            recordset = CAPI.Exec( cconn, query, theDate )


    return Undefined

end

Aruna_Tata

Thanks Appu!! That helped. And yes I've taken note and shall use more bind variables in my code :-)

Quoted Appu Nair (anair@alitek.com) on 2010/08/04 14:09:

Do you need to use the Oracle function to_date to convert a string date.I use oscript equivalents like this snippet.Also use of Bind Vars in oscript or any programming language should be practiced

There should be ways to do the to_date I will check later ands ee if I can post an update

Aruna_Tata

Now all I have to do is figure out how to get around the exact same problem for my "sys_connect_by_path" for the oracle statement :-)

Appu_Nair

I do not know the exact syntax of sys_connect_by_path but here's an oscript snippet where I use a

connect by which may be similar to what you are looking for.I am sure you can use the to_date like you had I just forgot how to...

    String stmt,error

/*At one shot we should try to get the parent node's name of
Permits and Forms/Applications folder*/

stmt=' select a.parentid,a.dataid,a.name as'+'"'+'currName'+'"'
stmt+=' ,b.name as '+'"'+'parentName'+'"'
stmt+=' from dtree a ,dtree b where a.subtype = 0 '
stmt+=' and a.name like (:A1)'
stmt+=' and b.dataid=a.parentid '
stmt+= ' And a.dataid in '
stmt+='(select dataid from dtree connect by prior dataid ='
stmt+= 'parentid start with dataid=(:A2 ))'

.fValues = CAPI.Exec(.fPrgCtx.fDbConnect.fConnection,stmt,'Permits and Forms/Applications',folderID)

if IsError( .fValues )
   error=Str.Format( ' (%1) ,(%2),(%3),(%4),(%5),(%6)', .fValues,stmt,'currname','parentname','Permits and Forms/Applications',folderID )
   File.Write( errorFile,error )
else
   error=Str.Format( 'LoadResultSets:SQL Stmt (%1) ,Gave Num Recs: (%2)', stmt,Str.ValueToString(Length( .fValues)) )
   File.Write( successFile,error )
end

Aruna_Tata

The syntax is sys_connect_by_path(name, "->") "Path" - I tried what you have here - but whatever I do, i seem to get the escape characted. Need to figure out what I am doing wrong.

Quoted Appu Nair (anair@alitek.com) on 2010/08/04 15:17:

I do not know the exact syntax of sys_connect_by_path but here's an oscript snippet where I use a

connect by which may be similar to what you are looking for.I am sure you can use the to_date like you had I just forgot how to...

Appu_Nair

Your original to_date and sys_connect_by can be written like this.It did work for me.try queries on SQLplus or toad and make sure you are testing the same in builder

//How to do the to_date

String theDateString = "2010-05-11"
query="select * from dtree where createdate > to_date(:A1, :A2)"
recordset = CAPI.Exec( cconn, query,theDateString,'yyyy-MM-dd')
//how to do the sysc_connect_by_path

/*Trying to write this query with Bind vars hard coded subtypes & dataid
   select
LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(name, ':') "Path",
DTree.DataID,
DTree.SubType,
DTree.GIF,
DTree.PermID,
DTree.Reserved,
DTree.ReservedBy
from
dtree
where
subtype in (0,144)
start with
dataid = 2000
connect by prior dataid = abs(parentid) and createdate > to_date('2010-01-04', 'yyyy-MM-dd')

*/

query="select LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(name, ':')"
                            query+="Path"+", DTree.DataID, DTree.SubType, DTree.GIF, DTree.PermID, DTree.Reserved, DTree.ReservedBy from dtree "
                            query+="where subtype in (0,144) start with dataid = 2000 connect by prior dataid = abs(parentid) and createdate > to_date(:A1, :A2)"

recordset = CAPI.Exec( cconn, query,theDateString,'yyyy-MM-dd')

Aruna_Tata

Its working now thanks!! Turns out I had to put "Path" as ""Path"" (two sets of double quotes) to get it to work - Oracle did not like "'Path'" (single quote within the double quotes).