HashFlare
IT圈子 - IT CONSULTANT

c#:
Xamarin

HTML5:
Intel XDK
PhoneGap
Sencha Touch

HTML&Ruby:
rhomobile

Node JS Framework:
JXcore
expressjs
Mojito
Meteor
Derby
Mean.js

Game Engine:
Unity
MoSync
Edgelib
Emo
Unreal Development Kit
ShiVa3D
Libdx
Papaya Social Game Engine
NME
SIO2

Other:
LiveCode
Corona SDK

LinkedIn 使用Node.js+html5 开发手机app

Exclusive: How LinkedIn used Node.js and HTML5 to build a better, faster app


 

MPA (Multi-page Application)  SPA (Single-page Application) http://www.thinksaas.cn/topics/0/380/380109.html

1. introduce git flow

the development will happen in feature branches, here we have features for future release and feature for next release, when programmer start to develop a module or a feature, he will create a new feature branch initialled from develop branch, once done the development, feature branch owner will do the merge to develop, after develop branch has some features, it will be merged to release branch, and QA will do the testing, if any issue is found, it will be fixed in release and merge back to develop, and once the release branch is fully tested, someone will create a pull request for merging to master, after reviewer reviewed the code changes, he may approve the request and do the merge, ideally master branch should be ready for product deployment at any point of time, but if any defects or urgent changes required, we will do it in hotfix branch,once done, it will be merged to master branch and merge back to develop branch as well.
2. common scenario – git merge & git rebase
Consider what happens when you start working on a new feature in a dedicated branch, then another team member updates the develop branch with new commits. This results in a forked history.
Assume now you have done the development work on feature,  you want to merge it to develop branch: how many options you have: cherry-pick or merge or rebasing+merge

because cherry-pick is not a structured way to do the merge, so I’m not going to discuss it.

this is what happens behind the scene when you git merge in command line or use GUI tool.

it will merge between  C3&C4 and their most recent common ancestor C2 and create a new commit C5

rebase works by going to the common ancestor of the two branches C2, getting the diff introduced by each commit of feature C4 , saving those diffs to temporary files, resetting feature branch to the same commit as develop (the branch you are rebasing onto), and finally applying each change in turn C4 prime.Now you switch to develop branch, and do git merge, it will do a fast-forward merge.

but what will happen if someone updated develop branch in between, I mean between you have done rebase and start to do merge on develop.

if your purpose is to keep a linear clean commit history on an active branch, rebase may not be that helpful.

 

3. when to use rebase
Bad practice

rebase develop onto your feature.
The problem is that this only happened in your repository. All of the other developers are still working with the original develop. Since rebasing results in brand new commits, Git will think that your develop branch’s history has diverged from everybody else’s.
Golden rule – never use it on public branch(Is anyone looking at this branch?)

Good practice (go to previous slide)
Now, let’s say that the new commits in develop are relevant to the feature that you’re working on.
One typical situation,
when someone has done a hot-fix for master,
by right, this hot-fix will be merged back to develop branch,
and you cannot continue with your development without this hot-fix in your feature branch,
then you may consider using rebase to get latest from develop
To incorporate the new commits into your feature branch,.

most of the time you may work solely on one feature branch, but if more than one developer working on same feature branch

you want to keep updated with latest changes or you want to keep a clean graph/commit history

conclusion:

conflict is inevitable, by adopting appropriate merging strategy, we can mitigate the risk of getting too many conflicts.
another suggestion is always examine your current status, which branch you are in,is there any other people working on this branch, and are you behind remote branches or ahead of remote branches with how many commits
hopefully you may have a better understanding of the merging strategy and understand what happens behind the scene.

https://git-scm.com/book/en/v2/Git-Branching-Rebasing
1. rebase is in local
all changes apply onto parent’s latest changes
then can do the merge
question: 1.how abt someone push new changes to parent branch
2.before rebase you pushed to remote, cannot do the fast-forward merge
2. merge in remote – pull request
latest changes and common ancestor changes
A forked commit history

active ‘parent’ branch
https://www.atlassian.com/git/tutorials/merging-vs-rebasing/conceptual-overview

