ODBC Question

[Leeus]

Hi all, quite a complex one here, at least I think.
I have a SQL DB logging some info and unfortunately it logs most of the data in one field called MsgText in the format below as an example.

MsgText
id=firewall time="2002-12-06 16:53:50" fw=TestFW pri=6 rule="ALLOW_PING_FROM_PUB" src=62.105.111.1 dst=62.105.111.2 proto=icmp-Echo sport=0 dport=0 indev=0 inport=1(PUBLIC) rc=100 msg="firewall pass"

Is there any way to split these up on an ASP Page so I have each "new" field in a cloumn???

[allida77]

I would iterate through the string and ouptut its Asc() value.

For each ch in strMsgText
Response.Write(Asc(ch))
Next

By doing this you can figure out what to split on. It looks like you could split on the tab CHR() (I cant remember what the numb is), anyway If there are tabs within the time or msg fields then you are going to have to do a little more work. When iterating through the string you are going to have to check for a " (CHR(34)) and ignore everything between them ,because there are tabs, and split after the last "(CHR(34)) . This is only because of the time or msg fields. I am not sure if I am helping or confusing but never the less you are going to have to split on a common delimiter between each field.

[Leeus]

These fields will be exactly the same every time if that helps?

[whammy]

If it's always the same, you can use the Mid() function (and other string manipulation functions) to split it up for you... looking for things like:

id=

For instance you can use InStr(stringname,"id=") to get the location that id= starts, and then look for the next occurence of a space.

Same goes for the other fields with quotes - this might be a bit complicated at first, if I have time to mess with it later I will... but I'm studying ASP.NET :(

You should be able to get started on it by reading here:

http://www.w3schools.com/asp

[whammy]

Ok... I saved your string as a text file named "text.txt" and wrote the following function/script for you (FYI, ALL from stuff I orignally learned from the link above!). If you're looping through a database you'll want to pay attention to my comments:

text.txt:

id=firewall time="2002-12-06 16:53:50" fw=TestFW pri=6 rule="ALLOW_PING_FROM_PUB" src=62.105.111.1 dst=62.105.111.2 proto=icmp-Echo sport=0 dport=0 indev=0 inport=1(PUBLIC) rc=100 msg="firewall pass"


ASP page:

<%
Option Explicit

Dim thefile, fs, f, currentline

Function ParseString(thestring,namestring,delimiter)
If InStr(thestring,namestring) Then
Dim startpos, endpos, tempstring, temparray, tempvalue
startpos = InStr(thestring,namestring) + Len(namestring)
tempstring = Mid(thestring,startpos,(Len(thestring)-startpos)+1)
endpos = InStr(tempstring,delimiter)-1
If endpos > -1 Then
tempvalue = Left(tempstring,endpos)
temparray = split(namestring,"=")
ParseString = temparray(0) & ": " & tempvalue & "<br />" & vbCrLf
Else
Exit Function
End If
Else
Exit Function
End If
End Function

' I am reading a text file instead of a database field, but it doesn't matter since either one
' should produce the same string!

thefile = Server.MapPath("text.txt")

Set fs = CreateObject("Scripting.FileSystemObject") ' Replace these two lines with db connection
Set f = fs.OpenTextFile(thefile,1,True)

Do While NOT f.AtEndOfStream ' Replace with Do While NOT rs.EOF

currentline = "" & f.ReadLine 'This would be your current record, replace with currentline = rs("whatever")

Response.Write ParseString(currentline,"id="," ")
Response.Write ParseString(currentline,"time=""","""")
Response.Write ParseString(currentline,"fw="," ")
Response.Write ParseString(currentline,"pri="," ")
Response.Write ParseString(currentline,"rule=""","""")
Response.Write ParseString(currentline,"src="," ")
Response.Write ParseString(currentline,"dst="," ")
Response.Write ParseString(currentline,"proto="," ")
Response.Write ParseString(currentline,"sport="," ")
Response.Write ParseString(currentline,"dport="," ")
Response.Write ParseString(currentline,"indev="," ")
Response.Write ParseString(currentline,"inport="," ")
Response.Write ParseString(currentline,"rc="," ")
Response.Write ParseString(currentline,"msg=""","""")
Response.Write ParseString(currentline,"blah="," ")

' If you're using a database connection, make sure you use rs.MoveNext here!!!
Loop

Set f = Nothing 'Close textstream stuff
Set fs = Nothing
%>


Output:

id: firewall
time: 2002-12-06 16:53:50
fw: TestFW
pri: 6
rule: ALLOW_PING_FROM_PUB
src: 62.105.111.1
dst: 62.105.111.2
proto: icmp-Echo
sport: 0
dport: 0
indev: 0
inport: 1(PUBLIC)
rc: 62.105.111.1
msg: firewall pass


Hope this helps! :)

The function can probably be improved/shortened, but it works...

P.S. The subject of this post should have been "String Manipulation Question" instead of "ODBC Question" as it really has nothing to do with ODBC... ;)

[whammy]

P.S. I just thought of something... it may be that line breaks are stored in the field... if so you can just split on vbCrLf and avoid the stuff above. If not, the above code should work

[Leeus]

Sorry that was a fair point, is there a good site on string manipulation as I am so bad on it!!

Thanks for the code Whammy, works really good!

[whammy]

Yeah, the link I provided in my original post (and commented on above) should teach you everything you need to know about string manipulation, if you play around with it a bit. :)

http://www.w3schools.com

I bet if one was to go through every tutorial on that site, and experiment with it on their own as well, they would be a real guru in not just ASP, but nearly everything having to do with development and programming. :D