slxdeveloper.com Community Forums

The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!

External Development Forums - SalesLogix OLEDB Provider
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to SalesLogix OLEDB Provider | New Thread

View: Search:

Author

Thread: Translate Binary field to text

Shayne
Posts: 8

Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 12:21 AM

I have a project that I am trying very hard to finish tomorrow, so really hoping someone has an easy answer for this. I am using the SLXOLEDB and connecting to the sysdba.plugins table, trying to read the sql query behind contact and account groups. I know it is possible since in my search for the answer I see Ryan just released a utility that does exactly that. I have tried mutliple things, the latest two involving reading into a string, converting that manually to binary, and then manually back to text, no luck there, and based on a somewhat related comment on here I tried reading it into an ado stream as binary and changing the streams type to text, but this errored on me. Any pointers on this would be appreciated.

Thanks,
Shayne

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 12:35 AM

And of course, the minute after I post this I make some progress. So instead of trying to convert it on the fly by changing the type, I did a save to file, closed the stream, changed the type and charset and then reopened and did a load from file....
With DBstm
.Type = adTypeBinary
.Mode = adModeReadWrite
.Open
.Write DBRS!Data
.SaveToFile "C:\slxStreamTemp.txt"
.Close
.Type = adTypeText
.Charset = "ASCII"
.Open
.LoadFromFile "C:\slxStreamTemp.txt"
Sqlstr = .ReadText
.Close
End With

I can see that all the data is going out to the file, however when I do the read back in, it is only a portion of the files text, so not sure what is going on there.

So I now have a huge set of text to parse. I could still use some pointers on doing this in a cleaner fashion minus the temporary file, and also the best way to extract out just the sql part of the data, as this data field seems to contain a bunch of other info as well. Lastly I see a default lineseperator setting for the stream object, is there perhaps a better setting for it than its current adCRLF to clean up the returned data?

Thanks,
Shayne

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 2:38 AM

Okay, I am actually at the point where all I really need is some advice on parsing this data properly, Split function in VB doesn't seem to play nicely with it, I can definitly muddle through it, but sure would love a more elegant solution if someone already knows one.

Thanks,
Shayne

[Reply][Quote]

Stuart
Posts: 178

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 3:52 AM

I had a go at parsing this stuff a while ago and decided it is not easy. I was looking at the SECURITY Blog which contains things like this:

' SecurityGroup| sgAccount
' EntityType|atDetailView
' RightsDefs|rdAdd|rdEdit|rdDelete|rdNoSync |PluginID|p6UJ9A0004MR
' EntityName||System:Account Detail

And the only way I could find was to read it line by line, and look for the various strings using InStr(). I'd be interested to hear if there is a more elegant way

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 10:53 AM

Can you remember how you got it seperated out into line by line? Thats what I was hoping the SPlit() function would do for me, but it failed miserable, and the returned text has a bunch of wierd box characters in it that I am sure is the issue.

[Reply][Quote]

Stuart
Posts: 178

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 24 Oct 08 12:46 PM

I will look up the code to split it into lines on Monday when I'm back in work, but it wasn't very difficult. The parsing was the hard bit

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 25 Oct 08 12:15 AM

No worries Stuart, I found a nice way to get down to the sql query, code is below. The last part is to trim off any non alpha characters that are hanging on.
To clarify, the Sqlstr varaiable at the start of this has the entire plugin.data field value in it.

Y = InStr(1, Sqlstr, "SELECT ", vbTextCompare)
If Y > 0 Then
Sqlstr = Right$(Sqlstr, Len(Sqlstr) - Y + 1)
Else
MsgBox "Error reading SQL query."
GoTo CleanAndClear
End If
Y = InStr(1, Sqlstr, "", vbTextCompare)
If Y > 0 Then
Sqlstr = Left$(Sqlstr, Y - 1)
Else
MsgBox "Error reading SQL query."
GoTo CleanAndClear
End If
Do While Not Right$(Sqlstr, 1) Like "[0-9a-zA-Z]"
Sqlstr = Left$(Sqlstr, Len(Sqlstr) - 1)
Loop

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 25 Oct 08 1:52 AM

Alright, I am so close to having this thing working I can taste it. Last problem (I think), is reading the parameter values. I can't quite figure out how they are stored in here. Got the names parsed out fine and all the code set to swap them into the sql query, I just can't quite determine how to properly read in the value. Here is a sample line in case anyone has some insight.
Name DateTime0
Attributes
paNullable DataType
ftDateTimeNumericScale PrecisionSizeValue @ùâ@

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 25 Oct 08 8:55 AM

What you are dealing with is "streamed data" where the code was originally developed in Delphi. If you understand Delphi, you can then "decode" this stuff correctly.

[Reply][Quote]

Shayne
Posts: 8

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 25 Oct 08 4:30 PM

Nope, never touched Delphi unfortunately, but I know this has been done by someone here, cough, recently, cough, so really hoping they will take pity and share some info with me.

[Reply][Quote]

Stuart
Posts: 178

Re: Translate Binary field to text Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 27 Oct 08 3:45 AM

I used this rather horrible code, which is reading line by line from a file, to get the PLUGINID. I couldn't figure out how to generalise this, so each value |I wanted needed its own Function


Private Function GetPluginID()

    Dim strPluginID As String
    Dim strline As String
    
    'Looking for this:
    '   SecurityGroup  sgAccount
    '   EntityTypeatDetailView
    '   RightsDefsrdAddrdEditrdDeleterdNoSync PluginIDp6UJ9A0004MR
    '   EntityNameSystem:Account Detail_OAG
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strFileLocation) Then
        Set myFile0 = fso.OpenTextFile(strFileLocation, fsoForReading)
        Do Until myFile0.AtEndOfStream
            strline = myFile0.ReadLine
            If InStr(strline, "SecurityGroup") > 0 And InStr(strline, "sgAccount") > 0 Then
                strline = myFile0.ReadLine
                If InStr(strline, "EntityType") > 0 And InStr(strline, "atDetailView") > 0 Then
                    strline = myFile0.ReadLine
                    If InStr(strline, "PluginID") > 0 Then
                        strPluginID = Right(strline, 12)
                    End If
                End If
            End If
        Loop
        myFile0.Close
        GetPluginID = strPluginID
    Else
        GetPluginID = ""
    End If

End Function

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 5/15/2024 5:56:27 PM