untitled
viviti

DBTools  

What is DBTools?

DBTools is a command line interface for DataBases access on MS platform with ODBC drivers.


How to use DBTools?

1:Interactive mode:
Call out the program without any parameters, the program will pop-out a windows as follow:

DBTools

1:Create DataBase

Click Edit Data Source button in field 1 on the windows. Will call out the SYSTEM program ODBC Data Source Manager. Use this program can define all ODBC data source in the system.
You may use Initialize Sample Data button in field 1 on the windows to initial test data for the selected data source also.

2:Open DataBase

Click Open DB button in field 2 on the windows. You can select a ODBC Data Source file and open the Data Source. Once the Data Source was opened, you may view or change the contents of the Data Source.

3:Create Table

You may use Input Table name and add all  Items name field(in field 3) to define new table structure. Every time you add a new item, a sample SQL command will build on SQL Commnad field(in field 7). This is useful for the person who does not know how to contructure a SQL command.
After Table name was define and all Items was added, You may change the SQL Commnad field(in field 7) directly to add or modify the SQL command as you wish. Then click Create Table button in field 4 to create a new Table.

4:Select Functions and Table

You may use Select Function and Table field(in field 5) to define SQL command structure. Every time you select a function or Table, a sample SQL command will build on SQL Commnad field(in field 7). This is useful for the person who does not know how to contructure a SQL command.
After all selection was done, You may change the SQL Commnad field(in field 7) directly to add or modify the SQL command as you wish. Then click SQL Exec button in field 6 to execute the SQL command.
In case of select command was execute, A Data Base content window was pop-up. This window enable you to review the contents in the table. You may page forward/backward/first/last to view the contents as well as save the information. After review was completed. Click OK button to close the review window.

5:SQL Execute

You may edit the SQL Commnad field(in field 7) code the SQL command as you wish. Then click SQL Exec button in field 6 to execute the SQL command.
In case of select command was execute, A Data Base content window was pop-up. This window enable you to review the contents in the table. You may page forward/backward/first/last to view the contents as well as save the information. After review was completed. Click OK button to close the review window.

6:Save DataBase Informations

After all test was completed, means connection is O.K. All SQL command can be executed correctly. Now you will like to save the D.B. dfine informaions, so we can run the program under background mode.
Click Save D.B. Info button in field 8. A file call DBInfo.ini was be saved. The first line of the file is The Open Data Base ODBC definition. Second line will copy the content of SQL Commnad field (filed 7). This is useful for the person who does not know how to contructure a ODBC definitions.
Edit the DBInfo.ini file as you wish DBTools program will do for you in background mode. then you can run DBTools with the ini file as input(ini_file) to access Data Base as you wish.

7:Close DataBase

After everything was done. Click Close DB button in field 9 and exit the program.

8:About

About button show you the simple readme informaions.
2:Batch Mode:
Call out the program with parameters as follows.