git rebase is good for local cleanup,
local cleanup? team policy?
https://www.atlassian.com/git/articles/git-team-workflows-merge-or-rebase/

don’t commit so frequently, like writing 1 line code then commit, other people who commit later will suffer

 

http://flsilva.com/blog/git-branching-and-merging/

git: fetch and merge, don’t pull

Below is my hand write architecture solution for a booking system.

architect_draft

related post: Architect ? SOA ?

Layering Guideline

https://msdn.microsoft.com/en-us/library/ee658109.aspx

https://msdn.microsoft.com/en-us/library/ee658090.aspx

Three-Layered Services Application

https://msdn.microsoft.com/en-us/library/ff648105.aspx

 

Caching Layer

http://www.asp.net/web-forms/overview/data-access/caching-data/caching-data-in-the-architecture-cs

https://msdn.microsoft.com/en-us/library/dd129907.aspx

https://msdn.microsoft.com/en-us/library/dd129907.aspx

Security Layer

https://masteringmean.com/lessons/410-Handling-Crosscutting-Concerns

Business Process vs Business Logic

http://knut.hinkelmann.ch/lectures/bpm2013/09-1_BP_BusinessLogic.pdf

Integration layer

https://msdn.microsoft.com/en-us/library/ff647962.aspx

https://www.opengroup.org/soa/source-book/soa_refarch/integration.htm

portal integraion ? SSO?

http://slideplayer.com/slide/5773197/

 

Q&A:

Service oriented architecture – Transport layer (http vs messaging)

http://stackoverflow.com/questions/13863761/service-oriented-architecture-transport-layer-http-vs-messaging

now let me walk you through these steps to get some coins, or you can scroll to the bottom to watch the video

以下是具体步骤,你也可以滑到底部看视频讲解
1. Click me to register an Account 点击我注册并登录alfa1
2.Verification 首次需要验证
2.1 Mobile verification 手机验证verification-2
verification-3
2.2 ID verification 身份验证(身份证/护照等都可以)verification-4
2.3 Address Verification 地址验证(拍照或扫描上传你水电费账单等的图片格式,只要包含你地址就行,其实用别人的也行,只要有地址,但是别人的姓名不要显示出来)verification-5

alfa6Now you are verified, you can start making transaction 验证到此成功,可以开始交易了

3. Update your wallet 更新添加你的钱包地址

eth

4. Place Order 购买

alfa3 alfa4 alfa5

5. verify your transaction record 交易完成,如何验证

order

verify

优酷:

 

youtube:

Although this Articles simply collects and gathers links for all front-end developer’s or designer’s reference, but it’s really important to introduce or remind us about ‘Wireframing’.

Basics:
• What's Wireframes and why is it important

A website wireframe, also known as a page schematic or screen blueprint, is a visual guide that represents the skeletal framework of a website.

网站的线框 ,也被称作页面蓝图或页面原理图,是展现网站页面的结构的可视化指导。
Wireframes are created for the purpose of arranging elements to best accomplish a particular purpose. The purpose is usually being informed by a business objective and a creative idea.

创建的线框用于更好的布局页面元素以实现特定的设计目标,而设计目标则是通过业务目标和创新想法来引导。
The wireframe depicts the page layout or arrangement of the website’s content, including interface elements and navigational systems, and how they work together.

线框描绘了页面布局或布局了页面的内容,包含界面元素和导航元素以及他们如何一起工作。

The wireframe usually lacks typographic style, color, or graphics, since the main focus lies in functionality, behavior, and priority of content.In other words, it focuses on what a screen does, not what it looks like.

线框通常缺乏印刷风格、色彩和图形,由于主要是集中在功能性,行为和优先级。换句话说,它专注于屏幕的功能,而不是它的外观。
Wireframes can be pencil drawings or sketches on a whiteboard, or they can be produced by means of a broad array of free or commercial software applications. Wireframes are generally created by business analysts, user experience designers, developers, visual designers and other roles with expertise in interaction design, information architecture and user research.

