Wednesday, November 30, 2016

database link between Vertica/non-Vertica database

Summarize some research about database link  between Vertica/non-Vertica database

As of now, DB links feature in not present in Vertica. We can not create Vertica database links like those that exist in Oracle and SQL Server. Moreover, we cannot run two databases simultaneously at one time in Vertica cluster.

But we can easily connect to another Vertica database if we need to to copy data between two Vertica databases. See the SQL Reference Manual:
CONNECT TO VERTICA database USER username PASSWORD 'password' ON 'host',port

For copy ORACLE data to Vertica, once can uses a unix shell script. Use COPY command in Vertica. In a shell script, pipe the output from oracle to Vertica in COPY command ( using input as STDIN) and then run the script. This shell script can also be used as STORED PROC in Vertica and running this Stored Proc from Vertica will pull the data from Oracle to Vertica.

For table join, if it requires join two tables in two Vertica databases, why not use two different schemas in same cluster/database  which can act as two different database.

Create Linkserver from SQL Server to Vertica via ODBC is possible, however there can have  performance concern.

Thursday, November 24, 2016

install 32bit instantclient ODBC for 64bit windows 2008 R2

One of my developer encountered similar issue in this nice article .

His creates Visual Basic form in MS Office Excel to pull data from Oracle database, while the Excel is 32 bits installed in his 64 bits windows 7 , hence requires 32 bits ODBC for this to work.

Launch 32bit ODBC configuration program (ODBC Data Source ) C:\Windows\SysWOW64, we can see there is "Microsoft ODBC for Oracle".  This is the same finding for his target depLoying server (windows 2008R2).  I was thinking no more 32bits  "Microsoft ODBC for Oracle" in windows 2008, which is wrong.

The server has 64 bits oracle client already installed for other application,

Luckily  I notice there is 32bit ODBC for 11g instantclient.  This is help to minimize the impact.

Follow the instantclient ODBC installation guide :
1. run odbc_install.exe as administrator in command prompt.
2. Append path of instantclient (e:\instantclient11203) to system environment variable PATH
3. create tnsnames.ora in e:\instantclient11203\netwoork\admin\

Missing part of the guide is : Launch 32bit ODBC configuration program (ODBC Data Source ) C:\Windows\SysWOW64\odbcad32.exe,

After this, my developer told me that his program can run suddenly.


MS ODBC for Oracle

Oracle ODBC Centre

Friday, September 30, 2016

SQL Server 2000 audit

There might be a requirement to audit failure logins.

sp_readerrorlog works in charm for version 2005 and above, however it is not in sql server 2000.

As it has different format for parameters. After spent some time, I finally find out how to use it correctly.

 Exec sp_readerrorlog 1,'D:\test.txt' ,  'world'   # grep all lines having the word

 Exec sp_readerrorlog 1,'D:\test.txt' , 3  # read 3rd line

 Exec sp_readerrorlog 1,'D:\mediator_dev\MSSQL$MED\LOG\ERRORLOG','Backup'


Login auditing - four option buttons - None, Success, Failure and All - under the "Audit level" heading on the Security tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager control level of SQL Server login audits. These choices correspond to values of 0, 1, 2 and 3 in the AuditLevel entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\ registry key AuditLevel of DWORD data type. This value determines whether login attempts with specified outcome (or any at all) will be recorded in the Windows Application Event Log and SQL Server error log.

It seems by default audit level is 0.


Wednesday, September 14, 2016

install nerdtree with vbundle for vim

1. preparation

    touch ~/.vimrc

2. install vbundle

git clone ~/.vim/bundle/vundle

3. add the following to .vimrc

set nocompatible               " be iMproved
filetype off                   " required!

set rtp+=~/.vim/bundle/vundle/
call vundle#rc()

" let Vundle manage Vundle
" required! 
Bundle 'gmarik/vundle'

" My Bundles here:

filetype plugin indent on     " required!
" Brief help
" :BundleList          - list configured bundles
" :BundleInstall(!)    - install(update) bundles
" :BundleSearch(!) foo - search(or refresh cache first) for foo
" :BundleClean(!)      - confirm(or auto-approve) removal of unused bundles
" see :h vundle for more details or wiki for FAQ
" NOTE: comments after Bundle command are not allowed.. 

4. install NERDTree, add the bundle name to .vimrc

call vundle#begin()

Bundle 'scrooloose/nerdtree'

call vundle#end()  

 5. re-launch vi, and type the :BundleInstall command to install the nerdtree.

6.  verify able to launch nerdtree, by typing ":NERDTree" . Note that the command is sensitive.

7. create hotkey  F2 for NERDTRee:  add below line to .vimrc.


some commands I learned easilly:
ctr+w+h  光标移到左侧树形目录,
ctrl+w+l 光标移到右侧文件显示窗口
ctrl + w + w    光标自动在左右侧窗口切换
   o 打开关闭文件或者目录

   t 在标签页中打开

  T 在后台标签页中打开

  ! 执行此文件

  p 到上层目录

  P 到根目录

  K 到第一个节点

  J 到最后一个节点
 k  previous
j  next 

tabs commands are what I am thinking to google,but already there.

:tabnew [++opt选项] [+cmd] 文件      建立对指定文件新的tab
:tabc   关闭当前的 tab
:tabo   关闭所有其他的 tab
:tabs   查看所有打开的 tab
:tabp   前一个 tab
:tabn   后一个 tab
and more to practice noted below.

