PERL losing vbCrLf in SQL DB 1

[1DMF]

I'm baffled on this one, I have an MS access form bound to my SQL Server.

The memo field on the form is populated via a lookup table I use with the following code.

Dim sTerm As String

If Nz(Me!Lookup, "") = "" Then
    MsgBox "Please select a look-up term first!"
    Exit Sub
End If

sTerm = " [" & Me![Lookup] & "] "

If InStr(1, Me![See], sTerm, vbTextCompare) Then
    MsgBox "Term already used"
    Exit Sub
End If

Me!See = Me!See & " [" & Me!Lookup & "] " & vbCrLf

Me.Refresh

Now bear with me this is a PERL question....

However when I retrieve the record in PERL and issue

$$dt{'See'} =~ s/\n\r/<br>/g;

I just get the contents of the field with no HTML <br> or cr/lf characters, just a long spaced text string with the terms.

I have used the SQL enterprise manager to look at the raw data and the newlines are in the SQL as expected.

So somewhere the vbCrLf is falling of the content of the data, where could this be happening is it a problem with the Win32:ODBC function striping newline characters?

How do I deal with this ?

 

[MikeLacey]

Win32::ODBC looks quite hard and has an update date of 1999; would you consider porting your code to use The DBI and ODBC?

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[icrf]

generally, I see /\r?\n/ used, as if you're running this on windows, the \n may already include both characters (and the order is backwards)

If you don't run that line, do the newline come through? Can you check somewhere prior and see if $$dt{'See'} has them?

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

Hello Guys,

1. WIN32::ODBC I prefer to DBI, it's faster and easier to use, I could use DBI as my SQL is all in a SQL module I wrote so all the calls to the module for doing read,writes and updates is easily modified, but if it is the WIN32::ODBC losing the crlf then I'd just prefer to just add the <br> code where I want it rather than changing a whole module, which is my work round at present.

2. /r I found has to be used as it doesn't always capture the newlines with /n , this must be something to do with it being Active state PERL on a windows box, i guess it stands for "Return". I'm not sure what you mean by being backwards, surely it shouldn't matter what order you do the substitute.

Anyhow it would seem that leaving out the substitute command has no effect, ie. the vbCrLF is not in the data so I can only assume PERL is removing them. what can I do to resolve this, is there another code I could use in the MS Access VBA instead of vbCrLf that might not get stripped by PERL?

 

[icrf]

Just pick something that won't be in your data. I've seen the verticle pipe | as a common delimiter lately, as it's not used very often in real data. Since you're surrounding terms in [square brackets], can you just split on those?

s/\]\s*\[/]<br>[/g

And as far as order goes, I was saying \r\n is different from \n\r. IE, substituting for "ba" will not affect the word "cab".

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

ahh I see icrft , i though \n\r is saying substitute \n OR \r not specifically both in that order, can I use one line to say \r OR \n or do i need to use 2 regex substitutes.

I'm confused with

s/\]\s*\[/]<br>[/g

are the square brackets the wrong way round or is the s* doing something, can you explain.

cheers,

1DMF

 

[icrf]

\s* is looking for any potential white space (\s is whitespace, * after it means zero or more of them). The square brackets are special characters in a regex, so they have to be escaped. So, it's looking for something like "] [" and replacing with "]<br>[" which should put line breaks between your terms.

You can use | as an OR in a regex, but it's generally faster to do two separate searches (see mike's faq219-3057 on the topic)

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

cheers icrf, i was thinking more that is would be

find "[anything]" so what would that be , something like this...

s/\[*\]/\[*\]<br>/g

or is that saying match zero to many square brackets ?

How would i match [*] = left bracket, wild card, right bracket.

all help appreciated.

 

[1DMF]

I've tried the other substitute and it isn't working, I also wondered should the other brackets be escaped?

I tried this, but still no joy