线框可以是白板上的铅笔画或草图,或者可以通过广泛的免费或商业软件等应用程序制作生成。 线框通常由业务分析师,用户体验设计师,开发人员,视觉设计师和其他在交互设计,信息架构和用户研究方面具有专长的人员创建。

profilewireframe

Wireframes focus on:

The range of functions available
The relative priorities of the information and functions
The rules for displaying certain kinds of information
The effect of different scenarios on the display
The website wireframe connects the underlying conceptual structure, or information architecture, to the surface, or visual design of the website.
Wireframes help establish functionality, and the relationships between different screen templates of a website. An iterative process, creating wireframes is an effective way to make rapid prototypes of pages, while measuring the practicality of a design concept. Wireframing typically begins between “high-level structural work—like flowcharts or site maps—and screen designs.”
Within the process of building a website, wireframing is where thinking becomes tangible
–https://en.wikipedia.org/wiki/Website_wireframe

Course&Certificate/课程&认证:

UI/UX Design

why is it important?
http://45royale.com/blog/importance-wireframing/

• The Future of Wireframes

Time to re-assess and understand how to make good use of Wireframes:
https://madebymany.com/blog/the-future-of-wireframes

• Wireframing Is Not a Religion

Wireframing is an important design method, a digital design board, you can amend or wipe out anytime
http://52weeksofux.com/post/866406780/wireframing-is-not-a-religion

• Five Commandments for Wireframing

Paul Boag is a fan of Wireframing, he believes that it is one necessity part of design
https://boagworld.com/design/wireframing-rules/

• 20 Steps to Better Wireframing

Possibly the biggest mistake in any development project is failure to plan.
http://blog.teamtreehouse.com/20-steps-to-better-wireframing

Some Resources and Round-Ups
• Tools

Opensource: http://pencil.evolus.vn/Default.html

COTS: viso, etc.

• I Love Wireframing
 http://wireframes.tumblr.com/
 一个tumblog专注于线框,原型,实物设计。
• Wireframe Showcase
 http://www.wireframeshowcase.com/
 在这个网站可以查看一些基于线框设计的网站,可分析学习设计人员是如何将实体模型转化为设计的。因为线框和设计都是由设计者自己上传的,每个线框设计都包含了一个简短的说明。大部分的网站原来是数字实物模型,有容易修改和重新设计的优势。起结果是令人愉快的线框设计。
• Standard Screen Patterns for Web Interface Design

http://designingwebinterfaces.com/designing-web-interfaces-12-screen-patterns
一些互动设计的原则和模式

• 50 Free UI and Web Design Wireframing Kits and Resources

https://www.smashingmagazine.com/2010/02/50-free-ui-and-web-design-wireframing-kits-resources-and-source-files/
这篇文章关注一些线框工具和独立的应用程序,包括一些你可以用来制作自己线框图的一些工具:线框工具集,浏览器窗口,表单元素,网格,Mac OS X元素和手机元素,你可以在任何图形编辑器中使用他们,PS,AI,甚至纸笔工具。

• A Collection of Printable Sketch Templates and Sketch Books for Wireframing

http://www.geekchix.org/blog/2010/01/03/a-collection-of-printable-sketch-templates-and-sketch-books-for-wireframing/
20个资源,用于应用程序开发的草图阶段。

• A Collection of Printable Web Browser Sketching and Wireframe Templates

https://speckyboy.com/a-collection-of-printable-web-browser-sketching-and-wireframe-templates/
这些可打印的草图模板都是特意为web设计人员设计的。附加有浏览器的版本说明(Safari,Chrome甚至Firefox)

• Useful(Offline) Utensils and Tookits for Designers

http://www.hongkiat.com/blog/useful-offline-utensils-and-toolkits-for-designers/

Free Wireframing Kits, UI Design Kits, PDFs and Resources – Smashing Magazine_files
当你可以使用一个预制指南来节省时间,更好的发挥你的创造力,为什么还要从头开始设计呢?在这篇文章中,你将得到很多很棒的免费工具,一个笔记集合以及一些要购买的产品来帮助你线下设计。

tips: copy and paste as value/formula/transpose(row to column, column to row)