ctrl + w + r    移动当前窗口的布局位置
go      在已有窗口 中打开文件、目录或书签,但不跳到该窗口
i       split 一个新窗口打开选中文件,并跳到该窗口
gi      split 一个新窗口打开选中文件,但不跳到该窗口
s       vsplit 一个新窗口打开选中文件,并跳到该窗口
gs      vsplit 一个新 窗口打开选中文件,但不跳到该窗口
!       执行当前文件
O       递归打开选中 结点下的所有目录
x       合拢选中结点的父目录
X       递归 合拢选中结点下的所有目录
e       Edit the current dif
双击    相当于 NERDTree-o
中键    对文件相当于 NERDTree-i,对目录相当于 NERDTree-e
D       删除当前书签
C       将选中目录或选中文件的父目录设为根结点
u       将当前根结点的父目录设为根目录,并变成合拢原根结点
U       将当前根结点的父目录设为根目录,但保持展开原根结点
r       递归刷新选中目录
R       递归刷新根结点
m       显示文件系统菜单
cd      将 CWD 设为选中目录
I       切换是否显示隐藏文件
f       切换是否使用文件过滤器
F       切换是否显示文件
B       切换是否显示书签

also reviewed commands to execute external commands.

:shell or just :sh  or Ctrl-z  and fg

:r textfile
:r ! ls -1 /home/user/directory

Installation on cygwin

Followed this article, I easily installed NERDTree  to the vim.

Installed more bundle from

In short time, I built up my own .vimrc as shown below.

hmc@hmc-P55A-UD3:~$ cat .vimrc
syntax enable
syntax on
set nocompatible              " be iMproved, required
filetype off                  " required

set rtp+=~/.vim/bundle/Vundle.vim
call vundle#begin()

Bundle 'scrooloose/nerdtree'
Bundle 'jistr/vim-nerdtree-tabs'
Bundle 'scrooloose/syntastic'
Bundle 'Valloric/YouCompleteMe'
Bundle 'vim-airline/vim-airline'
Bundle 'chriskempson/base16-vim'
Bundle 'majutsushi/tagbar'

call vundle#end()            " required
filetype plugin indent on    " required

nmap :NERDTreeToggle
let g:nerdtree_tabs_open_on_console_startup=1
map n NERDTreeTabsToggle

" for YCM

" 设置跳转到方法/函数定义的快捷键
 nnoremap j :YcmCompleter GoToDefinitionElseDeclaration
 " 触发补全快捷键
let g:ycm_key_list_select_completion = ['', '', '']
let g:ycm_key_list_previous_completion = ['', '', '']
let g:ycm_auto_trigger = 1
" 最小自动触发补全的字符大小设置为 3
let g:ycm_min_num_of_chars_for_completion = 3
" YCM的previw窗口比较恼人,还是关闭比较好
set completeopt-=preview


Wednesday, September 07, 2016

cut and paste for vi

--For Solaris, AIX , HP-UX platform

shift + v    # for copy to clipboard
dd             # delete the line
p               # paste

--For Linux platform

shift + v
d            # delete the line, slightly different with Solaris


Monday, August 29, 2016

laztoapk demo compliation error: cannot find crtbegin_so.o

with the help of laztoapk v0.9.0.35, which speeds up the setup in windows platform.

However, I hit below error when complile the example project.

D:\laztoapk\downloads\laz4android\fpc\3.0.0\bin\i386-win32\arm-linux-androideabi-ld.bfd.exe: cannot find crtbegin_so.o

after reading  

I notice the library path in default value of "project option/compilation option/path / library " is not correct, where it is

they are not my actual path,  Missing the "prefix" path  -   "D:\laztoapk\downloads"  for my environment.

Corrected them as shown below, and recompilation succeed!


 and I contributed the answer to,22414.30.html

Hope this help the author if this is a bug.

Wednesday, August 17, 2016

Integration of Oracle Client and Lazarus

In Linux64/Windows64, when launch Lazarus 1.6, it reports can't find oci.dll. And even worse in Windows64, the TOracleConnection icon is not found in sqldb component tab. (While in windows32, at least I can see component tab).

From these symptoms, I conclude something wrong with the integration between Lazarus and Oracle client.

Googled one article says, 11g client is buggy, while 12c works better.  As such, I upgraded my oracle client from to to prevent this issue.  (No time to verify his finding)

From some more articles related to install Oracle InstantClient, I realize the environment variable LD_LIBRARY_PATH is the key to tell Lazarus where to find the relevant library file like oci.dll.

I compared and found oci.dll is under $ORACLE_HOME/bin, (surprisingly not $ORACLE_HOME/lib ), thus I set LD_LIBRARY_PATH to the bin directory.  with this setting I see no more missing oci.dll message.

Next, I follow the same article to continue fixing the missing TOracleConnection component.  step1, 2, 3, compiling ... ,  restart IDE, Finally I see the icon there.

Big Thanks  to

Here, add some more info related to his step 3.

Step 3: Enabling the TOracleConnection in your Lazarus installation again. You have to go to the "Package" menu entry. Select "Open loaded Package". In the dialog you select the package "sqldblaz". Now click "add" and go to the tab "Add files". Then browse to the file "oracleconnection.pp" under "[Lazarus directory]\fpc\2.6.2\source\packages\fcl-db\src\sqldb\oracle" and add it to the package. The next step is, to change the file "registersqldb". Search for the first occurence of 
It's in an {$IFNDEF WIN64} section. You need to move it out there. I moved it under the line 
. Then save the file, compile the package via "compile" button. If it compiled successfully, click the button "use" and then "install". Now your Lazarus should be build new. After strating the IDE again, 

The registersqldb is  D:\lazarus64\components\sqldb\registersqldb.pas, and my modification to file as shown in the screenshot.