SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Multilingual support

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Multilingual support
Author Message
DaveG



Joined: 19 Oct 2009
Posts: 7
Country: United States

Post Multilingual support Reply with quote
Hi,

My project has a website that uses a group user account to access our database. To identify who made a change, we've built a procedure to pass the correct username to DB Audit. However, DB Audit's 'audit_login' column is a varchar(128), but my data could be nvarchar (unicode). Is there a way to build the tables using a nvarchar column instead of varchar?

Thanks,
Dave
Tue Jan 19, 2010 6:54 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Hi,

Are you referring to the system audit trail table db_audit.sys_audit_trail table or to audit trail tables audit_xxxxxxx used for the data-change auditing?


Regards
Tue Jan 19, 2010 6:59 pm View user's profile Send private message
DaveG



Joined: 19 Oct 2009
Posts: 7
Country: United States

Post Reply with quote
The audit trail tables audit_xxxxxxx used for the data-change auditing. Each tables has a 'audit_login' column as varchar(128). I need this to be nvarchar(128), if possible.

Thanks,
Dave
Tue Jan 19, 2010 7:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Unfortunately there is no easy way. But here is an idea... I'm thinking about creating a lookup table for storing user name codes as varchar values and full user names as nvarchar values. In the audit trail tables you would then save the code and use it as a reference to the lookup table, while in reports you would display nvarchar values. Basically DB Audit supports generating custom views in db_audit schema for data audit trail reports. When you run a report, the console first checks for defined V_SCHEMA_TABLE alias for the selected table. SCHEMA and TABLE here are placeholders for actual schema and table names, for example, V_DBO_MY_TRANSACTIONS. If such alias is found, it then checks for existence of user defined DB_AUDIT.V_SCHEMA_TABLE view and uses it as a data source for the report. In the view, you can join the audit trail table and lookup table and return nvarchar value and the console will report on that value. As far as I know DB Audit graphical console has some limitations on display of double-byte values, while the web-based console can display them just fine.
Tue Jan 19, 2010 7:14 pm View user's profile Send private message
DaveG



Joined: 19 Oct 2009
Posts: 7
Country: United States

Post Reply with quote
That might work. I may even be able to use the user table in conjunction with view to get the desired result. It's unfortunately I can't use DB Audit directly, but this is a decent workaround.

Thanks,
Dave
Tue Jan 19, 2010 8:03 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I just though of another method which doesn't require additional tables and joins, extra codes, etc... Technically you can convert nvarchar values to hexadecimal strings and store resulting strings in the existing varchar column audit_login, then in the reporting view, you can convert these values back to nvarchar format.

Here is a little code snippet demonstrating this idea


Code:
-- Unicode multi-byte value
DECLARE @mb_string NVARCHAR(64)
SET @mb_string = N'My Name'

-- convert it to hex
DECLARE @hex VARCHAR(128)
SET @hex = CONVERT(VARCHAR(128), CONVERT(VARBINARY(126), @mb_string))

-- convert it back to Unicode
SELECT CONVERT(NVARCHAR(64), CONVERT(VARBINARY(126), @hex))

Tue Jan 19, 2010 8:21 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.