1 / 46

SQLCMD A Better DB-Access?

Informix User Forum 2005 Moving Forward With Informix. SQLCMD A Better DB-Access?. Jonathan Leffler Sr. Technical Staff Member/IBM. Atlanta, Georgia December 8-9, 2005. SQLCMD A Better DB-Access. Jonathan Leffler

judson
Download Presentation

SQLCMD A Better DB-Access?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Informix User Forum 2005Moving Forward With Informix SQLCMDA Better DB-Access? Jonathan Leffler Sr. Technical Staff Member/IBM Atlanta, Georgia December 8-9, 2005

  2. SQLCMDA Better DB-Access Jonathan Leffler jleffler@us.ibm.com

  3. Agenda • Origins of SQLCMD • Basic Use • SQL • History • Controls • SQLUNLOAD • SQLRELOAD • Build and Install SQLCMD – A Better DB-Access?

  4. Origins of SQLCMD • Originally called RDSQL. • Created in 1987 (oldest surviving records). • Informix-SQL had an RDSQL option • Instead of Query-Language. • Informix-SQL was produced by RDS • Relational Database Systems Inc. • Before company went public • Informix Software Inc. • Renamed SQLCMD in 1992. SQLCMD – A Better DB-Access?

  5. Origins of SQLCMD • Intended as an alternative to ‘isql’. • Before DB-Access was created. • Designed for use in shell scripts. • Exits with non-zero status on error. • Careful use of standard input, output, error. • Output layout independent of selected data. • Designed for interactive use. • History allows you to list, edit, rerun SQL. SQLCMD – A Better DB-Access?

  6. Basic Use of SQLCMD • Command line: • sqlcmd –d stores –e ‘select * from customers’ • Interactive mode: • sqlcmd –d stores • SQL[1022]: select * from customers; • …data printed… • SQL[1023]: Prompt includes history command number SQLCMD – A Better DB-Access?

  7. SQL Command Interpreter • Executes preparable SQL commands: • DDL • CREATE TABLE, etc • DML • INSERT, DELETE, UPDATE, SELECT, … • Results of SELECT go to output. • Also EXECUTE PROCEDURE • Default format is variable-width fields with separators SQLCMD – A Better DB-Access?

  8. Connections • CONNECT statement: • CONNECT TO ‘dbase@server’ AS ‘conn1’ USER ‘me’ PASSWORD ‘mine’ WCT; • Accepts standard USING for PASSWORD. • And WITH CONCURRENT TRANSACTIONS for WCT. • Does not accept DORMANT. • SET CONNECTION statement. • DISCONNECT statement. SQLCMD – A Better DB-Access?

  9. Alternative Output Formats • SQLCMD formats data uniformly • Does not vary depending on width. • Key original design feature. • Selectable delimiters • Field delimiter – $DBDELIMITER or pipe ‘|’ • Record delimiter – Newline • Escape – Backslash • Quote – Double quote SQLCMD – A Better DB-Access?

  10. Alternative Output Formats • Select (default) • Variable-width fields with field separators. • Unload. • Variable-width fields with field terminators. • CSV • Comma-separated variable-width fields. • Non-numeric fields enclosed in quotes. • XML • Default tag around records is ‘<record>’. • XML is not accepted as an input format – by design. Quote format is the same as CSV except the delimiter is not set by SQLCMD SQLCMD – A Better DB-Access?

  11. Alternative Output Formats • Fixed • Fixed width fields. • No spaces between fields. • FixSep • Fixed width fields with separator • No separator after last field • FixDel • Fixed width fields with delimiter • Including after last field • Expect ‘format’ to change in future • format variable delimited; • format fixed separated; New! New! SQLCMD – A Better DB-Access?

  12. Alternative Output Formats • Command line: • -F xml -G customer –D @ -E = \-Q “’” –A ‘yyyy-mm-dd’ • Built-in commands: • format xml customer; • delim ‘@’; • escape ‘=‘; • quote ‘\’’; • date ‘yyyy-mm-dd’; • eor ‘\r\n’; No command line option (bug) SQLCMD – A Better DB-Access?

  13. UNLOAD • Syntax is superset of DB-Access or ISQL. • UNLOAD [CREATE|APPEND] TO [FILE] ‘somefile’ DELIMITER ‘@’ SELECT * FROM Table; • UNLOAD TO PIPE ‘pipecommand’ DELIMITER ‘@’ EXECUTE PROCEDURE mine(‘this’, ‘that’, ‘t’’other’); • This is subject to extension in the future. SQLCMD – A Better DB-Access?

  14. LOAD • Syntax is superset of DB-Access or ISQL • LOAD FROM [FILE|PIPE] ‘somefile’ DELIMITER ‘@’ INSERT INTO AnotherTable; • No transaction management. • Roll your own with • BEGIN WORK • COMMIT WORK • Or use RELOAD instead. SQLCMD – A Better DB-Access?

  15. RELOAD Statement • Syntactically similar to LOAD statement: • RELOAD FROM ‘file.unl’ INSERT INTO Table; • Automatically initiates transactions • Groups the inserts into smaller transactions. • Unless it is started within a transaction. • Transaction size is controllable: -N 1024 – command line transize 1024; – built-in command SQLCMD – A Better DB-Access?

  16. INFO Statement • Loosely similar to the INFO statement. • Supported by DB-Access and ISQL. • And not the database servers. • Interpreted by SQLCMD. • Interrogates system catalog. • Hairy code in places! • Output format is the same as a SELECT. • It is the output from a SELECT statement. SQLCMD – A Better DB-Access?

  17. INFO Statement • INFO HELP • Lists the various available options. • INFO TABLES • User tables. • INFO COLUMNS FOR sometable • INFO DATABASES • INFO CONNECTIONS • Not a database query SQLCMD – A Better DB-Access?

  18. History • SQLCMD records SQL commands • File specified by $SQLCMDLOG • Default name: ./.sqlcmdlog • Format is platform neutral • Portable – including 32-bit to 64-bit. • Can be shared by concurrent executions. • Size is configurable. • Default size is 50. • File size is limited. SQLCMD – A Better DB-Access?

  19. History • History can be turned on or off. • Defaults to on in interactive mode. • Off otherwise. • Some commands do not get recorded. • INFO commands generate two commands. • Use ‘list’ command to see previous commands • Synonym ‘l’. • Previous command is ‘0’. • Relative commands ‘l -10 0’. • Absolute ‘l 23 54’. • Output goes to same place as SELECT statements. SQLCMD – A Better DB-Access?

  20. History • ‘Rerun’ command runs commands again. • Synonym ‘r’. • Same semantics as ‘list’. • ‘Edit’ and ‘View’ commands allow you to edit commands. • Synonym ‘v’. • Beware: ‘e’ is short for ‘exit’, not ‘edit’. • DBEDIT, VISUAL, EDITOR, “vi”. • Commands are rerun when you exit editor. SQLCMD – A Better DB-Access?

  21. Exiting SQLCMD • Three commands to do it: • ‘exit’ (‘e’ and ‘x’) • ‘quit’ (‘q’) • ‘bye’ (‘b’) • Relic from earliest days and Informix 3.30 • The ‘informix’ program used ‘bye’ to terminate. • EOF on standard input. • Executed all command line options. • And one was ‘-e’ or ‘-f’ or equivalent. SQLCMD – A Better DB-Access?

  22. I/O Redirection • You can use Unix redirection, of course: • echo “select * from customer” |sqlcmd -d stores -F XML • Built-in commands: • input “filename”; • output “othername”; • error “whereever”; • Cancel with: • output “/dev/stdout”; • Even when system does not support those devices. Starts a new context SQLCMD – A Better DB-Access?

  23. Other Built-in Commands • Shell escape • ! cat /dev/null • Printing information • echo “This goes to stdout” • errmsg “This goes to stderr” • Query limit • qlimit 32 • Only first 32 rows are shown SQLCMD – A Better DB-Access?

  24. Other Built-in Commands • Benchmark • benchmark on • benchmark off • Timing • time; • clock [on|off]; • sleep 3; SQLCMD – A Better DB-Access?

  25. Contexts • Commands are executed in a context: • Controls many characteristics. • Output format, delimiters, transaction size. • Automatically inherited by new context. • Does not monitor SQL properties. • Each new input file starts a new context. • Cannot alter context of ‘calling’ file. • Beware: edited or rerun commands • Always run in a new context. SQLCMD – A Better DB-Access?

  26. Contexts • Level: 1 Input: /dev/stdin • Output: /dev/stdout Error: /dev/stderr • Date: mm/dd/yyyy Delimiter: '|' • Escape: '\\' Quote: '\"' • EOR: '\n' History size: 50 • Query limit: 0 Transaction size: 1024 • Input Base: 0 BlobDir: /tmp • XML Record Tag: RECORD Format: <TOS> select <BOS> • Heading: <TOS> off <BOS> History: <TOS> off <BOS> • Continue: <TOS> off <BOS> Silence: <TOS> off <BOS> • Trace: <TOS> off <BOS> Types: <TOS> off <BOS> • Verbosity: <TOS> off <BOS> Benchmark: <TOS> off <BOS> SQLCMD – A Better DB-Access?

  27. Contexts • Many attributes have a stack of values: • Up to 10 values per context. • Visible via the ‘context’ command. • Heading, continue, trace, verbose, format, history, silence, format, types, benchmark. • Stack commands are: • Push • Pop • And new values can be set (on, off) • Other attributes have no stack. SQLCMD – A Better DB-Access?

  28. Contexts • For example, you want to drop a table, • But it might not exist. • And that is not an error. • continue push;– saves current state • continue on;– script continues after error • DROP TABLE x; • continue pop;– reinstates previous state • Could just set continue off after the DROP • But this might not be desirable. SQLCMD – A Better DB-Access?

  29. Command Line Options • Try “sqlcmd -h” • Gives verbose summary of options • Basic flags: -d database – select database -f FILE – read SQL from file -e ‘SQL Statements’ – SQL on command line -H – Print column headings -T – Print column types -B – Benchmark mode -x – Trace executed statements SQLCMD – A Better DB-Access?

  30. Command Line Options • Going for the full set: [a-zA-Z] • Only need 11 more options • And one (-G) is obsolescent. • And 7 or so of those are reserved • Long option syntax likely • Heuristic for command line arguments • SQL statements contain spaces. • sqlcmd –d stores ‘info databases’ • Filenames contain no spaces. • sqlcmd –d stores $HOME/tmp/test.sql • If it’s wrong, use ‘-e’ or ‘-f’ explicitly. SQLCMD – A Better DB-Access?

  31. Username and Password • OK to use ‘-u username’ on command line. • Not a good idea to use ‘-p password’. • $SQLCMDPASSWORDS file can hide it. • No default file name! • Permissions should be 400 or 600. • Unchecked – but may be checked in future! • Based on INFOTPASS mechanism: • Used in infotables. • By Ravi Krishna <rkusenet@sympatico.ca> SQLCMD – A Better DB-Access?

  32. Username and Password • SQLCMDPASSWORDS file contains: • database|username|password • If user name given (as well as database): • First matching entry on both yields password. • If no user name given: • First matching entry on database yields username and password. • Applies to command line connections. • Also to the CONNECT statement. SQLCMD – A Better DB-Access?

  33. SQLUNLOAD • Simplest way to unload a table. • The inverse of sqlreload. • Specify database (-d) and table (-t). • Optionally output file, format, delimiters, etc. • Use ‘-O’ option to specify sort order • sqlunload –d stores –t customer –O customer_num • ‘sqlcmd –U’ forces this mode. SQLCMD – A Better DB-Access?

  34. SQLRELOAD • Simplest way to load a table. • The inverse of sqlunload. • Passing resemblance to DB-Load. • But much simpler to use. • DBLDFMT converts fixed format to load format. • Always specify database (-d) and table (-t). • Input file (-i) is optional. • ‘sqlcmd –R’ forces this mode SQLCMD – A Better DB-Access?

  35. Where’s the Source Code? • The International Informix Users Group! • http://www.iiug.org/software • Check the software repository for other tools • utils2_ak in particular. • Distributed as a gzipped tar file. • With the extension ".tgz“ • For example: sqlcmd-80.00.tgz SQLCMD – A Better DB-Access?

  36. Compilation • Building SQLCMD requires ClientSDK. • And a C compiler. • Extract source: • tar -xzf sqlcmd-80.00.tzg • cd sqlcmd-80.00 • ./configure --prefix=$HOME • make • make install Default location is $INFORMIXDIR SQLCMD – A Better DB-Access?

  37. What Can Go Wrong? • Configure can’t find a working ESQL/C. • Install ClientSDK. • Almost any version should be OK (5.00 up). • Set INFORMIXDIR and PATH. • Configure can’t find Bison, Byacc or Yacc. • Rename connecty.y to old.connecty.y • Rename CONNECTY.c to connecty.c SQLCMD – A Better DB-Access?

  38. What Can Go Wrong? • I don’t have the GNU readline library. • This is not an error. • You simply don’t get command editing. • Except by launching an editor. • Configure can’t find my GNU readline library. • Assuming it is installed under /usr/gnu: • LDFLAGS=-L/usr/gnu/lib \ CPPFLAGS=-I/usr/gnu/include \ ./configure --prefix=$HOME SQLCMD – A Better DB-Access?

  39. Installation • Two ways of doing the install. • One-off install for a single machine. • make install • make install prefix=/opt/sqlcmd • Places materials in: • ${prefix}/bin • ${prefix}/man/man1 • ${prefix}/etc SQLCMD – A Better DB-Access?

  40. Installation • Create binary-only distribution • Package for installation on many machines • make BOD • Creates sub-directory BOD • Can be packaged up for copying. • Requires I-Connect on target machine. • But database server is not necessary. SQLCMD – A Better DB-Access?

  41. Installation • BOD can be installed on other machines • Subsequent install (possibly by root): • ./jlss install sqlcmd • ./jlss –u me –g mine –d /opt/sqlcmd \install sqlcmd • Subsequent uninstall: • /opt/sqlcmd/etc/jlss uninstall sqlcmd Default location is $INFORMIXDIR SQLCMD – A Better DB-Access?

  42. What Else Do You Get? • SQLUPLOAD • Inserts or updates data in a table. • Alpha quality code. • INSBLOB, APPBLOB, SELBLOB, etc. • Vignettes handling BYTE and TEXT blobs. • Fully operational toy programs. SQLCMD – A Better DB-Access?

  43. What Else Do You Get? • Documentation on UNLOAD format. • SQLSERVER, SQLCLIENT • Run a single SQLCMD in background. • Each command sent to server • Results read back • Uses ‘sqlcmd -M fifo’ option. SQLCMD – A Better DB-Access?

  44. Bugs? • Report bugs to jleffler@us.ibm.com • Not many known bugs. • Support for extended data types is incomplete • COLLECTIONS, ROWS, DISTINCT types • User-defined types – especially opaque types • BLOB and CLOB (BYTE and TEXT OK) • Does handle • LVARCHAR, BOOLEAN, INT8, SERIAL8 • See TODO file (and ChangeLog). SQLCMD – A Better DB-Access?

  45. Questions and Answers • http://www.iiug.org/software • The current version is 80.00 (2005-11-23) • As of 2005-12-09 Thank You SQLCMD – A Better DB-Access?

  46. Informix User Forum 2005Moving Forward With Informix SQLCMDA Better DB-Access? Jonathan Leffler jleffler@us.ibm.com Atlanta, Georgia December 8-9, 2005

More Related