Solved

LotusScript help - killing quotes

Posted on 2002-07-30
19
715 Views
Last Modified: 2013-12-18
I'm hoping this is a very simple question ...

I've made an agent that connects to an SQL Server via ODBC. I am iterating through the documents in a view and inserting important data into an SQL Server table.

Now, logically, problems arise if a field contains an SQL string delimiter character, namely, the single quote --> ' <--

I made a function to replace all occurrences of ' in a string with '' (i.e. two single quotes) and thought I was really clever ...

Function KillQuotes(srcStr As String) As String
  KillQuotes = Evaluate (|@ReplaceSubString(| & srcStr & |;"'";"''")|)
End Function

... but it doesn't work ! I just get a type mismatch error. I don't really understand where the error is.

I am using Lotus Notes 4.6.2.  Please help me get the KillQuotes function working correctly!
0
Comment
Question by:davidmwilliams
  • 6
  • 5
  • 5
  • +1
19 Comments
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187380
Evaluate always returns a Variant type so you need to declare your function as a Variant.
0
 
LVL 4

Author Comment

by:davidmwilliams
ID: 7187702
Ahh, interesting ... presumably the result has to then be cast to a string?

Can you give a code sample, just so I am clear?

Many thanks!
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187710
If you check out the example from online help, you will notice that you need to reference the returned value by passing an index.

In your code this would look something like this :

Function KillQuotes(srcStr As String) As String
 dim Result as Variant
 Result = Evaluate (|@ReplaceSubString(| & srcStr & |;"'";"''")|)
 KillQuotes=Cstr(Result(0))
End Function

This way the function still returns a string, but inside the function you reference the first value returned to the Variant (=index 0) to assign to the function.

Try it and let me know if it works (I'm using a R5 client, so there might be some differences)
0
 
LVL 4

Author Comment

by:davidmwilliams
ID: 7187719
Fantastic, I'll try that out back in the office (I'm on Australian time :) and get back to you within 12 hours.
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187723
Okay, I'm in Belgium, so I'll probably read about it tomorrow morning :-)
0
 
LVL 4

Author Comment

by:davidmwilliams
ID: 7189315
Alas, this fails with an unhelpful "operation failed" in error$ :(
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7190068
Can you post your complete script here?
Including Initialize, Declarations and stuff so I can see where and how you call the function.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7193070
Willi !

Do me a favor,  Try replacing the Bars for Evaluate statements with { and }  and the double quotes with Bars.

Just a try... I have played with these characters and have achieved some appreciatable results.

Good Luck!
Or
Just send us the full code.

-Arun
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7193481
Hello David,

take this:
Function KillQuotes(srcStr As String) As String
  Dim p As Integer
  p = Instr(1,srcStr, "'")
  Do While (p > 0)
    srcStr = Left$(srcStr, p) & "'" & Mid$(srcStr, p+1)
    p = Instr(p + 2,srcStr, "'")
  Loop
  KillQuotes = srcStr
End Function

Good luck,
zvonko

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:Arunkumar
ID: 7194447
Zvonko !
Why to write a huge script when it could be acheived by a one liner ???
*jus curious*

;-P
Arun.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7194612
Because JM's one-liner is not working :)

Where is yours?

Or should I show mine <|:)

0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7195717
The reason for the operation failed is simple:
When passing a quote to an @Formula, you need to put a \ in front of it, that's why the evaluate won't work.

It's better to do it with a script function like the one zvonko suggests.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7195889
Oh how polite we all are today :)

So here JM's corrected line to get the points:
Function KillQuotes(srcStr As String) As String
     Dim Result As Variant
     Result = Evaluate (|@ReplaceSubString("| & srcStr & |";"'";"''")|)
     KillQuotes=Cstr(Result(0))
End Function

David, points go to: jerrith! <|:-)

(not to Arun!!! :)

0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7195909
oh come on !  Dont be ridiculous !
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7195915
I tried that zvonko with the extra quotes to go around the passed string (I overlooked that) but it didn't work, either.
Have you tested this?
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7196027
You know what I dont want to test it... Cause i know the oneliner will work for sure 100% !

;-)
0
 
LVL 10

Accepted Solution

by:
zvonko earned 100 total points
ID: 7196115
Of course JM, I have learned my lesson :)

The ridiculous difference between assuming something and testing something is between it works or it does not work.

I test my assumptions to see it work <|:-)

JM, I assume your problem is at function invocation time, not function execution.
So try this extension:
Function KillQuotes(Byval srcStr As String) As String
     Dim Result As Variant
     Result = Evaluate (|@ReplaceSubString("| & srcStr & |";"'";"''")|)
     KillQuotes=Cstr(Result(0))
End Function

0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7196695
LOL !
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7327130
Thanks David :-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now