Interact window service with command line

Window service can be started at system boot, or at any other time, without the
need for any wrapper code to start the service. The service can be managed
using command-line tools ("net start",
"net stop", or sc.exe) or GUI tools (the Services administrative tool).

Installation

Most Windows OSes derived from Windows NT (such as Windows XP, Windows 2000,
Windows 2003 Server) provide a command-line tool for installing
services, called SC.EXE for "Service Control". To create a service for
svnserve, use SC.EXE:

sc create
binpath= "c:\myservice.exe --service "
displayname= "My Service Name"
depend= Tcpip //* explain this line later

where is any service name you want, e.g. "My Service Name", and
are the arguments to svnserve, such as --root,
--listen-port, etc. (All of this should be specified on a single
line, of course.)

If the path to the service contains spaces or other characters that
must be escaped, then you must enclose the path to it with
double-quotes, which themselves must be quoted using a backslash.
Fortunately the syntax is similar to that on Unix platforms:

sc create
binpath= "\"c:\program files\My Service\bin\myservice.exe\" ..."

SC has many options; use "sc /?". The most relevant are:

sc create create a new service
sc qc query config for a service
sc query query status
sc delete delete any service -- BE CAREFUL!
sc config ... update service config; same args as sc create
sc start start a service (does NOT wait for completion!)
sc stop stop a service (does NOT wait for it to stop!)

Note that the command-line syntax for SC is rather odd. Key/value
pairs are specified as "key= value" (without the double-quotes). The
"key=" part must not have any spaces, and the "value" part MUST be
separated from the "key=" by a space.

If you want to be able to see the command shell, add these arguments
to the "sc create" command-line:

type= own type= interact

This sets the "interactive" bit on the service, which allows it to
interact with the local console session.

You can create as many services as you need; there is no restriction
on the number of services, or their names. I use a prefix, like
"msv.foo", "msv.bar", etc. Each service runs in a separate process.
As usual, it is your responsbility as an administrator to make sure
that no two service instances use the same repository root path, or
the same combination of --listen-port and --listen-host.


Uninstalling
------------

To uninstall a service, stop the service, then delete it, using "sc
delete ". Be very careful with this command, since you can
delete any system service, including essential Windows services,
accidentally.

Also, make sure that you stop the service before you delete it. If
you delete the service before stopping it, the Service Control Manager
will mark the service "deleted", but will intentionally not stop the
service. The service will be deleted when the system reboots, or when
the service finally exits. After all, you only asked to delete the
service, not to stop it.


Automatically Starting Service on System Boot
---------------------------------------------

By default, SC creates the service with the start mode set to "demand"
(manual). If you want the service to start automatically when the
system boots, add "start= auto" to the command line. You can change
the start mode for an existing service using "sc config start=
auto", or also by using the Windows GUI interface for managing
services. (Start, All Programs, Administrative Tools, Services, or
just run "services.msc" from Start/Run or from a command-line.)

* Note: If the service had dependecies, in order for it to start correctly
on system boot, you must properly declare its startup dependencies.
The Service Control Manager will start services as early as it can,
and if you do not properly declare its startup dependencies ( ex. tcp/ip ),
it can potentially start before the TCP/IP stack has been started.
This is why you must provide specify 'depend= Tcpip' to SC.EXE when
creating the service in above example.


Starting and Stopping the Service
---------------------------------

You start and stop the service like any other Windows service. You
can use the command-line "net start ", use the GUI Services
interface.


Debugging
---------

Debugging a Windows service can be difficult, because the service runs
in a very different context than a user who is logged in. By default,
services run in a non-desktop environment. They cannot interact
with the user (desktop) in any way, and vice versa.

Also, by default, services run as a special user, called LocalSystem.
LocalSystem is not a "user" in the normal sense; it is an NT security
ID (SID) that is sort of like root, but different. LocalSystem
typically does NOT have access to any network shares, even if you use
"net use" to connect to a remote file server. Again, this is because
services run in a different login session.

Depending on which OS you are running, you may have difficulty
attaching a debugger to a running service process. Also, if you are
having trouble *starting* a service, then you can't attach to the
process early enough to debug it.

