This command is used to execute directly requests to specified in parameters database table. Command allows executing insert, update, any stored procedures, any SQL statement.
Here is general command statement syntax:
cmdmgr.exe dbrequest [--section=section_name] --sqlstatement=<"SQL text"> | statement<select|insert|update|sp> [additional paramters]
There two general types of statements:
- any SQL statement. This statement required «sqlstatement» parameter that must contain SQL query;
- standard statements. This is select\insert\update or stored procedure statements. «statement» parameter must contain statement type. Command manager support such statements: select, update, insert, sp.
Here are syntaxes for every statement type:
1. «select» statement:
cmdmgr.exe dbrequest [--section=section_name] --statement=select --table=PREFIX.TABLE_NAME --columns="Col1,Col2,ColN" --conditions="Cond1>1,Cond2!=2,Cond3=3" [--df | disableformatting] [--maxrows=25] [--asis] [--xml]
After execution result will be in output stream. Here are parameters that use this statement:
- table – this is database table for which statement is executing. This must be table name with prefix;
- columns – columns of specified table that will be selected. To select all columns, pass «*» as value;
- conditions – conditions for statement execution. Can be empty.
Example:
cmdmgr.exe dbrequest statement=select --table=eaglemgr.dbo.cmdmgr_test_table --columns="*" --conditions="ID<3" --df -asis
Figure 35. Select example.
2. «update» statement:
cmdmgr.exe dbrequest [--section=section_name] --statement=update --table=PREFIX.TABLE_NAME --values="Col1=1,Col2=2" --conditions="Cond1>1,Cond2!=2,Cond3=3" [--df | disableformatting] [--xml]
Execution of this statement will only print error or success message. Here are statement parameters:
- table – as with «select» statement this is database table for which statement is executing. This must be table name with prefix;
- values – this is «column=value» list of columns that statement will update;
- conditions – conditions for statement execution. This parameter cannot be empty.
Example:
cmdmgr.exe dbrequest statement=update --table=eaglemgr.dbo.cmdmgr_test_table --values="Name=1,Value =2" conditions="ID=2"
Figure 36. Update example.
3. «insert» statement:
cmdmgr.exe dbrequest [--section=section_name] --statement=insert --table=PREFIX.TABLE_NAME --values="Col1=1,Col2=2" [--xml]
Execution of this statement will print only success or error message and rows count affected. Here are statement parameters:
- table – this is database table for which statement is executing. This must be table name with prefix;
- values – list of «column=value» parameters that statement will insert to table.
Example:
cmdmgr.exe dbrequest statement=insert --table=eaglemgr.dbo.cmdmgr_test_table --values="ID=4,Name=filo,Value=test"
Figure 37. Update example.
4. stored procedure («sp») statement:
cmdmgr.exe dbrequest [--section=section_name] --statement=sp --sp=Schema.SpName --arguments="Arg1=1,Arg2=2" [--df | disableformatting] [--maxrows=25] [--asis] [--xml]
Based on stored procedure type, this statement execution can print data as in «select» statement to output stream. Here are statement parameters:
- sp – name of stored procedure in database. Format is Schema.SpName;
- arguments – this is «arg_name=value» list of stored procedure arguments.
Example:
cmdmgr.exe dbrequest --statement=sp --sp="EAGLEMGR.DBO.TEST_CALC" --arguments="in_value_1=1,in_value_2=1,in_action=+" -asis
Figure 38. Stored procedure example.
5. any SQL statement:
cmdmgr.exe dbrequest [--section=section_name] --sqlstatement="SQL text" [--df | disableformatting] [--maxrows=25] [--asis] [--xml]
This statement allows to execute any user defined SQL query. Statement has only one parameter that must be specified:
- sqlstatement – this is user defined SQL query that will be executed. As in «select» statement, if user defined SQL query, returns any result data, it will be printed to output stream;
Example:
cmdmgr.exe dbrequest --sqlstatement="select * from eaglemgr.dbo.cmdmgr_test_table " -df --maxrows=25 –asis
Figure 39. SQL statement example.
Note: Most statements have optional parameters that are the same in any context. Here are these parameters and their descriptions:
- section – this is section from db_connection.ini. By default, primary section used;
- df(disableformatting) – switches off advanced output formatting;
- maxrows – maximum rows count to output;
- asis – print output data, without replacing data to columns type (<BLOB>, <CLOB>, <XML> .etc);
- xml – output will be formatted in XML. If this parameter is specified, --asis and --disableformatting will be ignored.
0 Comments