Sheet and cell: =Sheet1!$A$2:$A$8

1.How to convert column list to comma separated list in Excel?


Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub

 –https://www.extendoffice.com/documents/excel/1544-excel-convert-column-to-comma-separated-list.html
2.How to quickly apply formula to an entire column or row with/without dragging in Excel?
here I recommend copy the formula and then select a starting cell and scroll to the last cell you want to apply and hold down shift+select the last cell, paste,
because for excel’s nature of infinite scrolling in vertical direction, if you do fill down to apply formula, it may apply to lots of empty cells you don’t want to apply to, that will cost high memory usage, and in some case excel may get crashed.
https://www.extendoffice.com/documents/excel/867-excel-apply-formula-to-entire-column-row.html
How to remove prefix / suffix from multiple cells in Excel?
https://www.extendoffice.com/documents/excel/2915-excel-remove-prefix-suffix.html
3.Use AutoSum to sum numbers
https://support.office.com/en-us/article/Use-AutoSum-to-sum-numbers-543941e7-e783-44ef-8317-7d1bb85fe706
4.Find mismatch value comparing two column

IF(ISERROR(MATCH(E2,$C$2:$C$13363,0)),”na”,E2)

5.SumIF
https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
6. Vlookup
https://www.timeatlas.com/vlookup-tutorial/
7. Match Index
quickly find corresponding values based on comparing the other columns.
=INDEX($F$2:$F$100,MATCH(C2,$E$2:$E$100,0))
http://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/
8.Transpose/convert multi column to single column
https://www.extendoffice.com/documents/excel/1172-excel-transpose-multiple-columns-into-one-column.html
column to table: http://www.cpearson.com/EXCEL/ColumnToTable.aspx
9.Charts
Scatter Chart – Format Data Label – Label options – select Range
SUMIF
How to use SUMIF in Excel – formula examples to conditionally sum cells https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/
https://www.techonthenet.com/excel/formulas/sumif.php
https://support.microsoft.com/en-us/help/260415/sumif,-countif-and-countblank-functions-return-value-error
10. Group and Subtotal
Data-> subtotal
https://www.gcflearnfree.org/print/excel2013/groups-and-subtotals?playlist=Excel_2013
11. value (long number string or date time) as Text
method 1: import as csv, select the column, change the type to text
method 2: Text()
https://www.extendoffice.com/documents/excel/2476-excel-convert-date-to-text-or-number.html
12 group by sum
https://www.extendoffice.com/documents/excel/4970-excel-sum-by-group.html
=IF(A2=A1,””,SUMIF(A:A,A2,B:B)), (A2 is the relative cell you want to sum based on, A1 is the column header, A:A is the column you want to sum based on, the B:B is the column you want to sum the values.) Press Enter key, drag fill handle down to the cells to fill the formula.

problems:

#to prevent possible loss of data excel cannot shift non-blank cells off of the worksheet
remove the formats
#before copy paste, make sure that you want to copy the function or value only, otherwise excel by default will apply functions if you have to all cells you paste to, that will cost much longer time and more memory
#stop excel converting the value
1). Change the file extension from .csv to .dif
2). Double click on the file to open it in Excel.
3). The ‘File Import Wizard’ will be launched.
4). Set the ‘File type’ to ‘Delimited’ and click on the ‘Next’ button.
5). Under Delimiters, tick ‘Comma’ and click on the ‘Next’ button.
6). Click on each column of your data that is displayed and select a ‘Column data format’. The column with the numeric value should be formatted as ‘Text’.
7). Click on the finish button, the file will be opened by Excel with the formats that you have specified.
http://stackoverflow.com/questions/137359/excel-csv-number-cell-format
#whitespace, trim not work ?
 Substitute(A1,char(160),””)
remove stubborn spaces and characters in a cell
https://www.sageintelligence.com/tips-and-tricks/excel-tips-tricks/2014/07/remove-stubborn-spaces-cell/
DATA->Text to Columns
ref:
https://blogs.office.com/excel/
Used Script:
IF(ISERROR(MATCH(E2,$C$2:$C$13363,0)),”na”,E2)
=INDEX($f$2:$f$120,MATCH(A2,$e$2:$e$120,0))
INDEX($J$2:$J$239,MATCH(A2,$I$2:$I$239,0))
CONCATENATE(“exec somefunction(‘”,A2,”‘,”,(0-F2),”,”,C2,”);”)
=LEFT(A3,LEN(A3)-6)