So what's a developer to do? Well, there are several ways you can
debug services. First, you'll want to enable "interactive" access for
the service. This allows the service to interact with the local
desktop -- you'll be able to see the command shell that the service
runs in, see console output, etc. To do this, you can either use the
standard Windows Services tool (services.msc), or you can do it using
sc.exe.

* With the GUI tool, open the properties page for a service, and go
to the "Log On" page. Select "Local System account", and make
sure the "Allow service to interact with desktop" box is checked.

* With SC.EXE, configure the service using the command:

sc config type= own type= interact

Yes, you must specify type= twice, and with exactly the spacing
shown.

In both cases, you'll need to restart the service. When you do, if
the service started successfully, you'll see the console window of the
service. By default, it doesn't print anything out.

Next, you'll want to attach a debugger, or configure the service to
start under a debugger. Attaching a debugger should be
straightforward -- just find the process ID. But if you need to debug
something in the service startup path, you'll need to have a debugger
attached from the very beginning. There are two ways to do this.

In the first method, you alter the command-line of the service (called
the "binary path"). To do this, use SC.EXE to set the binary path to
whatever debugger you are going to use. I use the most recent version
of WinDbg, which is excellent, and is available at:

http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx

For example, this command would configure the service to start under a
debugger:

sc config binpath= "d:\dbg\windbg.exe -g -G d:\bin\myservice.exe
--root d:\path\root --listen-port 9000"
depend= Tcpip

The entire command must be on a single line, of course, and the binary
path must be in double-quotes. Also, the spacing MUST be: binpath= "..."

Substitute whatever debugger you want, with whatever command-line you
want, in place of windbg.exe. Then start the service (sc start
), and the Service Control Manager should execute the
command-line you provided as the binary path. Then your debugger
should start, and should launch the "myservice" process.

User objects and resources

In the following you will get an overview, how you can get information about your Oracle user environment. For the samples it is assumed, that you are already connected to the database via SQL*Plus.

Which tables do you have?

SQL> select table_name from user_tables;

How is my table defined, which columns has it?

SQL> describe table_name

Which database objects do I have currently?

SQL> select object_name, object_type from user_objects;

Which privileges has your Oracle account?

SQL> select * from session_privs;

Which quota do you actually have on which tablespace?

SQL> select tablespace_name, bytes, max_bytes from user_ts_quotas;

How big are your tables/objects for themselves?

SQL> select segment_name, tablespace_name, bytes from user_segments;

ref:

http://www-it.desy.de/systems/services/databases/oracle/at_desy/user_objects.html.en

Basic Introduction to SQLPLUS

The SQL*PLUS (pronounced "sequel plus") program allows you to store and retrieve data in the relational database management system ORACLE. Databases consists of tables which can be manipulated by structured query language (SQL) commands.
A table is made up of columns (vertical) and rows (horizontal).
A row is made up of fields which contain a data value at the intersection of a row and a column.
Be aware that SQL*PLUS is a program and not a standard query language.

Getting Started

It is a prerequisite that users are registered for ORACLE, an ORACLE account is needed.
On Unix platforms you must start the script oraenv to set the ORACLE environment. Enter the command . oraenv and press <Return;>.
Don't forget to type a blanc between the dot and oraenv.
If you are working with a PC using MS Windows, simply use Netinstall to install the product. You can find the software in the database folder.
Enter sqlplus on unix systems or run it on Windows from the start menue. Answer the displayed prompts by entering your ORACLE user-name and password.
The SQL*PLUS command prompt SQL > indicates that you are ready to work.

Some elementary Commands

alter user user identified by newpassword	enables user to change the password
help	accesses the SQL*PLUS help system
exit, quit	terminates SQL*PLUS
ho[st]	leads to the operating system without leaving SQL*PLUS
ho[st] command	executes a host operating system command
ho[st] oerr	accesses the ORACLE error help for unix

Editing and Executing

All entered input is stored as a single SQL*PLUS statement in the command buffer. Pressing the <Return> key while editing will either open a new numbered line or, if the previous line ends with a semicolon or consists of a single slash, will execute the SQL*PLUS command. Opening new numbered lines allows you to structure statements and enables you to refer to particular lines by later using edit functions.

