批处理之家 - Powered by Discuz! Board

标题: [网络工具] 分享一个数据库连接工具[SQLCMD] [打印本页]

作者: caruko 时间: 2013-2-1 09:38 标题: 分享一个数据库连接工具[SQLCMD]

THE HANDY DANDY ANALOGX SQLCMD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

AnalogX SQLCMD allows you to quickly and easily run SQL queries against ODBC
data sources.  Basically, it's a simple portable way to execute SQL commands
from batch files, etc.

SQLCMD is like most commandline utilities out there; from the command prompt
you can type the name by itself to get a list of options:

F:\tools\sqlcmd> sqlcmd

AnalogX SQLCMD version 1.00 (Release)
The latest version can always be found at http://www.analogx.com/

Usage: SQLCMD [options]
Options: /USER [name]       Username to login as
      /PASS [password]    Password for username
      /DB [database]       Name of ODBC connection to use
      /LOG [filename]    Log results to file
      /APPEND             Append results to file
      /SILENT             Do not decode results
      /COMMAND [cmd]       SQL command to execute

Here's a brief description of each option:

USER       This is the username to login to the database with.  This field
            is optional and will try to log in using a NULL account when it
            is not supplied.
PASS       Password for username specified above.
DB       ODBC Database to connect to.
LOG       Specify the filename to log to.  Useful for debugging or keeping
            track of operations performed.  While not a log in the normal
            sense, it outputs all information pertaining to commands run.
APPEND    Instead of overwriting the existing log file, it will append
            onto it.
SILENT    Suppresses all text output.
COMMAND    SQL query to perform

It's all pretty straight-forward (to my thinking, at least).  Here's a couple
of examples:

sqlcmd /log "C:\Logs\sqlcmd.log" /append /db "Website" /command "select * from [users]"

or:

sqlcmd /user Jimmy /pass Password /db "Website" /command "update [users] set Downloads=1"

also, if you execute it like so:

sqlcmd /user Han /db "Website"

without the /COMMAND then it will go into it's "interactive" mode, where you
can just enter in SQL queries.  Once in this mode, simply type 'QUIT' and it
will exit.

If the program thinks it has successfully executed the command, it will return
0, otherwise it will return 1 (these are normally the defaults for success
and failure for commandline programs), so it should be easy to include SQL
commands into batch files.

For more info, and some MP3 music, make sure to check out our website at:

http://www.analogx.com/

使用方法：
第一步，在“控制面板--ODBC数据源”中添加需要连接的数据库，并设置一个"名称"。
第二步，直接执行命令：sqlcmd /user username /pass Password /db "ODBC_Name" /command "SQL_cmdline" 即可。

注意：  /SILENT  只会输出表的字段结构，不输出数据。

http://bcn.bathome.net/s/tool/index.html?key=sqlcmd

作者: caruko 时间: 2013-2-1 10:08

分享一个自用的SQLCMD使用范例；获取数据使用了论坛第三方工具 conset ，不用它用for 读取也可以。
代码具体用来干什么，还是不明说啦

@echo off
setlocal ENABLEDELAYEDEXPANSION
::设置连接数据库的字符串sqlc
set sqlc=sqlcmd /log dbs.log /APPEND /user sa /db "BGS-YCM" /command
::获取数据库查询结果 records
%sqlc% "select * from TimeRecords where card_id='0016459843' and sign_time > '2013.1.1'" |findstr /v "[a-zA-Z]" |conset records=

::整理数据
for /f "tokens=4,5,6,7,8,9,10 delims=:'/ " %%a in ('conset /v records') do (
	set dt=1%%d%%e%%f
	if %%g equ 1 (
		if !dt! gtr 1060000 if !dt! lss 1083100 set "#%%c.%%a.%%b#A=%%g$%%d:%%e:%%f"
		if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
		if !dt! gtr 1173000 set "#%%c.%%a.%%b#D=%%g$%%d:%%e:%%f"
	)
	if %%g geq 2 (
		if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
		if !dt! gtr 1173000 set "#%%c.%%a.%%b#C=%%g$%%d:%%e:%%f"
	)
	set /a ￥%%c￥%%a￥%%b￥+=1
)

::分析缺少打卡的日期
for /f "tokens=1-4 delims=￥=" %%a in ('set ￥') do (
echo,
	IF NOT DEFINED #%%a.%%b.%%c#A call :补上班 %%a %%b %%c
	IF NOT DEFINED #%%a.%%b.%%c#C call :补下班 %%a %%b %%c
)
pause
goto :eof


:补上班
echo,%1.%2.%3日，补上班...
IF DEFINED #%1.%2.%3#B for /f "tokens=1-2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
	if %%a equ 1 (
		call :改时间 %1 %2 %3 1
	)
) else (
	call :补时间 %1 %2 %3 1
)
goto :eof

:补下班
echo,%1.%2.%3日，补下班...
IF DEFINED #%1.%2.%3#D (
	call :改次 %1 %2 %3
) else (
	set /a flag=!￥%1￥%2￥%3￥!+1
	call :补时间 %1 %2 %3 !flag!
)
goto :eof
	
:改次
echo,%1.%2.%3 改次!#%1.%2.%3#D!
for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#D!") do (
	echo,%sqlc% "update TimeRecords set mark=2 where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
)	
goto :eof

:补时间
echo,%1.%2.%3:%4补时间...
if %4 gtr 1 (
	set /a hh=17+!RANDOM!%%2,ss=!RANDOM!%%60
	if !hh! equ 17 (set /a mm=31+!RANDOM!%%29) else set /a mm=!RANDOM!%%60
	set "newtime=%1.%2.%3 !hh!:!mm!:!ss!"
) else (
	set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
	set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
)
echo,%sqlc% "insert into TimeRecords values (1,'0016459843','160921','!newtime!',%4,0)"
goto :eof

:改时间
echo,%1.%2.%3迟到改时间，原!#%1.%2.%3#B!
set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
	echo,%sqlc% "update TimeRecords set sign_time='!newtime!' where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
)	
goto :eof
复制代码

作者: Foxie 时间: 2013-2-1 17:12

这个在SQLCMD中还有些用处，在日常中用处不大。不过，嘿嘿，第三方先多再精嘛！

作者: lateol 时间: 2013-2-27 03:12

神马东西来着一时没看懂……

欢迎光临批处理之家 (http://bbs.bathome.net/)

Powered by Discuz! 7.2