Genesys CTI User Forum
Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: catanirex on October 18, 2013, 10:41:36 AM
-
Does anybody know how to read the content of Interaction table, AllAttributes column (image type) in Genesys UCS main database on Microsoft SQL server 2008?
We do not want to change any data - just read it! We need to look into that column via SQL code.
Thanks in advance!
-
I do not know why do you have posted this topic here, because it is not problem/issue related to Genesys platform, but to the MSSQL in general. So,on google you can find a lot of answers and way how to achieve it. Why do you need read the column via database? You can check it currently:
1. In applications logs
2. Via some app using PSDK
-
Check the docs on UCS and IXN Tables catarinex, it specifies the keys and each table meaning
-
catanirex, i've tried, but without success. looks like standard solutions for decoding blob data doesn't work with ucs db. don't waste time with it. better way that i found is using Contact SDK for requesting interactions and their content.
-
Thanx smile,
We also tried standard SQL commands without success for that column. IxnContent table we managed to view using standard conver methods - but not the AllAttributes.
We really would need to read it, since developing a sdk application is a bit to much work just for this :(
cavagnaro & Kubig - I know the ucs db structure since long time. But Genesys have done something non-SQL standard in the AllAttributes Image/blob column :( Thats why I posted the question here.
-
I do not about some non-standard using. In SQL world is image type using for example saving of string field. Whole procedure how to read image type column from SQL database took me approx 5 minutes. What did you do for solution?
You can just read the column's value and encode it from bytes to the string (how simple) - There are many ways how to achieve it and is at your decision what solution you will choose.
-
Ha-ha, Kubig ;)
I bet you can't decode AllAttributes field (in case of ucs 8.1 & ms sql 2008). Would you try?
[quote author=Kubig link=topic=8050.msg35446#msg35446 date=1382621338]
I do not about some non-standard using. In SQL world is image type using for example saving of string field. Whole procedure how to read image type column from SQL database took me approx 5 minutes. What did you do for solution?
You can just read the column's value and encode it from bytes to the string (how simple) - There are many ways how to achieve it and is at your decision what solution you will choose.
[/quote]
-
Of course I can and you can too, all what want can :-) And of course I have tried it before write the post. What will you bet, smile?:-)
I have tried to encode AllAttributes column value from some InboundNew email and see email headers for example (X-Header-...) etc.
Do you mean, that I have a time to write false post or make fun of you? Calm down man, please.
-
[quote author=Kubig link=topic=8050.msg35450#msg35450 date=1382623140]
Of course I can and you can too, all what want can :-) And of course I have tried it before write the post. What will you bet, smile?:-)
[/quote]
it depends of your location ;)
i really spent a huge time trying to extract data from this field. and i know it was possible in case of ucs 7.6 and ms sql 2005. don't know what is changed, but can't do this in latest version.
-
I have tried it on UCS 8.1.2 and MSSQL2008. So, you are not right, unfortunately. Simple test, copy the value from the column and put in into some on-line converter from hex to string, check yourself what will be the result
-
cool. have you tried to do same thing using standard sql functions like Convert?
-
Not yet,but on web are Some hints for creating procedure. Other Option Is via Some application which can Connect to The database and convert The bytes to The String. I have not time to develope working code for sql, I just want to correct information about unreadable column,because it is not true. For explain, I hate the words "it is not possible" or something else. Other thing is Genesys must be able to read this column,so the data MUST be readable via std function.
-
Good position, Kubig, thank you!
live and learn ;)
-
Idea:
Run SQL Profiler while Genesys queries the UCS and you will see the SQL query it launches.
Not sure if will work but deserves a try
-
It should be the one of the ways, but I think that Genesys read the value in byte format and then convert it in own code(stack). If anybody have time to test/try trace via SQL profiler, it will be interesting for others here :-)
-
You can decode it using the following, example, ruby script:
[code]
data = event['InteractionAllAttributes'];
# Filepointer - could be two bytes and two bytes for number of segments, not sure
sizeBytes = 3;
fp = 0;
# 16 bit big endian
length = data[fp, sizeBytes].unpack('n')[0];
segmentCountBytes = 1;
fp += sizeBytes;
segmentCount = data[fp, segmentCountBytes].unpack('C')[0];
# Skip 0x00, not sure about this - could be it is 16 bit int
fp += 1;
segmentSizeBytes = 2;
segmentIdx = 0;
event['InteractionAttributes'] = {};
while (fp < length)
# Skip 0x00 - todo, is part of header?
fp += 1;
# Key - Value structure
segmentLength = data[fp, segmentSizeBytes].unpack('n')[0];
fp += segmentSizeBytes;
key = data[fp, segmentLength];
fp += segmentLength;
segmentLength = data[fp, segmentSizeBytes].unpack('n')[0];
fp += segmentSizeBytes;
# Show non readable characters as .
value = data[fp, segmentLength].gsub(/[^[:print:]]/,'.');
fp += segmentLength;
event['InteractionAttributes'][key] = value.to_s;
end;
[/code]
-
If somebody is still interested in this topic, here is a draft C# implementation of a KVC conversion, based on post by Rutger.
Types handling have to be improved. Right now it works for strings, sub-kvc and ints. For other types I don't have examples on my system right now.
[code=csharp]
private Dictionary<string, object> ReadKeyValueCollection(byte[] buffer, int offset = 0)
{
var kvc = new Dictionary<string, object>();
int fp = offset;
int length = BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, fp));
fp += 2;
int pairs = BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, fp));
fp += 2;
while (fp - offset < length)
{
int type = BitConverter.ToChar(buffer, fp);
fp += 1;
int keyLength = BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, fp));
fp += 2;
var key = ASCIIEncoding.Default.GetString(buffer, fp, keyLength);
fp += keyLength;
int valueLength = BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, fp));
fp += 2;
kvc[key] = ReadValue(type, buffer, fp, valueLength);
fp += valueLength;
}
return kvc;
}
private object ReadValue(int type, byte[] buffer, int offset, int length)
{
// 0 - str, 1 - int, 2 - utf16???, 3 - lst
switch (type)
{
case 1: return ReadInt(buffer, offset, length);
case 3: return ReadKeyValueCollection(buffer, offset);
default: return ASCIIEncoding.Default.GetString(buffer, offset, length);
}
}
private object ReadInt(byte[] buffer, int offset, int length)
{
switch (length)
{
case 1: return BitConverter.ToChar(buffer, offset);
case 2: return BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, offset));
case 4: return BinaryPrimitives.ReverseEndianness(BitConverter.ToInt32(buffer, offset));
case 8: return BinaryPrimitives.ReverseEndianness(BitConverter.ToInt64(buffer, offset));
default: return BinaryPrimitives.ReverseEndianness(BitConverter.ToInt16(buffer, offset));
}
}
[/code]