l[ist]	lists command buffer (the current line is marked with a star)
ln or n	makes line n the current line and lists it
ln m	lists lines n through m
a text	appends text to current line
c/oldstring/newstring	changes oldstring to newstring in current line
i	inserts a line after current line
del	deletes the current line
r[un]	runs and lists command buffer
/	runs command buffer
;	lists command buffer

If you use substitution variables, like &variable, instead of values or names in your SQL statement, SQL*PLUS will prompt you and substitute the entered value. A substitution variable is a user variable name preceded by an ampersand.

Working with Command Files

You can use command files to save complex commands. After creating a command file you can retrieve, edit, and run it. The default file extension is .sql . If you use other file extensions you must write the full file name like name.extension.

ed[it]	overwrites a scratch file with the contents of the command buffer
	edit enables you to edit this file with the defined host operating system
	editor. The name of the scratch file is afiedt.buf .
	After leaving the editor the buffer is listed and you can execute it.
ed[it] filename	enables you to edit an existing or new file filename.sql
sav[e] filename	creates file filename and stores the command buffer into it
sav[e] filename [option]	stores command buffer into file filename
	Possible options are cre[ate], app[end], rep[lace].
get filename	loads the host operating system file filename into the command buffer
sta[rt] filename [arg1 arg2 ..]	executes file filename
	arg1 arg2 .. are arguments you wish to pass to the command file

If you run a command file in which a substitution variable like &1 is used, you will be prompted for that value. You can avoid being prompted by passing an argument to the command file.

Queries and Subqueries

Retrieving data from the database is the most common SQL operation. A query is an SQL command (specifically a select) that retrieves information from one or more tables. A subquery is a select which is nested in another SQL command.

The Describe Command

desc[ribe] name

lists the column definition for table or view name

Basic Select Commands

The basic select command consists of two parts, called clauses: select some data from table.
Examples

select * from tabname	selects all columns and rows from table tabname
select distinct col from tabname	selects column col from table tabname
	and returns only one copy of duplicate rows
select col1, col2 ... from tabname	selects specified columns from table tabname
select col1, col2*3 from tabname	selects col1,col2 from table tabname
	and lists col1, col2 multiplied by 3
select 2*3 from dual	calculates 2*3 and will display the result

Selecting Rows and Ordering

To retrieve specific rows from a table you need to add a where clause. A where clause consists of one or more search conditions which can be connected by logical operators. To display the retrieved data in a specific order you need to add an order by clause.
Examples
select col1,col2 from tabname
where col1 < col2 and col2 !=0
order by col2
Columns col1, col2 are selected from table tabname and all rows where col2 is not equal to zero and col1 is less than col2 are displayed in an ascending order (ordered by col2).
select col1,col2 from tabname
where col1 like '_A%' or col1 like '+++'
order by col2 desc
Columns col1,col2 are selected from table tabname and all rows where col1 is equal to '+++' or where the second letter in col1 is an 'A' are displayed in a descending order.
In this example two different escape characters are used. The underscore matches exactly one character whereas the percent sign can match zero or more characters.
select col1,col2 from tabname
where col1 in ( value1,value2 )
Columns col1,col2 are selected from table tabname and all rows where col1 is equal to value1 or to value2 are displayed.
select col1,col2 from tabname
where col1 not between value1 and value2
Columns col1,col2 are selected from table tabname and all rows where col1 is not in the range between value1 and value2 are displayed.

Using Set Operator

Set operators combine the results of two queries into a single result. If a statement contains multiple set operators, they will be evaluated from left to right.

set operator
union	returns all distinct rows selected by either query
union all	returns all rows selected by either query, including all duplicates
intersect	returns all distinct rows selected by both queries
minus	returns all distinct rows selected by the first query but not the second

Example
select * from table1
union all
select * from table2
This will combine all rows, columns of table1 and table2.

Querying Multiple Tables

If you want to retrieve information from different tables, you can do this by issuing different queries or a single JOIN query. In a JOIN query, you list the names of the tables you are querying in the from clause and the names of the linking columns in the where clause. The omission of the linking where clause causes a cartesian product of both tables. A JOIN combines rows from two or more tables where columns which the tables have in common match. If a column name is not unique, you must use a prefix to make clear which column from which table you want to select (e.g. tablename.columnname).

