Xbasic string manipulation - replace single quotes with double quotes

[[email protected]]

I'm using the following to return a delimited list of user groups for the current user.

dim uname as c
uname = A5ws_GetCurrentUser()
session.usergroups = crlf_to_comma(A5WS_Get_User_Assignments(uname))

This sets session.usergroups to this value (including the single quotes): 'Administrator,User,View Only'

I need session.usergroups to look like this "Administrator","User","View Only" so I can use it in an IN Clause in an sql statement.

In using stritran(session.usergroups, ",", "','") I can get the string to look like this 'Administrator","User","View Only'

However I haven't been able to use strtran to replace the the single quotes at the beginning and end with double quotes? How can I do this please?

[madtowng]

If I understand what you're trying to accomplish, the code below should work.
The image below illustrates the results of my code.

Capture.PNG

session.usergroups = stritran(session.usergroups,"'",chr(34))
session.usergroups = stritran(session.usergroups,",",chr(34)+","+chr(34))

[[email protected]]

Thanks but it ends up truncating the string to this: 'Administrator

Seems to work ok in the immediate window for some reason but not when live.

Have gone around in circles for some time on this so I'm going to park it for now. Thank you though.

[madtowng]

Post the code you're using. There's probably a reason you come up with something different that what I end up with.

[[email protected]]

function onTabbedUIInitialize as v (e as p)


dim uname as c
uname = A5ws_GetCurrentUser()
session.usergroups = crlf_to_comma(A5WS_Get_User_Assignments(uname)) 'works

session.usergroupsForIN = stritran(session.usergroups,"'",chr(34))
session.usergroupsForIN = stritran(session.usergroupsForIN,",",chr(34)+","+chr(34))


end function

[madtowng]

Hi Richard,

Sorry about the delay, but I try to test things.
CODE A should work for your function as written. I couldn't test exactly because I don't have web security enabled.
CODE B was designed to immediately show the results.
I think I nailed what you're trying to accomplish, but if not, please share your thoughts.

Gregg

CODE A

function onTabbedUIInitialize as V (e as p)

dim session.usergroups as c
dim session.usergroupsForIN as c
dim uname as c

uname = A5ws_GetCurrentUser()
session.usergroups = A5WS_Get_User_Assignments(uname) 'works

for each u in session.usergroups
session.usergroupsForIN = session.usergroupsForIN + sql_quote(u.value) + crlf()
next
session.usergroupsForIN = crlf_to_comma(session.usergroupsForIN)

end function

CODE B

function onTabbedUIInitialize as c (e as p)
' This is the one I did the testing with. I set e.name = "Gregg98188" to call the function
dim session.usergroups as c
dim session.usergroupsForIN as c
dim uname as c

if e.name = "Gregg98188" then
session.usergroups = <<%str%
Administrators
Users
View Only
%str%

else
uname = A5ws_GetCurrentUser()
session.usergroups = A5WS_Get_User_Assignments(uname) 'works
end if

'debug(1)
for each u in session.usergroups
session.usergroupsForIN = session.usergroupsForIN + sql_quote(u.value) + crlf()
playwithitnow = .t.
next ' u
session.usergroupsForIN = crlf_to_comma(session.usergroupsForIN)

onTabbedUIInitialize = session.usergroupsForIN

end function

[[email protected]]

Thanks.

I tried CodeA which is close but it seems to keep putting a single quote at the beginning and end so I end up with '''Administrator'',''User''' for example.

So I added another strtran function but still no luck.

Here is the full code.

dim session.usergroups as c
dim session.usergroupsForIN as c
dim uname as c

uname = A5ws_GetCurrentUser()
session.usergroups = A5WS_Get_User_Assignments(uname) 'works

for each u in session.usergroups
session.usergroupsForIN = session.usergroupsForIN + sql_quote(u.value) + crlf()
next
session.usergroupsForIN = crlf_to_comma(session.usergroupsForIN)

'this is the line I added to try to eradicate the single quote at beginning and end but it gets rid of all of the double quotes and the result is 'Administrator,User'
session.usergroupsForIN = stritran(session.usergroupsForIN,chr(39),"")

[madtowng]

I added one more part that matches what I'm understanding as shown in the lower part of the image below.
This has me confused because it goes against every bit of sql I've written.
Just to satisfy my curiosity, could you share the where section of the working sql code .
I am sure I can generate what you need.

select *
from usertable
where usergroup in ('Administrators','Users','View Only')

Capture.PNG
Capture.PNG

[madtowng]

For simple sql sharing, I threw a quick table up at https://paiza.io/projects/p01U3NVPwMwxoFYNRCuCgw?language=mysql

[armasoft]

Ugly solution, but I think it works..

If you use

session.groups = A5WS_Get_User_Assignments(uname)

you can then use my_result = """+stritran(session.groups,crlf(),"","")+""" to build the required string

[madtowng]

I'm guessing you're setting up some kind of sql statement.
Can you share what that statement should look like ?

[[email protected]]

To view the result of the code I'm setting the Initial Value of the quick search field in the grid to session.usergroupsForIN and it looks like this: '''Administrator'',''Hosp1","Hosp2'''

I am also using session.usergroupsForIN in the AlphaDAO SQL SELECT Statement for the dynamic choices in a drop down field in the same grid. The SQL code looks like this:

SELECT
Hospital
FROM tblCodeHospitals
WHERE Hospital IN (session.usergroupsForIN)

Note: The whole reason for trying to build the string for the IN clause is that when setting up the choices for the drop down you can create an argument but you don't seem to be able to set its value to a session variable, whereas you can in other parts of alpha such as the query for the grid's recordset. If I could do this I could use an array_argument just fine.

I'm using V4.6.2.9 - 7564-5566 21 MAR-2021

[madtowng]

You might have to make some minor adjustments for your project but the should work.
The code above union all in the image shown displays all the data in the table.
The code below union all shows the results using arguments in the where section.

Capture.PNG

dim itWorked as L ' this is used to determine if the sql executed successfully
args.set("A","Administrators")
args.set("B","Hosp1")
args.set("C","Hosp2")
sqlCode = <%txt%
select hospital
from tblCodeHospitals
where hospital in (:A,:B,:C)
<%txt%>
cn.open(sqlConnection)
itWorked = cn.execute(sqlCode,args)