2009 10월 « 어느용

오라클에서 컬럼 명들을 가지고 오는 쿼리

select *
from dba_col_comments
where 1=1
and owner='anyxxxxxx'
and comments is NULL
and able_name not like 'BIN$%'
order by table_name

select *

from dba_col_comments

where 1=1

and owner='anyxxxxxx'

and comments is NULL

and able_name not like 'BIN$%'

order by table_name

오라클에서 테이블 명들을 가지고 오는 쿼리

select *
from dba_tab_comments
where 1=1
and owner='anyxxxxxx'
and comments is NULL
and table_name not like 'BIN$%'
order by table_name

select *

from dba_tab_comments

where 1=1

and owner='anyxxxxxx'

and comments is NULL

and table_name not like 'BIN$%'

order by table_name

sql server 백업을 하게 하기 위한 스크립트

"C:\Program Files\Microsoft SQL Server90\Tools\Binn\sqlcmd" -S localhost -i e:\Backupbackup.sql

1	"C:\Program Files\Microsoft SQL Server90\Tools\Binn\sqlcmd" -S localhost -i e:\Backupbackup.sql

풀백업

declare @runtime nvarchar(10)
declare @backupdevice nvarchar(100)
declare @backupname nvarchar(100)

set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2)
set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak'
set @backupname = N'backup_' + @runtime

BACKUP DATABASE [TU] TO DISK = @backupdevice WITH NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10

declare @runtime nvarchar(10)

declare @backupdevice nvarchar(100)

declare @backupname nvarchar(100)

set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2)

set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak'

set @backupname = N'backup_' + @runtime

BACKUP DATABASE [TU] TO DISK = @backupdevice WITH NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10

증분 백업

declare @runtime nvarchar(10)
declare @backupdevice nvarchar(100)
declare @backupname nvarchar(100)

set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2)
set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak'
set @backupname = N'backup_' + @runtime

BACKUP DATABASE [TU] TO DISK = @backupdevice WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10

declare @runtime nvarchar(10)

declare @backupdevice nvarchar(100)

declare @backupname nvarchar(100)

set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2)

set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak'

set @backupname = N'backup_' + @runtime

BACKUP DATABASE [TU] TO DISK = @backupdevice WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10

.bat 날자로 파일명 생성

set yyyy=%date:~0,4%
set mm=%date:~5,2%
set dd=%date:~8,2%
echo group%yyyy%%mm%%dd%.log >> file.txt

set yyyy=%date:~0,4%

set mm=%date:~5,2%

set dd=%date:~8,2%

echo group%yyyy%%mm%%dd%.log >> file.txt

OLE DB로 oracle이건 MSSQL이건 데이터를 가져오기 위한 기초 코드

private OleDbDataReader rd;
private OleDbConnection Conn;
private string strRet;
private OleDbCommand cmd;
private StringBuilder sbSQL;
private OleDbParameter paramnSeq;

try
{
	// SQLOLEDB MSDAORA
	Conn = new OleDbConnection("Provider=MSDAORA;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xxx )(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = GROUP))); User Id =; Password =;");
	Conn.Open();

	try
	{
		sbSQL = new StringBuilder();

		sbSQL.Append(" SELECT * ");
		sbSQL.Append(" FROM HEIS_CUSTOMER ");
		sbSQL.Append(" WHERE 1=1 ");
		sbSQL.Append(" AND CUSTOMER_CODE = ? ");

		paramnSeq = new OleDbParameter("CUSTOMER_CODE", OleDbType.Char, 4);
		paramnSeq.Value = "0003";

		cmd = new OleDbCommand(sbSQL.ToString(), Conn);
		cmd.Parameters.Add(paramnSeq);

		rd = cmd.ExecuteReader();

		if (rd.Read())
		{
			strRet = rd["CUSTOMER_CODE"].ToString();
		}
		else
		{
			strRet = "";
		}
	}
	catch (System.Exception e1)
	{
		throw e1;
	}
	finally
	{
		rd.Close();
	}
}
catch (System.Exception e2)
{
	throw e2;
}
finally
{
	Conn.Close();
}

private OleDbDataReader rd;

private OleDbConnection Conn;

private string strRet;

private OleDbCommand cmd;

private StringBuilder sbSQL;

private OleDbParameter paramnSeq;

try