$$dt{'See'} =~ s/\]\s*\[/\]<br>\[/g;

why is regex so complicated!

 

[MikeLacey]

I would use

/\[\*\]/

to match

[*]

in a string.

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[1DMF]

how do i tell it to replace it with itself and add <br> on the end.

like this ?

s/\[\*\]/\[\*\]<br>/g

now I know how all these programmers worked out how to draw pictures with ascii , they were trying to work out these darn pesky regex's

 

[1DMF]

Ok I found the problem

$$dt{'See'} =~ s/\]\s*\[/\]<br>\[/g;

works fine, I was applying it after I had hyperlinked the terms, do'h.

i was doing this first...

# Set replace code 
my $replace = " <a href=\"#$$dt{'Term'}\" ONCLICK=\" document.glossary.SEARCH.value=''; document.glossary.OLDSEARCH.value=''; document.glossary.PGCNT.value=1; visit('$jtm','$term'); \" title=\"Click to cross reference\"><font color=#E20E2D>[$tr]</font><\/a> ";

# Replace Also See with linked terms
$$dt{'See'} =~ s/\[$tr\]/$replace/i;

therefore "] whitespace [" was never found, well come on it is monday

 

[icrf]

Monday it is. The quantifiers * + ? {n,m} in a regex operate on the item before them. IE, in "\s*" "\s" is whitespace, and "*" quantifies that whitespace as zero or more occurances. So for the other way you were thinking, you'd say something like /\[[^\]]\]/$&<br>/i but that gets into (negative) character class and backreferences...it's good stuff if you want to dive in, but perl works great as a "learn as much as you need" kind of language.

Check faq219-1847 for some regex basics, or perldoc perlre for the dirt.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[icrf]

forgot that all-important quantifier:

/\[[^\]][red]+[/red]\]/$&<br>/i

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

thanks icrf, I take it $& is what you use to add itself in a regex substitute.

im a bit lost with the extra square barcket in the first bit \[[^\]]+\]

why are there 2 escaped right brackets ?

and it still doesn't answer the underlying question in the first place, where are the vbCrLf newline characters vanishing too, they are in SQL but not in the data once PERL gets it, I must assume this is a problem with the Win32::ODBC module, it's not something PERL as standard would do as I never had this problem when the database was in flat text file format.

oh well I have a work round for this but the actual description field looks a mess now as the newlines do not get converted anymore and how it looks in the MS Access DB Form, when you create the term description all neatly spaced with paragraphs and newlines is all squashed together when shown in the HTML page.

Hmmm how on earth am I going to deal with that, I hate the idea of placing a special character via the VBA code before the data is saved to the SQL DB, putting extra characters in data that shouldn't be there is just well, not the right thing to do in my book, but I might be left with little choice.

Any further ideas on this problem, and don't say use DBI::ODBC, it's not shipped as standard with PERL where as Win32::ODBC is, that surely says something.

Regards,

1DMF

 

[icrf]

Now I'm not suggesting you use DBI::ODBC for your production application, but I do suggest using it to run a simple script to get those fields with newlines in them and see if the newline are still there. Then change the test to run with Win32::ODBC and see if they disappear. If so, you've narrowed down the culprit and can begin looking through the module code for the problem, or contact the module author if they are still active.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

hey icrf, hmmm I think it's more a problem of not having the right code in the PERL regex.

I've done more testing and it seems that on the description the code is working of sorts.

if i have a paragraph and then start a new one by typing the text in the memo field on the MS Access database, I find that perl is matching the full blank newlines but not the end of paragraph one.

ie.

blah blah blah (end of paragraph not matched)
(blank new line is matched)
blah blah blah.

but because the end of paragraph is not matched to get the correct result you need to put 2 blank new lines to get the HTML to look correct, one for the end of paragraph and the other for the blank line.

so

# Replace newline with html
$$dt{'TDesc'} =~ s/\n\r/<br>/g;

works great for the blank newlines, so what does the enter key place on the end of a paragraph to match that?

should i then do

$$dt{'TDesc'} =~ s/\n/<br>/g;

or should it be

$$dt{'TDesc'} =~ s/\r/<br>/g;

or is it another code ?

this might have been why the other terms lookup wasn't working, or is it because pressing "Enter" in a memo field on an MS Acces Form attached to SQL places a different key code than hardcoding vbCrLf.

what is the "LineFeed" code in PERL if \r is return and \n is newline

 

[icrf]

A CR/LF (windows newline) is \r\n, so when you have a blank line there, it's two CR/LF's in a row, ie "\r\n\r\n" and you're trying to match "\n\r" so you're matching the middle of the two newlines. Try reversing it and match "\r\n" instead of "\n\r"

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[1DMF]

I wish I could give you another star, so just my sincere thanks will have to do

 

[icrf]

Glad to help.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo