Solved

LotusScript help - killing quotes

Posted on 2002-07-30
19
730 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 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

16 Experts available now in Live!

Get 1:1 Help Now