1.sdcard issues

a. if your sdcard more than 32G, windows disk manager may not be able to format it correctly, I am not sure abt mac and other os

b. if you find there are some unallocated spaces, then your sdcard may has been corrupted due to some improper operations, then you definitely need to delete the partition and repartition it.

c. remember partition it as primary not logic, and better choose FAT32 format

format tool: https://www.sdcard.org/downloads/formatter_4/eula_windows/index.html

partition tool: http://www.partition-tool.com/partner/app.htm

2.Images:

images for miner: http://cryptomining-blog.com/tag/raspberry-pi-mining/

Setup your Raspberry Pi as mining device controller

ubuntu mate for raspberry https://ubuntu-mate.org/raspberry-pi/

run standard raspbian on pi-top

https://www.raspberrypi.org/forums/viewtopic.php?f=29&t=149151&p=990308

https://github.com/rricharz/pi-top-install

run kali on raspberry

Installing and Troubleshooting Kali Linux On Raspberry Pi

multiple boot with berryboot (predefined os: rasbian/ubuntu mate,core/kali,etc.

http://www.geekfan.net/5244/    

www.howtogeek.com/141325/how-to-multi-boot-your-raspberry-pi-with-berryboot/

add custom os www.howtogeek.com/141325/how-to-multi-boot-your-raspberry-pi-with-berryboot/

www.youtube.com/watch?v=GYamNsUXC8M

https://forums.kali.org/showthread.php?449-Booting-Kali-Linux-with-Berryboot

http://www.berryterminal.com/doku.php/berryboot

ethereum version: ethembedded.com/?page_id=171

http://www.multibootpi.com/

3.Power supply

5V 2.1A

if power brownout you will see a lighting bolt on top right corner.

Don’t use usb connected your computer, it may burn your motherboard, be carefully when use phone power supply, USB connectors normally imply 5V (but note that some cheap USB connected chargers [not “power supplies”] may be unregulated, and when lightly loaded may output more than 5Volt, even 6Volt or more),

generally all micro USB cables should be adhering to the USB standards and output at around 5V, but we specifically mention the 5V to make sure people are checking the voltage levels put out by their charger before blindly plugging it in.So once we’re sure people make sure it’s a 5V PSU, since they’re already looking down there, check for how many mA (or Amps) it puts out.  The Model B needs ~700mA (0.7A) to run.  As such it will not run off your computer’s USB port as that only provides 500mA. –https://www.raspberrypi.org/forums/viewtopic.php?f=5&t=4812

http://raspberrypi.stackexchange.com/questions/26705/will-any-external-battery-power-a-raspberry-pi
http://raspi-ups.appspot.com/en/index.jsp

power supply switch https://www.pi-supply.com/product/pi-supply-raspberry-pi-power-switch/?v=79cba1185463

4.Settings:

1. network

A. wifi connected, no internet access

B. cannot connect to wifi

C. static ip address

check points:

ethnet wlan

ifconfig wconfig

/etc/network/intefaces

/etc/wpa_suppliant/wpa_suppliant.conf

/etc/resolv.conf

sudo ifdown wlan0

sudo ifup wlan0

https://www.raspberrypi.org/documentation/configuration/wireless/wireless-cli.md

dns flush: sudo apt-get nscd     sudo /etc/init.d/nscd restart

http://raspberrypi.stackexchange.com/questions/4275/dns-resolution-failure

https://www.raspberrypi.org/forums/viewtopic.php?f=91&t=31238

http://askubuntu.com/questions/572152/i-cant-access-the-internet-through-my-raspberry-pi-when-connected-through-ssh

https://www.raspberrypi.org/forums/viewtopic.php?t=23344

https://www.raspberrypi.org/forums/viewtopic.php?f=91&t=98903

How to Set Up WiFi on the Raspberry Pi

www.circuitbasics.com/raspberry-pi-wifi-installing-wifi-dongle/

http://weworkweplay.com/play/automatically-connect-a-raspberry-pi-to-a-wifi-network/

raspberrypihq.com/how-to-add-wifi-to-the-raspberry-pi/

wifi country code

http://raspberrypi.stackexchange.com/questions/44183/wifi-country-code-resetting

https://www.raspberrypi.org/forums/viewtopic.php?f=28&t=81021

Easy Wireless Configuration with wicd

blog.bartbania.com/raspberry_pi/easy-wireless-configuration-for-raspberry-pi/

2.Resolution

5.Tools

tools for android phone user all you need are here: http://www.makeuseof.com/tag/6-android-apps-every-raspberry-pi-owner/

virtual keyboard: sudo  apt-get install matchbox

remote access: https://www.raspberrypi.org/documentation/remote-access/

6.Screen Display/Monitor

if your monitor supports multiple ports, config the correct one

monitor

Using Cell Phone Screens with any HDMI Interface

7.Cooling system

yes, you need, http://raspberrypi.stackexchange.com/questions/22928/does-the-raspberry-pi-need-a-cooling-system, https://www.zhihu.com/question/20767376

heat sink

water cooling

8.Cluster

9.Diagnosis

#pro01: ubuntu welcome to emergency mode

Using a VNC client that requests the wrong amount of colors, will crash the application (displaying an “emergency recovery shell” on screen).

http://www.berryterminal.com/doku.php/berryboot/headless_installation

http://raspberrypi.stackexchange.com/questions/37558/how-to-troubleshoot-a-headless-pi-that-boots-into-emergency-mode

https://ubuntu-mate.community/t/getting-emergency-mode-screen-on-boot-up-every-time/2626/3

https://ubuntu-mate.community/t/getting-emergency-mode-screen-on-boot-up-every-time/2626/5

https://www.raspberrypi.org/forums/viewtopic.php?f=56&t=124149

9.Run ssh on startup/boot

https://www.raspberrypi.org/forums/viewtopic.php?f=66&t=86950

http://raspberrypi.stackexchange.com/questions/1747/starting-ssh-automatically-at-boot-time

Other:

Build a RespNode http://raspnode.com/diyEthereumGeth.html#homenet

中文安装全记录: http://blog.csdn.net/iracer/article/details/51620051

pi-top install hands on

Hands on with Pi-Top, the Raspberry Pi Powered Laptop

1. cannot store into flash drive even the file size within its available space limits, try to format it to NTFS

U盘明明空间很大,但是无法存入东西,格式化成NTFS

2.boot from usb stick: cannot find usb drive in boot menu, it could be that your usb stick is not marked as active.

follow steps below to activate your flash drive

diskpart

Windows:

Administrative Tools

System Information

window partition – disk manager

ubuntu partition – use another linux system to do it ( because you cannot umount the disk when use let alone partition it)

 

SSH auto close – try admin, or remote server cannot connected ( try other machine out of your current intranet)

 

cannot find the menu?

try tab for computer

try hardware/software menu button or home button

 

google chrome freezes my laptop

1. Try clearing your cache and cookies (Ctrl+Shift+Backspace). Start by clearing from the time period you started facing the issue, then expand to “beginning of time”.
2. Uncheck Use hardware acceleration (Menu > Settings > Show Advanced Settings > System menu).

1.Setup oracle for .net developer:

download http://www.oracle.com/us/products/tools/utilsoft-087491.html

install.bat all <Path> odac

addling assembly to GAC: Gacutil.exe -i  Oracle.DataAccess.dll

adding assembly to GAC without Gacutil.exe: http://www.totaldotnet.com/sub/adding-assembly-to-gac-using-command-prompt-without-gacutil-exe

Problem you may encounter: The provider is not compatible with the version of Oracle client

This is Oracle.DataAccess Issue with .net and 64 bit

https://community.oracle.com/thread/3537075?start=0&tstart=0

2.Basic Usages:

1. using sql developer:

before debug need to ‘compile for debug’

missing connection menu: https://amoratech.wordpress.com/2011/09/25/sql-developer-3-0-3-0-04-34-connection-menu-is-missing/

Import from Excel into Oracle www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

2. Handle Exception

https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm

3.execute store procedure manually

var RESULT refcursor;

exec sp(‘id1;6?id2;2?id3;4?id5;11?id4:10’,:RESULT);

print RESULT;

4.update with inner join

http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle

5.using temp table

create temp table inside sp need extral permission
1)ON COMMIT DELETE ROWS 事务级
2)ON COMMIT PRESERVE ROWS 会话级

