IT Blog - Page 5 of 18 - Technical notes in IT

Error handling in sql server was a pain before SQL Server 2005. Starting from Sql Server 2005 try ... catch blocks was introduced. It was not only giving us a beauty of coding, but greatly improved our development performance. It comes with the following build-in functions: ERROR_NUMBER() ERROR_SEVERITY() ERROR_STATE() ERROR_PROCEDURE() ERROR_LINE() ERROR_MESSAGE() When these functions is called in a CATCH block, they will returns the error information. After working on many projects, I found a best pattern for error handling in Sql Server as the following steps. 1. Create a log table to store error information catch by try...catch blocks, CREATE table sys_log( id int identity primary key, procName varchar(255), essionID int, errorNumber int, severity smallint, errorState smallint, errorLine int, description nvarchar(255), info nvarchar(max), mslangid smallint default 1033, --1033 English, 2052 Chinese timeStemp datetime default getdate()) GO 2. create a stored procedure to catch and log error information: CREATE proc sys_LogError @procname sysname, @info nvarchar(max)='' AS INSERT INTO sys_log(ProcName,SessionID,ErrorNumber,Severity,ErrorState,ErrorLine,Description,info) values ( @procname, @@spid, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), @info ) 3. in all stored procedures that need to handle exepctions in transaction using this template: CREATE proc proc_name AS BEGIN tran BEGIN try -- do something print 1/0 commit tran END try BEGIN catch rollback tran declare @info nvarchar(max)='@table='+@table+', @urls='+@urls declare @procname sysname = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) exec sys_LogError @procname,@info ---- re-throw exeception if you need --declare @msg nvarchar(2048) = error_message() --declare @severity smallint = error_severity() --declare @state smallint = error_state() --raiserror (@msg, @severity, @state) END catch ERROR_MESSAGE() can be referenced with MASTER.dbo.sysmessages table, mslangid is for identifying…

2020-09-03 0 Comments 344 Views 1 Like IT Team Read more