{

// SQLOLEDB MSDAORA

Conn = new OleDbConnection("Provider=MSDAORA;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xxx )(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = GROUP))); User Id =; Password =;");

Conn.Open();

try

{

sbSQL = new StringBuilder();

sbSQL.Append(" SELECT * ");

sbSQL.Append(" FROM HEIS_CUSTOMER ");

sbSQL.Append(" WHERE 1=1 ");

sbSQL.Append(" AND CUSTOMER_CODE = ? ");

paramnSeq = new OleDbParameter("CUSTOMER_CODE", OleDbType.Char, 4);

paramnSeq.Value = "0003";

cmd = new OleDbCommand(sbSQL.ToString(), Conn);

cmd.Parameters.Add(paramnSeq);

rd = cmd.ExecuteReader();

if (rd.Read())

{

strRet = rd["CUSTOMER_CODE"].ToString();

}

else

{

strRet = "";

}

catch (System.Exception e1)

{

throw e1;

}

finally

{

rd.Close();

}

catch (System.Exception e2)

{

throw e2;

}

finally

{

Conn.Close();

}

오유처럼 더블 클릭시에 맨 위로 가고 맨 아래로 가고 싶으면

<script language="javascript">
	function totopbottom() {
		//	if (document.body.scrollTop == 0) { 오유에는 이렇게 되어 있지만...
		if (document.documentElement.scrollTop == 0) {
			window.scrollTo(0,document.body.scrollHeight);
		} else {
			window.scrollTo(0,0);
		}
	}
	function topbottom() {
		document.body.ondblclick = totopbottom;
	}
</script>

function totopbottom() {

// if (document.body.scrollTop == 0) { 오유에는 이렇게 되어 있지만...

if (document.documentElement.scrollTop == 0) {

window.scrollTo(0,document.body.scrollHeight);

} else {

window.scrollTo(0,0);

}

function topbottom() {

document.body.ondblclick = totopbottom;

}

</script>

ORACLE Connect Sample using DataAdapter without TNS

#region Using directives

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;

#endregion

namespace UsingADOManagedProvider
{
	partial class ADONetForm1 : Form
	{
		public ADONetForm1( )
		{
			InitializeComponent( );
			// connect to Northwind Access database
			string connectionString = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.XXX )(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = GROUP))); User Id =; Password =";

			// get records from the customers table
			string commandString = "Select CompanyName, ContactName from Customers";

			// create the data set command object
			// and the DataSet

			OleDbDataAdapter DataAdapter = new OleDbDataAdapter( commandString, connectionString );

			DataSet DataSet = new DataSet( );

			// fill the data set object
			DataAdapter.Fill( DataSet, "Customers" );

			// Get the one table from the DataSet
			DataTable dataTable = DataSet.Tables[0];

			// for each row in the table, display the info
			foreach ( DataRow dataRow in dataTable.Rows )
			{
				lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow["ContactName"] + ")" );
			}
		}
	}
}

#region Using directives

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.OleDb;

using System.Drawing;

using System.Windows.Forms;

#endregion

namespace UsingADOManagedProvider

{

partial class ADONetForm1 : Form

{

public ADONetForm1( )

{

InitializeComponent( );

// connect to Northwind Access database

string connectionString = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.XXX )(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = GROUP))); User Id =; Password =";

// get records from the customers table

string commandString = "Select CompanyName, ContactName from Customers";

// create the data set command object

// and the DataSet

OleDbDataAdapter DataAdapter = new OleDbDataAdapter( commandString, connectionString );

DataSet DataSet = new DataSet( );

// fill the data set object

DataAdapter.Fill( DataSet, "Customers" );

// Get the one table from the DataSet

DataTable dataTable = DataSet.Tables[0];

// for each row in the table, display the info

foreach ( DataRow dataRow in dataTable.Rows )

{

lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow["ContactName"] + ")" );

}

JET OLEDB Connect Sample using DataAdapter

#region Using directives

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;

#endregion

namespace UsingADOManagedProvider
{
	partial class ADONetForm1 : Form
	{
		public ADONetForm1( )
		{
			InitializeComponent( );

			// connect to Northwind Access database
			string connectionString = "provider=Microsoft.JET.OLEDB.4.0; " + "data source = c:\nwind.mdb";

			// get records from the customers table
			string commandString = "Select CompanyName, ContactName from Customers";

			// create the data set command object
			// and the DataSet

			OleDbDataAdapter DataAdapter = new OleDbDataAdapter( commandString, connectionString );

			DataSet DataSet = new DataSet( );

			// fill the data set object
			DataAdapter.Fill( DataSet, "Customers" );

			// Get the one table from the DataSet
			DataTable dataTable = DataSet.Tables[0];

			// for each row in the table, display the info
			foreach ( DataRow dataRow in dataTable.Rows )
			{
				lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow ["ContactName"] + ")" );
			}
		}
	}
}