Monday, August 29, 2011

Excel: Data validation - select a value from a list from other sheet

You could select a values for a list in other sheet. Steps for MS Office 2007 are:
1. Select the cell you want to change.
2. Data -> Data Validation -> Settings
3. Field: Allow = List
4. In Source field, enter a value similar to:

=Sheet2!$A$2:$A$5

This is the range from Sheet2 cells A2 to A5

The result is a dropdown for the value of the cell.

Tuesday, August 16, 2011

Database initialization and getting the last db schema - .bat file template

Batch file template:

@echo Setting database configuration...
@call db-config.bat
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
@echo .
@echo .
@echo .
@echo .
@echo .
@echo .
@echo .
@echo .
mysql -u %USER% --password=%PASS% --execute="DROP database %WORKING_DB%;"
@rem IF NOT %ERRORLEVEL% == 0 GOTO ERROR
mysql -u %USER% --password=%PASS% --execute="CREATE database %WORKING_DB%;"
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
mysql -u %USER% --password=%PASS% %WORKING_DB% < %MYMYSQLDIR%schema.sql @IF NOT %ERRORLEVEL% == 0 GOTO ERROR mysql -u %USER% --password=%PASS% %WORKING_DB% < %MYMYSQLDIR%update.sql @IF NOT %ERRORLEVEL% == 0 GOTO ERROR @rem mysqldump -u %USER% --password=%PASS% %WORKING__DB% --skip-add-drop-table --no-create-info --complete-insert --skip-triggers > %DB_DIR%latest-schema.sql
mysqldump -u %USER% --password=%PASS% %WORKING_DB% --skip-add-locks --skip-disable-keys --skip-set-charset --skip-triggers --skip-quote-names > %DB_DIR%latest-schema.sql
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR

@echo Seems everything to be fine, end with success
@Goto :END

:ERROR
@echo Error, stopping script...
@rem Pause if the script is run in a window (not from command prompt)
@ping -n 10 localhost > nul
@exit /b 1

:END
@exit /b 0


File db-config.bat contains some SET commands, for example:

@SET MYMYSQLDIR=G:\projects\xxx\etc\db\mysql\
@SET USER=root
@SET PASS=superlongandsecretpassword
@SET WORKING_DB=wb2
@exit /b 0

Monday, August 15, 2011

Modify primary key syntax

MySQL:

ALTER TABLE your_table DROP PRIMARY KEY, ADD PRIMARY KEY ( some_field, other_field );


There is no problem some_field to be AUTO_INCREMENT.

Friday, August 12, 2011

Easier Command prompts recognition

If you use more that one Command Prompts in Windows, you probably mess them up. An easy way to distinguish them is with color command. Write in each of them color command with different arguments. Letter color changes, yeah!

Command Prompt window #1:
color A

Command Prompt window #2:
color B

Functions in DOS Batch files

A nice tutorial:

http://www.dostips.com/DtTutoFunctions.php

MS DOS batch tips

So, by way of tribute to the dying art of the DOS Batch file, I present my top ten batch file tricks:

1 Use PUSHD / POPD to change directories
Read Scott Hanselman's writeup on PUSHD. The basic idea is that it keeps a stack, so at the simplest level you can do something like this:

PUSHD "C:\Working Directory\" ::DO SOME WORK POPD

That allows you to call the batch file from any directory and return to the original directory when you're done. The cool thing is that PUSHD can be nested, so you can move all over the place within your scripts and just POPD your way out when you're done.

Thursday, August 11, 2011

Generating JAR via batch file with error checks

@SET JAR=miteff.com.jar
del %JAR%
cd some-folder\
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
del *.class
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
javac *.java
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
cd ..\..\..
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
javac *.java
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
jar -cf %JAR% .
@IF NOT %ERRORLEVEL% == 0 GOTO ERROR
@dir %JAR%
IF NOT %ERRORLEVEL% == 0 GOTO ERROR
@echo Copy the jar to dest folder...
@copy /y %JAR% X:\

@echo Seems everything to be fine, end with success
@Goto :END

:ERROR
@echo Error, stopping script...
@rem Pause if the script is run in a window (not from command prompt)
@ping -n 10 localhost > nul

:END

Importing comma separated values in mysql

mysql -uroot -p --execute="USE databasename;LOAD DATA INFILE 'd:/path/sql-file.txt' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'; SHOW WARNINGS"


There are a lot of problems which comma separated files make. For example:
- new lines
- commas in field

This is not recommended for migration between different kinds of databases (MySQL <-> Oracle)

Note: mysqlimport is not recommended, because it cannot show the warnings (it is feature request from year 2005, so i suppose will not be implemented)

Tuesday, August 9, 2011

Importing Oracle databases from binary dump

imp full=yes file=file-name.sql

You are not sure that file you have is in "imp" format? Check the header (birst bytes) of the file. Oracle imp binary dumps have a format similar to:

??EXPORT:V10.02.01
DSYS
RUSERS
4096
0
32
0