Use:DBTools [-n|N] [-D|d] [-i|I] ini_file [output_file] [log_file]

  • ini_file: Data base script file name.
  • output_file: Data output file name.
  • log_file: Execution log will save on log file. Default log file name is DBToolLog.txt.
    • *** The default output file format is CSV. Base on the sub-name of output_file, The output format may be:
    Sub-Name Format
    CSV CSV
    HTM HTML
    HTML HTML
    SQL SQL commands

    * Default for SQL format, Data informations will dump by update commands. In case of  -D|d option was provided, A drop, create command was insert before insert commands for all data duplicate. and In case of -I|i option was provided, Data will dump as insert commands.

  • -N|n  sql_dump_flag means dump D.B. structure only.
  • -D|d   Drop and create the table before D.B. data copy.
  • -I|i  Use Insert SQL command for D.B. data copy.
    default will use update command.
    • *** The first command line in ini_file must be the discription of source Data  Base informations. It means the source must be a Data Base. And the destination may be a file in CSV,HTML,SQL format or another DataBase. Since ODBC driver was used, the Data Base can be a commercial/open source Data Base or a Excel/CSV/Txt file.

    All the SQL command will access with source DataBase. In case of Select SQL command, the data will copy to the output file or destination DataBase.

    ini_file directive:
    1:First character less than 'A' will treat as a comment line.
    2:The first command line must discribe the Data Base source.
  • DataBase Infomations Format:
    ODBC;Key-Word1=Value1;Key-Word2=Value2
    Following are Data Base Infomations Key-Words:
    Key-Word description
    SERVER=address Define Srrver location(IP address).
    DATABASE=D.B._name Define Data Base Name
    UID=User_ID/UserName Define User ID or Name.
    PWD=Pass_Word
    PASSWORD=Pass_Word    		 Define Pass Word.
    DBA=W Define D.B. Access in write mode
    DBQ=D.B.File_Name Define Data Base file name.
    DEFAULTDIR=Dir. Define default Dir.
    DRIVER=Driver Define ODBC Driver Name.
    Extensions=txt,csv,tab,asc Define file extension for text file type(FIL=text)
    FIL=text | excel 8.0 Define file type
    FILEDSN=DSN_Name
    DSN=DSN_Name    		 Define DSN file Name.

    * MySQL DataBase connection information shall delete 'ODBC;' from the D.B informations script. That means MySQL D.B. does not accept 'ODBC;' script.
    • 3:Next directive may use EXCEL DBOut HTML or standard SQL commands....
    4:HTML format-1 format-2 format-3 format-4 [Max. = 10]
    Define Select output will dump in HTML format.
    Ex. Three background colors example.
       HTML '<td align="center" bgcolor="#96cdcd"><font size="2">%s</font></td>' '<td align="center" bgcolor="#fff8dc"><font size="2">%s</font></td>' '<td align="center" bgcolor="#bfefff"><font size="2">%s</font></td>'
    5:EXCEL xls_file_Name Insert/Update/Drop Table(sheet)_name Key
    Define Select output will insert into EXECL file.
    6:DBOut;D.B.Informations;Insert/Update/Drop DataBase_name KeyName
    Copy Select output to This Data Base.
    D.B.Informations same as 2:
    Insert/Update/Drop:
    (* this parameter will override the command line -D|d and -I|i flag)
    Insert/Update:
    Use Insert or Update command for data copy. (For Excel only insert mode).
    Drop:
    Drop and create the Table before data copy.
    KeyName:
    Key column name for Update SQL command.

    *SQL Select command causes data dump, so HTML/EXCEL/DBOut commands must present previous and effect on next Select commands.

    ini_file examples:

    EX. for CSV/Text file
    ODBC;DBQ=E:\Delelop\SERVICE\DBTOOLS\WEATHER;DefaultDir=E:\Delelop\SERVICE\DBTOOLS\WEATHER;Driver={Driver da Microsoft para arquivos texto (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\Weather.dsn;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

    Select * from wout.csv
    EX. for Access file
    ODBC;DBQ=F:\TEMP\DBTools\Debug\Test.mdb;DefaultDir=F:\TEMP\DBTools\Debug;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\Access.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

    Select * from STUDENT
    EX. for Excel file
    ODBC;DBQ=F:\TEMP\DBTools\Debug\Test.xls;DefaultDir=F:\TEMP\DBTools\Debug;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=65535;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

    Select * from STUDENT
    EX. for MySQL
    DRIVER={MySQL ODBC 3.51 Driver};DESC=;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\MySQL.dsn;

    Select * from dynabind_section
    EX. for Oracle
    ODBC;DRIVER={Oracle ODBC Driver};FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\DSN-name.dsn;SERVER=server-name;UID=myUID;PWD=MyPassword;DBQ=DBQ-name;DBA=W;APA=T;PFC=1;TLO=O;

    Select * from STUDENT




    Web Hosting · Blog · Guestbooks · Message Forums · Mailing Lists
    Easiest Website Builder ever! · Build your own toolbar · Free Talking Character · Email Marketing
    powered by a free webtools company bravenet.com