Based OO principles the private fields should not be accessd by anything outside of the object. However, for some reason we have to access the private fields, a good example is unit testing. With dotnet reflection we could access any fields, members in an object. The accessibility is controled by BindingFlags, specify proper bindingFlags we can access all private fields in a class. To access the private fields in base class we need to add one more level of the type with BaseType. Here is the extension method to get the value of a public and private field in an instance. public static T GetFieldValue<T>(this object obj, string name) { var bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance; var field = obj.GetType().GetField(name, bindingFlags); if (field == null) field = obj.GetType().BaseType.GetField(name, bindingFlags); //father else if (field == null) field = obj.GetType().BaseType.BaseType.GetField(name, bindingFlags); //granpa else if (field == null) field = obj.GetType().BaseType.BaseType.BaseType.GetField(name, bindingFlags); //father of granpa return (T)field?.GetValue(obj); } 2,150 total views

2020-09-03 0 Comments 743 Views 0 Like IT Team Read more

Post_name in wordpress is urlencoded, and not showing Chinese chars. urlencode DELIMITER ; DROP FUNCTION IF EXISTS urlencode; DELIMITER | CREATE FUNCTION URLENCODE(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8 DETERMINISTIC CONTAINS SQL BEGIN -- the individual character we are converting in our loop -- NOTE: must be VARCHAR even though it won't vary in length -- CHAR(1), when used with SUBSTRING, made spaces '' instead of ' ' DECLARE sub VARCHAR(1) CHARSET utf8; -- the ordinal value of the character (i.e. ñ becomes 50097) DECLARE val BIGINT DEFAULT 0; -- the substring index we use in our loop (one-based) DECLARE ind INT DEFAULT 1; -- the integer value of the individual octet of a character being encoded -- (which is potentially multi-byte and must be encoded one byte at a time) DECLARE OCT INT DEFAULT 0; -- the encoded return string that we build up during execution DECLARE ret VARCHAR(4096) DEFAULT ''; -- our loop index for looping through each octet while encoding DECLARE octind INT DEFAULT 0; IF ISNULL(str) THEN RETURN NULL; ELSE SET ret = ''; -- loop through the input string one character at a time - regardless -- of how many bytes a character consists of WHILE ind <= CHAR_LENGTH(str) DO SET sub = MID(str, ind, 1); SET val = ORD(sub); -- these values are ones that should not be converted -- see http://tools.ietf.org/html/rfc3986 IF NOT (val BETWEEN 48 AND 57 OR -- 48-57 = 0-9 val BETWEEN 65 AND 90 OR -- 65-90 = A-Z val BETWEEN 97 AND 122 OR -- 97-122 =…

2020-08-30 0 Comments 1365 Views 0 Like IT Team Read more

Summary The data is stored in javascript variable in 51job website. The crawling could not get right data directly from html, but javascript. Using json plugin to achive the goal. Website: https://search.51job.com/ Search result Html source The data is not in the page html, but javascript code in json format. Xpath Using xpath to look for data on the page is fine, but in the code it returns nothing, because the data is in javascript variable. contentx = le.HTML(content) rets = contentx.xpath('//div[@class="e"]//p[@class="t"]') # returns nothing Crawling with python Using regular expression to parse out the jason data in javascript. ret = re.findall('window.__SEARCH_RESULT__ = (.*?)</script>', content_str)[0] Use json plugin to load ret into json object. d = json.loads(ret) Finally, get the data from json array. Complete code import requests import lxml.etree as le import re import json content = requests.get( url='https://search.51job.com/list/010000,000000,0000,00,9,99,python,2,1.html?lang=c&postchannel=0000&workyear=99&cotype=99&degreefrom=99&jobterm=99&companysize=99&ord_field=0&dibiaoid=0&line=&welfare=', ).content content_str = content.decode('gbk', 'ignore') # <meta http-equiv="Content-Type" content="text/html; charset=gbk"> ret = re.findall('window.__SEARCH_RESULT__ = (.*?)</script>', content_str)[0] d = json.loads(ret) # <class 'dict'> {'top_ads': [], 'auction_ads': [], 'market_ads': [], 'engine_search_result': [{'type': 'engine_search_result', results = d['engine_search_result'] for result in results: print(result) 2,777 total views

2020-08-28 0 Comments 793 Views 0 Like IT Team Read more

Based on the official knowledge base, currently there is no “out of the box” way to control events visibility — no plugin setting, plugin filter, etc. But with a bit of custom coding, you can get pretty close. 1. Hide event from month view if the use is not logged in. Ref: https://theeventscalendar.com/knowledgebase/k/making-a-members-only-calendar/ 1. create a event category: Agent Assign the event to the category. 2. add stylesheet, e.g. Custom CSS & JS plugin. .type-tribe_events.tribe-events-category-agent { display: none; } body.logged-in .type-tribe_events.tribe-events-category-agent { display: inline; } 2. disable popup tip window. ref: https://theeventscalendar.com/support/forums/topic/disabling-the-calendar-pop-up-views/ body .tribe-events-calendar .tribe-events-tooltip { visibility:hidden; } @media (max-width: 600px) { body .tribe-events-calendar .tribe-events-tooltip { visibility:hidden; } } 1,601 total views, 5 views today

2020-08-20 0 Comments 378 Views 0 Like IT Team Read more

Python Code import json import requests # 3rd party import jsonpath from pyecharts.charts import Map from pyecharts import options as opts from src.covid19_data import nameMap url = 'https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist' resp = requests.post(url).text data = json.loads(resp) # get contry names and confirmed cases $：outer {}， ..: fuzzy match + key name = jsonpath.jsonpath(data, "$..name") # ['美国', '巴西', '印度', '俄罗斯', ... ] confirm = jsonpath.jsonpath(data, "$..confirm") pairs = list(zip(name, confirm)) # [('美国', 5321520), ('巴西', 3109630), ('印度', 2329638), ...] # 2. data visualization map plotting - size,title,color,data map_ = Map(opts.InitOpts(width='1200px', height='600px')) .add(series_name="Global Covid-19 Confirmed Cases", data_pair=pairs, # data entry - country : confirmed maptype="world", # world map name_map=nameMap, # country name mapping is_map_symbol_show=False) # 不显示标记点 # set series options map_.set_series_opts(label_opts=opts.LabelOpts(is_show=False)) map_.set_global_opts(title_opts=opts.TitleOpts(title="Global Covid-19 Pandemic (Chinese)"), visualmap_opts=opts.VisualMapOpts(max_=6000000, is_piecewise=True)) map_.render('../result/covid19.html') covid19_data nameMap = { 'Singapore Rep.': '新加坡', 'Dominican Rep.': '多米尼加', 'Palestine': '巴勒斯坦', 'Bahamas': '巴哈马', 'Timor-Leste': '东帝汶', 'Afghanistan': '阿富汗', 'Guinea-Bissau': '几内亚比绍', "Côte d'Ivoire": '科特迪瓦', 'Siachen Glacier': '锡亚琴冰川', "Br. Indian Ocean Ter.": '英属印度洋领土', 'Angola': '安哥拉', 'Albania': '阿尔巴尼亚', 'United Arab Emirates': '阿联酋', 'Argentina': '阿根廷', 'Armenia': '亚美尼亚', 'French Southern and Antarctic Lands': '法属南半球和南极领地', 'Australia': '澳大利亚', 'Austria': '奥地利', 'Azerbaijan': '阿塞拜疆', 'Burundi': '布隆迪', 'Belgium': '比利时', 'Benin': '贝宁', 'Burkina Faso': '布基纳法索', 'Bangladesh': '孟加拉国', 'Bulgaria': '保加利亚', 'The Bahamas': '巴哈马', 'Bosnia and Herz.': '波斯尼亚和黑塞哥维那', 'Belarus': '白俄罗斯', 'Belize': '伯利兹', 'Bermuda': '百慕大', 'Bolivia': '玻利维亚', 'Brazil': '巴西', 'Brunei': '文莱', 'Bhutan': '不丹', 'Botswana': '博茨瓦纳', 'Central African Rep.': '中非共和国', 'Canada': '加拿大', 'Switzerland': '瑞士', 'Chile': '智利', 'China': '中国', 'Ivory Coast': '象牙海岸', 'Cameroon': '喀麦隆', 'Dem. Rep. Congo': '刚果（金）', 'Congo': '刚果（布）', 'Colombia': '哥伦比亚', 'Costa Rica': '哥斯达黎加', 'Cuba': '古巴', 'N. Cyprus': '北塞浦路斯', 'Cyprus': '塞浦路斯', 'Czech Rep.': '捷克', 'Germany': '德国', 'Djibouti': '吉布提', 'Denmark': '丹麦', 'Algeria': '阿尔及利亚',…

2020-08-12 0 Comments 993 Views 0 Like IT Team Read more

Joining all column values into one string with concat function is a pain, especially for a table with large numbers of columns. This script concats all columns quickly. declare @sql varchar(max) SELECT @sql = ISNULL( @sql+ '' + '+'',''+', '') + 'convert(varchar(50), [' + c.name + '])' FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'herb' and c.name!='id' EXEC( 'SELECT ' + @sql + ' FROM herb') Result The script generates long string value of @sql as following: convert(varchar(50), [alias])+','+convert(varchar(50), [application])+','+convert(varchar(50), [attachment])+','+convert(varchar(50), [category])+','+convert(varchar(50), [caution])+','+convert(varchar(50), [chemical])+','+convert(varchar(50), [desc])+','+convert(varchar(50), [english])+','+convert(varchar(50), [enterdate])+','+convert(varchar(50), [extract])+','+convert(varchar(50), [form])+','+convert(varchar(50), [form1])+','+convert(varchar(50), [grow])+','+convert(varchar(50), [guijing])+','+convert(varchar(50), [image_all])+','+convert(varchar(50), [image_leaf])+','+convert(varchar(50), [image_root])+','+convert(varchar(50), [indication])+','+convert(varchar(50), [indication1])+','+convert(varchar(50), [locations])+','+convert(varchar(50), [medicalprop])+','+convert(varchar(50), [name])+','+convert(varchar(50), [notes])+','+convert(varchar(50), [origin])+','+convert(varchar(50), [pharmacology])+','+convert(varchar(50), [pinyin])+','+convert(varchar(50), [process])+','+convert(varchar(50), [source])+','+convert(varchar(50), [store])+','+convert(varchar(50), [url])+','+convert(varchar(50), [usage]) 2,598 total views

2020-05-31 0 Comments 1109 Views 1 Like IT Team Read more

Problem The second line contains pingying and alias. html code: Zhōngfǔ   肺之募穴 First try get data roughly: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace(" ", " "); Result: Zhōngfǔ 肺之募穴 Split the alias column in MySQL: UPDATE `xuewei` set pingying=substring_index(alias,' ',1); UPDATE `xuewei` set bieming=substring_index(alias,' ',-1); Split the column in process: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace(" ", " "); var arr = alias.Split(' '); pingying = arr[0]; alias = arr[arr.Length-1]; //get last item in the array for the space in between is uncertain. Save to database: await conn.ExecuteAsync( $"INSERT IGNORE INTO xuewei (name,pingying,alias,position,anatomy,indication,operation,imagegeneral,imagepoint) VALUES " + $"('{data.Name}', '{data.Pingying}', '{data.Alias}', '{data.Position}', '{data.Anatomy}', '{data.Indication}', '{data.Operation}', '{data.ImageGeneral}', '{data.ImagePoint}');"); 1,953 total views

2020-05-18 0 Comments 700 Views 0 Like IT Team Read more

Content data on webpage: It's html code looks like this: Parsing data using regular expression with named group. Use foreach loop to avoid the order change on page. Do not use the text value from DOM function/property, because the text value does not contain html tag, which gives you the necessary mark on data. 14,070 total views, 5 views today

2020-05-17 3 Comments 3403 Views 0 Like IT Team Read more

Download Visual Paradigm Community https://www.visual-paradigm.com/download/community.jsp?platform=windows&arch=64bit Install Visual Paradigm Online Training https://www.visual-paradigm.com/training/visual-paradigm-essential/ User Guide User Guide 2,982 total views

2020-05-14 0 Comments 888 Views 0 Like IT Team Read more

1…3 456 7…18

SQL Server Transaction Exception Handling Best Practice

Accessing private fields in C#

MySQL urlencode/urldecode that support chinese chars

Crawling 51job in Python

WordPress EventCalendar visibility

Python Data Visualization Practise

concat all columns in SQL Server

Crawler - Split column

Crawler - get data from unwrapped html content

Visual Paradigm v16.1 Installation