[新手上路]批处理新手入门导读[视频教程]批处理基础视频教程[视频教程]VBS基础视频教程[批处理精品]批处理版照片整理器
[批处理精品]纯批处理备份&还原驱动[批处理精品]CMD命令50条不能说的秘密[在线下载]第三方命令行工具[在线帮助]VBScript / JScript 在线参考
返回列表 发帖

[网络工具] 分享一个数据库连接工具[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

分享一个自用的SQLCMD使用范例; 获取数据使用了论坛第三方工具 conset ,不用它用for 读取也可以。
代码具体用来干什么,还是不明说啦
  1. @echo off
  2. setlocal ENABLEDELAYEDEXPANSION
  3. ::设置连接数据库的字符串sqlc
  4. set sqlc=sqlcmd /log dbs.log /APPEND /user sa /db "BGS-YCM" /command
  5. ::获取数据库查询结果 records
  6. %sqlc% "select * from TimeRecords where card_id='0016459843' and sign_time > '2013.1.1'" |findstr /v "[a-zA-Z]" |conset records=
  7. ::整理数据
  8. for /f "tokens=4,5,6,7,8,9,10 delims=:'/ " %%a in ('conset /v records') do (
  9. set dt=1%%d%%e%%f
  10. if %%g equ 1 (
  11. if !dt! gtr 1060000 if !dt! lss 1083100 set "#%%c.%%a.%%b#A=%%g$%%d:%%e:%%f"
  12. if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
  13. if !dt! gtr 1173000 set "#%%c.%%a.%%b#D=%%g$%%d:%%e:%%f"
  14. )
  15. if %%g geq 2 (
  16. if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
  17. if !dt! gtr 1173000 set "#%%c.%%a.%%b#C=%%g$%%d:%%e:%%f"
  18. )
  19. set /a ¥%%c¥%%a¥%%b¥+=1
  20. )
  21. ::分析缺少打卡的日期
  22. for /f "tokens=1-4 delims=¥=" %%a in ('set ¥') do (
  23. echo,
  24. IF NOT DEFINED #%%a.%%b.%%c#A call :补上班 %%a %%b %%c
  25. IF NOT DEFINED #%%a.%%b.%%c#C call :补下班 %%a %%b %%c
  26. )
  27. pause
  28. goto :eof
  29. :补上班
  30. echo,%1.%2.%3日,补上班...
  31. IF DEFINED #%1.%2.%3#B for /f "tokens=1-2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
  32. if %%a equ 1 (
  33. call :改时间 %1 %2 %3 1
  34. )
  35. ) else (
  36. call :补时间 %1 %2 %3 1
  37. )
  38. goto :eof
  39. :补下班
  40. echo,%1.%2.%3日,补下班...
  41. IF DEFINED #%1.%2.%3#D (
  42. call :改次 %1 %2 %3
  43. ) else (
  44. set /a flag=!¥%1¥%2¥%3¥!+1
  45. call :补时间 %1 %2 %3 !flag!
  46. )
  47. goto :eof
  48. :改次
  49. echo,%1.%2.%3 改次!#%1.%2.%3#D!
  50. for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#D!") do (
  51. echo,%sqlc% "update TimeRecords set mark=2 where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
  52. )
  53. goto :eof
  54. :补时间
  55. echo,%1.%2.%3:%4补时间...
  56. if %4 gtr 1 (
  57. set /a hh=17+!RANDOM!%%2,ss=!RANDOM!%%60
  58. if !hh! equ 17 (set /a mm=31+!RANDOM!%%29) else set /a mm=!RANDOM!%%60
  59. set "newtime=%1.%2.%3 !hh!:!mm!:!ss!"
  60. ) else (
  61. set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
  62. set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
  63. )
  64. echo,%sqlc% "insert into TimeRecords values (1,'0016459843','160921','!newtime!',%4,0)"
  65. goto :eof
  66. :改时间
  67. echo,%1.%2.%3迟到改时间,原!#%1.%2.%3#B!
  68. set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
  69. set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
  70. for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
  71. echo,%sqlc% "update TimeRecords set sign_time='!newtime!' where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
  72. )
  73. goto :eof
复制代码

TOP

这个在SQLCMD中还有些用处,在日常中用处不大。不过,嘿嘿,第三方先多再精嘛!
Batch For Ever.

TOP

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

TOP

返回列表