Simple Join

select col1,tab1.col2,col3 from tab1,tab2
where tab1.col2=tab2.col2
This is the most common type of join. It returns rows from two tables based on an equality condition, therefore it is also called an equi-join.

Non-Equi Join

select tab1.col1,tab2.col2 from tab1,tab2
where tab1.col1 between lowval and highval
Since this join doesn't return rows based on a equality condition, it is called a non-equi join.

Self Join

select alias1.col1,alias2.col1 "Header 2" from tabname alias1,tabname alias2
where alias1.col2=alias2.col3
In this example the table tabname is joined with itself. Using of two different alias names for the same table allows you to refer to it twice. Since the names of the resulting columns in this example are the same, the second column gets a new header.

Outer Join

select col1,col2 from tab1,tab2
where tab1.col1=tab2.col2(+)
Suppose you want to retrieve information from two tables where not all rows match but the result should contain all values from one or more columns. A simple join will select only matching rows whereas the outer join extends the result. All matching rows will be selected and when you append the outer join operator (+) to a column name, those rows which do not match will also be selected. In the example the number of rows which are selected is the number of rows in table tab2. If rows match, the outer join works as a simple join, if not, the values from tab2.col2 and a NULL value for the non existing value of tab1.col1 will be selected.

Data Definition Language DDL

DDL commands allow you to create, alter and delete objects (e.g tables, views) and also to grant and revoke privileges.
create table tabname (col1 type1,col2 type2,...) creates table tabname
col1 ... coln are the column names, type1,type2.. specifies the datatype of a column which can be number, date, char, varchar.
number(p,s) specifies a fixed point number having precision p (total number of digits) and scale s (number of digits to the right of the decimal point).
number(p) specifies a fixed point number.
number specifies a floating point number.
char(size) specifies fixed length (max 255) character data of length size.
varchar2(size) specifies variable length (max 2000) character string having a maximum length of size bytes.

create table tabname as subquery creates table tabname
subquery inserts rows into the table upon its creation. A subquery is a form of the select command which enables you to select columns from an existing table.

create view viewname as subquery creates view viewname
A view is a logical table based on one or more tables.

drop table tabname	removes table tabname from the database
alter table tabname add (col1 type1,col2 type2,...)	adds columns to table tabname
alter table tabname modify (col1 type1,col2 type2,...)	modifies column definitions
rename oldname to newname	renames table oldname
alter user user identified by newpassword;	enables user to change the password
	to newpassword
grant privilege on object to user	grants a privilege to user
revoke privilege on object from user	revokes a privilege from user

Data Manipulation Language DML

DML commands manipulate and query data in existing tables. These commands do not commit current actions.

insert into tabname (col1,col2...) values (val1,val 2...)	inserts rows into table tabname
insert into tabname subquery	inserts rows(selected by a subquery) into
&table tabname
update tabname set col1=expr1,col2=expr2... where cond	updates rows in table tabname
	columns are set to values of expressions if
	condition cond is true
update tabname set (col1,col2...)=(subquery) where cond	updates rows in table tabname
	columns are set to selected values if
	condition cond is true
delete from tabname [where cond]	either deletes all rows from table tabname
	or rows where cond is true

Schema

When you select data from a table or you insert data into a table then this object has to be in your own schema. In other words, you must be the owner.
If you are not the owner of the object, but the owner granted some privileges to you, you have to specify schema.tabname.
Example
select * from scott.emp

Transaction Control Commands

Transaction Control Commands manage changes made by Data Manipulation Language commands. A transaction (or logical unit of work) is a sequence of SQL statements that ORACLE treats as a single unit. A transaction ends with a commit, rollback , exit, or any DDL statement which issues an implicit commit. In most cases transactions are implicitly controlled.

commit	makes all changes since the beginning of a transaction permanent
rollback	rolls back (undoes) all changes since the beginning of a transaction
rollback to savepoint savep	rolls back to savepoint savep
savepoint savep	defines savepoint savep

user objects, user resources