6.split string into temp table

http://stackoverflow.com/questions/27786412/oracle-stored-procedure-split-string-for-insert-or-update-to-a-table

http://stackoverflow.com/questions/29557207/split-string-and-iterate-for-each-value-in-a-stored-procedure

http://www.techonthenet.com/oracle/index.php

7. Transpose Column to Row 列转行

PIVOT https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

http://www.2cto.com/database/201206/136218.html

http://blog.sina.com.cn/s/blog_4adc4b090101ges3.html

8. Dynamic SQL

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm

       https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

Dynamic PL/SQL date parameter with time value retained https://stackoverflow.com/questions/18455867/dynamic-pl-sql-date-parameter-with-time-value-retained

9.Group

9.1 by having ***

Dates: Grouping date-time values into {n} minute buckets. http://steve-lyon.blogspot.sg/2011/08/bucketing-up-date-times-by-various.html

9.2 sum(decode(

9.3 common table expression

with r as (select username, productid, sum(amt) shoppingamt
from transactionlog where transdate = date’2014-06-11′
group by GROUPING SETS((username, productid), (productid))
)
select * from r where username is null;

with r as (select productid, sum(amt) shoppingamt
from transactionlog where transdate = date’2014-06-11′
group by rollup(productid)
)
select * from r ;

10.Package level&Schema level

Receive Array/Table as input

a.Type in database level – can be used as a nested table

create or replace TYPE   associateArray     IS   TABLE   OF   varchar2(100);

arrayDatabaselevel  :=new associateArray();

arrayDatabaselevel.extend(1);

arrayDatabaselevel(1) := ‘test’;

select * from table(arrayDatabaselevel)

b.Type in Package level – can only be used in loop

create or replace PACKAGE PKG_helloworld AS

TYPE   associateArray IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;

END PKG_helloworld ;

——————-

create or replace PROCEDURE test(

arrayPackageLevelin PKG_helloworld.associateArray

)…………………….

if arrayPackageLevelis not null then
for i in 1 .. arrayPackageLevel.count loop
if (arrayPackageLevel(i) is not null) then
name := regexp_substr(arrayPackageLevel(i), ‘[^,]+’, 1, 1); number := to_number(regexp_substr(arrayPackageLevel(i), ‘[^,]+’, 1, 3));date := to_date(regexp_substr(arrayPackageLevel(i), ‘[^,]+’, 1, 2),’yyyy-mm-dd hh24:mi:ss’);

c.Mixed

arrayDatabaselevel  := associateArray();
arrayDatabaselevel.extend(arrayPackageLevel.count);
for i in arrayPackageLevel.first .. arrayPackageLevel.last loop
arrayDatabaselevel(i) := arrayPackageLevel(i);
end loop;

!!important deadlock issue here

oracle_deadlock

11. regexp_substr https://www.techonthenet.com/oracle/functions/regexp_substr.php

12.Scheduler

https://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm

       https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12413

Scheduler Running log https://docs.oracle.com/cd/E18283_01/server.112/e17120/scheduse008.htm

13.Cross Join

http://www.w3resource.com/oracle/joins/oracle-cross-join.php

oracle-cross-join-pictorial-presentation

14.Merge Statement

merge into table t1 using  table t2 on (t1.id=t2.id)

when match then update set ***

when not match then insert ***;

15.analytic functions

–https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

select t1.id, min(amt) keep (DENSE_RANK FIRST ORDER BY datecreated desc) as amount
from money_transferlog t1
group by t1.id

16. exec store-procedure and export to csv file

set colsep ‘,’
var    input1 VARCHAR2(10);
var    input2 VARCHAR2(10);
var results REFCURSOR

begin
:input1 := ‘2016-01-01’;
:input2 := ‘100’;
storeProcedure(:input1,:input2,:results);
end;
/
spool /tmp/results.csv
print results;

17. Insert All when else

https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_9014.htm#i2111652

INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;

#################################################

declare
v_sql varchar2(5000);
begin
v_sql := q'[select
col1||’,’||
col2
from
(
select * from ***
)
order by col1]’;
dump_sql_to_csv(v_sql, ‘example.csv’);
end;
/

####################################################

declare
v_sql varchar2(2000);
result varchar2(2000);
result2 varchar2(2000);
thedate date;
begin
thedate:=date’2018-01-01′;
v_sql := ‘select :a – sysdate, sysdate-3 from dual’;
execute immediate v_sql into result,result2 using in thedate;
dbms_output.put_line(result||’,’||result2);
end;
/

Problem:

# :ORA-01861: literal does not match format string

e.g date format, need to convert

s_dateList(i):= to_date(dateList(i),’yyyy-mm-dd hh24:mi:ss’);

#-1 No Data found:

always handle exception for ‘select column into ***’

#0 insert special character :  ‘  &

http://www.blogjava.net/pengpenglin/archive/2008/01/16/175689.html

 

#1 parameter name cannot be the same with column name !!!

SELECT × FROM TABLE COL_NAME=INPUT_NAME

exact fetch returns more than requested number of rows

#2 date format

wrong: to_char(DateColumn, ‘yyyy-MM-dd hh:mm:ss‘)

correct: to_char(DateColumn, ‘yyyy-MM-dd hh24:mi:ss‘)

#3 Input is too long (> 2499 characters) – line ignored

Sol: if it happens in store procedure, simply make it multi lines instead one long single line

#4 no data found Exception

select sum(*) count(1) into won’t cause exception,

select <col> into <name> may cause error:ORA-01403: no data found

one typical scenario: let’s say we have two table t1 and t2 joined by userid, then in one sp you wrote like:

create or replace PROCEDURE
(
list in PKG.associateArray,
result out sys_refcursor
)
AS
counter int;
msg varchar2(1000);
BEGIN
if list is not null then
for i in 1 .. list.count loop
if (list(i) is not null) then
BEGIN
select count(1) into counter from t1 where t1.userid='**';
if(counter > 0) then
do something here
select col from t2 where t2.userid='***';
do somethingelse here...
end if;
END
end if;
end loop;
end if;
end ;

this is the common error we all may have write like that, correct one should be :


create or replace PROCEDURE
(
list in PKG.associateArray,
result out sys_refcursor
)
AS
counter int;
msg varchar2(1000);
BEGIN
if list is not null then
for i in 1 .. list.count loop
if (list(i) is not null) then
BEGIN
select count(1) into counter
from t1
inner join t2 on t1.userid=t2.userid
where t1.userid='**';
if(counter > 0) then
do something here...
select col from t2 where t2.userid='***';
do somethingelse here...
end if;
END
end if;
end loop;
end if;
end ;

or you can add exception handling into your sp.

#5 to_date error :ORA-01841: (full) year must be between -4713 and +9999, and not be 0

select regexp_instr(‘2016-08-01 23:59:59’, ‘\d{4}-\d\d-\d\d \d{2}:\d{2}:\d{2}’) from dual;

0 3421

official site: http://www.nodemcu.com

forum: http://bbs.nodemcu.com/

source code: https://github.com/nodemcu

 

Setup:

download: http://yunpan.cn/c3qQbdCTK7dtb  访问密码 ef05

win7 users may need cp2102 usb to uart bridge controller driver,

CP210x USB to UART Bridge VCP Drivers https://www.silabs.com/products/mcu/Pages/USBtoUARTBridgeVCPDrivers.aspx

 

 

RANDOM POSTS

0 1479
Although this Articles simply collects and gathers links for all front-end developer's or designer's reference, but it's really important to introduce or remind us...