1 Changes in sqsh-2.5, sqsh-2.5.16 and sqsh-2.5.16.1 2 3 New features, improvements and bug fixes: 4 51 - Feature - Print messages to File (P2F). 6 With this feature you can now set a threshold on the number of error messages or print statements 7 that will be shown on screen for the current batch. If the threshold is exceeded, the remainder 8 of the messages will be written to a file. The threshold is specified with variable $p2faxm and the 9 filename by $p2fname. 10 By default the threshold variable $p2faxm and the filename $p2fname are set to NULL, which means 11 that the feature is disabled. If the threshold value is set to 0, then the feature is still disabled. 12 If you set both these variables then the feature is enabled when you run SQSH interactively. 13 14 For example: Suppose a heap table "inventory" exists which is accidentally loaded twice using bcp 15 and you want to build a clustered index on that table while ignoring the duplicate 16 rows. To prevent an overflow of warning messages on screen you now could do: 17 18 SYBASE.sa.tempdb.1> \set p2fname="~/tmp/sqsh_p2f.out" 19 SYBASE.sa.tempdb.1> \set p2faxm=10 20 SYBASE.sa.tempdb.1> create clustered index inventory_cx on inventory(id) with ignore_dup_row 21 SYBASE.sa.tempdb.2> go 22 Warning: deleted duplicate row. Primary key is '0' 23 Warning: deleted duplicate row. Primary key is '1' 24 Warning: deleted duplicate row. Primary key is '2' 25 Warning: deleted duplicate row. Primary key is '3' 26 Warning: deleted duplicate row. Primary key is '4' 27 Warning: deleted duplicate row. Primary key is '5' 28 Warning: deleted duplicate row. Primary key is '6' 29 Warning: deleted duplicate row. Primary key is '7' 30 Warning: deleted duplicate row. Primary key is '8' 31 Warning: deleted duplicate row. Primary key is '9' 32 Warning: Number of printed server messages exceeds p2faxm=10 limit for current batch. 33 Remaining server messages will be printed to file: /home/sybase/tmp/sqsh_p2f.out 34 35 When you assign a new value to the p2fname variable, the current file is closed and a new one 36 is opened. If the file already exist, then new data will be appended. 37 Note that for each new batch the message counter is reset. This feature is not applicable when 38 running SQSH in batch mode. So when you would execute something like: 39 40 sqsh -C"create clustered index inventory_cx on inventory(id) with ignore_dup_row" \ 41 -Lp2faxm=10 -Lp2fname=/tmp/sqsh_p2f.out 42 43 the variable settings are ignored and while no output file is specified with the "-o" option 44 nor output redirection is used like "... > result.out 2>&1", the warning messages will go to the 45 terminal screen altogether. 46 Please note that if you enable this feature by default by specifying the variables in the ~/.sqshrc 47 file for example and the threshold p2faxm is set rather low, that the output of sp_sysmon 48 for example may inadvertently be written to a file. 49 50 SYBASE.sa.master.1> \set p2faxm=3 51 SYBASE.sa.master.1> \set p2fname=~sybase/tmp/sqsh_p2f.out 52 SYBASE.sa.master.1> sp_sysmon "00:00:10" 53 SYBASE.sa.master.2> go 54 =============================================================================== 55 Sybase Adaptive Server Enterprise System Performance Report 56 =============================================================================== 57 Warning: Number of printed server messages exceeds p2faxm=3 limit for current batch. 58 Remaining server messages will be printed to file: /home/sybase/tmp/sqsh_p2f.out 59 (return status = 0) 60 61 622 - Feature - Implementation of new commands: 63 64 \run: New command that will execute a batch file that allows you to pass on parameters 65 to the script. Suppose you have a simple script file sp_helpdb.sqsh that contains: 66 67 exec sp_helpdb ${1} 68 \go 69 70 If you run this script without specifying a parameter, the expanded string is empty and you 71 just would end up executing 'sp_helpdb', displaying the results on all your databases. However, 72 if you specify a database name as parameter, the result would display extended information 73 for just the specified dbname. In previous versions you could start SQSH only on the command line 74 and provide the parameters mixed with other argument options. For example: 75 76 sqsh -SSYBASE -Usa -i sp_helpdb.sqsh master -D tempdb -e 77 78 But there was no possibility to start a script from the SQSH prompt and passing on arguments 79 as well. You can use the \loop command to run a script file, but that command does not accept 80 additional parameters. As the \loop command is the heart of SQSH, I did not want to take the risk 81 of breaking existing code, so I implemented this new command "\run". Now you can run from the prompt: 82 83 SYBASE.sa.tempdb.1> \run -e -i sp_helpdb.sqsh master 84 85 Other arguments of the \run command are: 86 87 Use: \run [-e] [-f] [-h] [-n] [-p] [-m style] -i filename [optional script parameters ...] 88 -e Run the script file with echo on 89 -f Suppress footers 90 -h Suppress headers 91 -l Suppress separator lines with pretty output style 92 -n Disable SQL buffer variable expansion 93 -p Report runtime statistics 94 -m style Specify output style {bcp|csv|horiz|html|meta|none|pretty|vert} 95 -i filename SQL file to run 96 97 98 \lcd: Local Change Directory. This command takes a directory name as argument and changes the 99 local SQSH context to this directory. You can use "\lcd -" to return back to the previous 100 directory. If you exit SQSH then the shell is still in the same directory from where SQSH was 101 started. 102 103 104 \pwd: Print Working Directory. Show the name of the current local working directory. 105 106 107 \ls: List files in the current directory. The command does not take any arguments and is basically 108 a shortcut for "\shell ls". 109 110 1113 - Feature - Readline filename completion. When Readline support is compiled into SQSH, you have the ability 112 to use Readline keyword completion by using the TAB character to complete an objectname. The list 113 of objectnames is a built-in list, or is provided in a file specified by the $keyword_file variable 114 or the list is dynamically created by executing a query specified in $keyword_query each time you 115 switch database context and if $keyword_dynamic is set to True. Note that $keyword_completion must 116 be set to a value of 1 (lower), 2 (upper), 3 (smart) or 4 (exact) to enable this feature. 117 When you type part of a string that cannot be expanded to an objectname, i.e. there is no match, 118 than SQSH tries to complete the string as a filename in the current directory. So what you can do 119 now for example: 120 121 $> sqsh -SSYBASE -Usa 122 password: 123 SYBASE.sa.master.1> \lcd $SYBASE/$SYBASE_ASE/scripts 124 \lcd: local directory changed to: /opt/sybase/ASE-15_0/scripts 125 SYBASE.sa.master.1> \run -n -i ./instm<TAB> 126 127 results in the completed filename "./instmsgs.ebf". Note that Readline filename completion ignores 128 the value of $keyword_completion, instead you can set completion-ignore-case to "on" in your 129 ~/.inputrc file if you wish case insensitive filename completion. 130 So as long as there are completion matches in the internally created SQSH object list, no filename 131 completion will be attempted and no possible filename completions will be shown. SQSH will only 132 revert to filename completion if the internal object list is exhausted. In the example above, 133 there are most likely no objectnames in your database that start with "./instm", so SQSH will 134 immediately attempt filename completion on this string. 135 136 1374 - Feature - If your server supports SSL connections, i.e. a certificate is installed in the server and a public 138 certificate is available on the client and a SSL filter is specified in 139 $SYBASE/$SYBASE_OCS/config/libtcl[64].cfg like: 140 141 [FILTERS] 142 ssl=libsybfssl.so 143 144 you can then start a secure session by specifying: 145 146 sqsh -Ssybprod1:5001:ssl ... 147 148 The portnumber used on this connection must be configured for SSL connections in the server. The 149 host may be specified with a name or an IP address. 150 The target server address syntax host:port[:filter] is also supported now with the \bcp command. 151 A workaround is implemented to allow host:port addresses when using the FreeTDS library 152 middleware. Note that FreeTDS does not support SSL connections (yet). 153 154 1555 - Improvement - The \go command now accepts the option -e to echo the expanded SQL command buffer before 156 sending it to the server and the -l option will suppress the separator lines with the pretty 157 output style. The echo option was already available as a SQSH startup parameter (i.e sqsh -SSYBASE -e) 158 and could be changed by setting the $echo variable to On or Off. Now it is available on a per 159 batch execution basis. For example: 160 161 SYBASE.sa.tempdb.1> \set WHERECLAUSE="where city like 'B%'" 162 SYBASE.sa.tempdb.1> select * from pubs2..publishers $WHERECLAUSE 163 SYBASE.sa.tempdb.2> \go -e -mpretty -l 164 echo --> SYBASE.sa.tempdb.1> select * from pubs2..publishers where city like 'B%' 165 +========+======================================+======================+=======+ 166 | pub_id | pub_name | city | state | 167 +========+======================================+======================+=======+ 168 | 0736 | New Age Books | Boston | MA | 169 | 1389 | Algodata Infosystems | Berkeley | CA | 170 +--------+--------------------------------------+----------------------+-------+ 171 172 In older versions you had to explicitly set variable 'echo' to on and the pretty 173 output style showed separator lines that could not be suppressed: 174 175 SYBASE.sa.tempdb.1> \set echo=On 176 SYBASE.sa.tempdb.1> select * from pubs2..publishers $WHERECLAUSE 177 SYBASE.sa.tempdb.2> \go -mpretty 178 echo --> SYBASE.sa.tempdb.1> select * from pubs2..publishers where city like 'B%' 179 +========+======================================+======================+=======+ 180 | pub_id | pub_name | city | state | 181 +========+======================================+======================+=======+ 182 | 0736 | New Age Books | Boston | MA | 183 sepline> +--------+--------------------------------------+----------------------+-------+ 184 | 1389 | Algodata Infosystems | Berkeley | CA | 185 +--------+--------------------------------------+----------------------+-------+ 186 187 The -l option on the \go and \run command is a one time option that activates separator 188 line suppression for the current batch or current script file only. There is also a new 189 variable "nosepline" that is a Boolean which defaults to False, but when enabled with 190 \set nosepline=True, in the .sqshrc file for example, will activate pretty separator line 191 suppression permanently and you you do not have to provide the -l parameter to the \go or 192 \run command anymore. 193 194 1956 - Improvement - Two new read only variables are introduced that contain the date and time SQSH was built. 196 The variables are named builddate and buildtime. 197 198 SYBASE.sa.master.1> \echo "Program $version was compiled on $builddate - $buildtime" 199 Program sqsh-2.5 was compiled on Mar 15 2014 - 11:15:42 200 201 2027 - Improvement - SQSH now understands the ANSI inner join syntax when using Readline TAB completion on an alias name 203 to display the column names for the related table. In sqsh-2.4 this only worked for the left, right 204 and outer ANSI join syntax. 205 206 2078 - Improvement - Improved the display of timestamps in the "\history -i" and \snace commands that make use of the 208 $datetime format variable. This variable my contain %q to display milliseconds or microseconds 209 for datetime or bigdatetime datatype columns, but that is not relevant for the timestamps in the 210 command output of "\history -i" and \snace. So the ".%q" will now be filtered out for these commands. 211 212 2139 - Improvement - The autoconf input files that finally make up the configure script are updated to automatically 214 detect 64 bit compilations and thus setting "CPPFLAGS=-DSYB_LP64" in the src/Makefile as required. 215 Also building SQSH on the Cygwin platform is now fully automated by configure and make. 216 This works on both Cygwin32 and Cygwin64 bits platforms. 217 Furthermore the gnulib installation scripts like config.sub, config.guess and install-sh have 218 been refreshed to the latest version available. 219 220 22110 - Improvement -In sqsh-2.1.8 tilde expansion on the command line was introduced by using the tilde_expand() 222 function of the Readline package. By default this tilde expansion is rather strict and only 223 works properly if the tilde is preceded by a blank or tab character and followed with a slash, 224 blank space or newline. So for example you were able to use this in a command like 225 226 SYBASE.sa.master.1> select ... 227 SYBASE.sa.master.2> \go > ~/tmp/result.log 228 229 but you could not use it in 230 231 SYBASE.sa.master.1> \set session="~/.sqsh/session.ini". 232 233 This is being fixed now by implementing tilde expansion in the sqsh_expand() function (where it 234 obviously belongs). When the tilde is preceded by a blank space, tab, double quote, equal sign, 235 colon, > or < and the tilde name specifier is followed by a slash, blank space, tab, newline, 236 double quote, equal sign or colon, then the tilde will be expanded (still using the readline 237 tilde_expand function), otherwise it will be left alone. If you need a tilde where it would 238 otherwise be expanded, you have to use single quotes or you may escape the tilde with \\~. 239 For example: 240 241 SYBASE.sa.master.1> \set searchpath='.:~/bin:~/lib:~sybase/bin:~sybase/lib' 242 243 A tilde will be expanded to your HOME directory and a tilde followed by a unix/linux login name 244 will expand to the home directory for that login, according to the /etc/passwd file. 245 Note that tilde expansion will not occur in the SQL buffer, but only for SQSH commands. 246 With this code change, SQSH is now a little more compliant with tilde expansion in bash. 247 248 24911 - Bugfix - Typing a ^C after a \do command was run, resulted in a segmentation fault. This was caused by 250 a faulty interrupt stack that was not popped after the \do command finished. 251 252 25312 - Bugfix - Fixed a problem with assigning values to the variables colsep, linesep, bcp_colsep and bcp_rowsep. 254 In previous SQSH versions it was not possible to reset these variables to the default 255 by assigning the 'default' string or a NULL value and the latter resulted in a segmentation fault. 256 It is now possible/easier to assign control characters to these variables, for example: 257 258 SYBASE.sa.master.1> \set linesep="\n" 259 SYBASE.sa.master.1> \set linesep=NULL 260 SYBASE.sa.master.1> \set linesep=default 261 262 The last two commands reset linesep to "\n\t". That is, result rows that exceed the physical width 263 ($width variable) of the terminal window are split in multiple lines and subsequent lines start 264 with a tab character by default. 265 266 SYBASE.sa.master.1> \set width=50 267 SYBASE.sa.master.1> \set linesep=default 268 SYBASE.sa.master.1> select * from pubs2..publishers where pub_id="0736" 269 SYBASE.sa.master.2> go 270 pub_id pub_name 271 tab--> city state 272 ------ ---------------------------------------- 273 tab--> -------------------- ----- 274 0736 New Age Books 275 tab--> Boston MA 276 277 SYBASE.sa.master.1> \set linesep='\n' 278 SYBASE.sa.master.1> select * from pubs2..publishers where pub_id="0736" 279 SYBASE.sa.master.2> go 280 pub_id pub_name 281 no tab--> city state 282 ------ ---------------------------------------- 283 no tab--> -------------------- ----- 284 0736 New Age Books 285 no tab--> Boston MA 286 287 28813 - Bugfix - SQSH (when compiled with FreeTDS to connect to a MSSQL server) will now properly display 289 uniqueidentifier datatypes created by the newid() function. This is basically a workaround 290 in FreeTDS processing, i.e. do not use an explicit cs_convert for this datatype but use native 291 FreeTDS internal conversions instead. 292 29314 - Coding - In several places code has been changed to solve compiler complaints on redeclared objects 294 or to improve code readability. See below for details. 295 29615 - Bugfix - The \do command would result in a segmentation fault when the server password is empty (NULL). 297 29816 - configure - Running configure for sqsh-2.5 and sqsh-2.5.16 could fail on some systems where "grep -e" is 299 not supported. This is being fixed now by using the command stored in $EGREP that is determined 300 earlier by configure, instead of using "grep -e" when probing for the current OpenClient version. 301 30217 - Enhancement - The source code is prepared for CT-Library version 16 (CS_VERSION_16, with $SYBASE/OCS-16_0). 303 304 305 All modified source files in release 2.5 306 307 sqsh-2.5/src/cmd_bcp.c - Renamed _CYGWIN32_ defined checks to __CYGWIN__ in order to be able to use this 308 directive on both Cygwin32 and Cygwin64 target builds. 309 Implementation of feature "P2F": Counter is reset to zero at the start of a new batch. 310 Support the "-S host:port[:filter]" syntax with the \bcp command to specify a server 311 address to connect. 312 313 sqsh-2.5/src/cmd_connect.c - Renamed _CYGWIN32_ defined checks to __CYGWIN__ in order to be able to use this 314 directive on both Cygwin32 and Cygwin64 target builds. 315 New callback function "validate_srvname_cb" introduced to enable a feature to 316 specify the ssl filter in -Shostname:portname:ssl type of connections. 317 Allow for -Shostname:portname connections with FreeTDS. 318 Enablement of feature "P2F". 319 Improved time display of command \snace in network authenticated connections like 320 Kerberos. 321 322 sqsh-2.5/src/cmd_do.c - Fixed a bug with interrupt handlers. 323 Implementation of feature "P2F": Counter is set to zero at the start of a new batch. 324 Fixed a bug when the g_password variable is NULL, i.e. the server password is empty. 325 326 sqsh-2.5/src/cmd_go.c - New command option -e can now be specified with the \go command to echo the expanded 327 SQL buffer before it is being send to the server. 328 New command option -l to suppress separator lines with the -mpretty output style. 329 330 sqsh-2.5/src/cmd_history.c - Improvement of displaying timestamps with the "\history -i" command. 331 332 sqsh-2.5/src/cmd.h - Definition of new commands \run, \lcd, \pwd, \ls. 333 334 sqsh-2.5/src/cmd_run.c - Implements the \run command. 335 336 sqsh-2.5/src/cmd_shell.c - Implements the \lcd, \pwd and \ls commands. 337 338 sqsh-2.5/src/dsp.c - Implementation of feature "P2F": Counter is set to zero at the start of a new batch. 339 Fixed a problem with assigning default or NULL values to the variables colsep, 340 bcp_colsep, bcp_rowsep and linesep. 341 342 sqsh-2.5/src/dsp_desc.c - Implemented a fix for the MSSQL uniqueidentifier datatype using FreeTDS. In previous 343 versions this datatype was displayed using the cs_convert function, but apparently 344 that did not work correctly in FreeTDS. Now we let FreeTDS CTlib handle this datatype 345 natively and then it works OK. The display length of the uniqueidentifier datatype is 346 explicitly set to 36 bytes. 347 348 sqsh-2.5/src/dsp.h - Definition of flag DSP_F_NOSEPLINE for the "\go -mpretty -l" option. 349 350 sqsh-2.5/src/dsp_meta.c - Handle the CS_UNIQUE_TYPE datatype (MSSQL uniqueidentifier) correctly in meta display 351 style. 352 353 sqsh-2.5/src/dsp_pretty.c - Implementation of the "\go -mpretty -l" option to suppress additional separator lines. 354 355 sqsh-2.5/src/Makefile.in - Regenerated the list of all dependencies for each sqsh object file. 356 357 sqsh-2.5/src/sqsh_config.h - SQSH_VERSION set to sqsh-2.5 358 359 sqsh-2.5/src/sqsh_expand.h - Defined EXP_TILDE flag. 360 361 sqsh-2.5/src/sqsh_expand.c - Implemented tilde expansion. 362 363 sqsh-2.5/src/sqsh_global.c - Adapted the copyright string. 364 Initialize new global variables for the P2F feature (g_p2f_fp, g_p2fc). 365 366 sqsh-2.5/src/sqsh_global.h - Definition of new variables g_p2f_fp and g_p2fc for the P2F feature. 367 368 sqsh-2.5/src/sqsh_init.c - Close the g_p2f_fp file pointer during sqsh_exit if it is still open. 369 (P2F feature enablement) 370 371 sqsh-2.5/src/sqsh_job.c - Reset getopt variable list for child processes. 372 Removed command line tilde expansion from here and implemented it in sqsh_expand.c 373 374 sqsh-2.5/src/sqsh_readline.c - Changed cast of sqsh_completion function to (rl_completion_func_t *). 375 Implementation of filename tab completion when object list is exhausted. 376 377 sqsh-2.5/src/sqsh_readline.h - Changed declaration of external Readline functions to match the declaration in 378 readline/history.h in case this file is not included from readline/readline.h. 379 380 sqsh-2.5/src/var_ctlib.c - Skip some tests when variable packet_size is assigned NULL value (default). 381 382 sqsh-2.5/src/var_dsp.c - Some cosmetic code changes to increase readability. 383 Improved handling of linesep, colsep, bcp_colsep and bcp_rowsep variables. 384 The check for a "NULL" value assignment to a variable is now case insensitive. 385 386 sqsh-2.5/src/var.h - Changed default of variable linesep to "\n\t". Removed variable start_connected. 387 Defined new variables p2faxm, p2fname (feature P2F), nosepline and read only 388 parameters builddate, buildtime. 389 390 sqsh-2.5/src/var_debug.c - The check for a "NULL" value assignment to a variable is now case insensitive. 391 392 sqsh-2.5/src/var_misc.c - Improved handling of NULL assignments in var_set_esc, var_set_nullint, 393 var_set_nullstr and var_set_int. 394 Assignment of "NULL" values to variables is case insensitive now, i.e. you 395 might execute "\set bcp_colsep=null". 396 Implementation of a new function var_set_p2fname that handles file opening and 397 closing when a filename is assigned to the variable p2fname that is being used 398 by the P2F feature. 399 400 sqsh-2.5/src/var_passwd.c - The check for a "NULL" value assignment to a variable is now case insensitive. 401 402 sqsh-2.5/src/var_readline.c - Improved assignment to Readline stifle_value variable in case the assignment 403 variable is NULL. 404 405 sqsh-2.5/src/sqsh_parser/Makefile.in - Do not remove the generated C files with "make distclean". 406 Add an option to the 'ar' command that can be set with 'configure' to 407 allow for 64 bits archive creation on AIX, i.e. -X32_64. 408 409 sqsh-2.5/src/sqsh_parser/sqsh_parser.c - Define internal parser functions as static (local). Especially xmalloc 410 clashed with Readline functions on some target systems. 411 412 sqsh-2.5/src/sqsh_parser/tsql.c - Generated file from tsql.y: include 'stdlib.h' instead of deprecated 413 'malloc.h'. 414 415 sqsh-2.5/src/sqsh_parser/tsql.l - Correctly process ANSI inner join syntax as well. 416 417 sqsh-2.5/src/sqsh_parser/tsql.y - Include 'stdlib.h' instead of deprecated 'malloc.h'. 418 419 420 421 Changes in sqsh-2.4 422 423 New features, enhancements, bug fixes: 424 4251a - Feature - Readline auto-completion of aliased objects in the current SQL buffer. 426 In sqsh-2.1.8 dynamic auto-completion was introduced when readline support is also 427 compiled in sqsh and the variable $keyword_completion is set to a value between 1 and 4. 428 (See the sqsh man page for a description of the meaning of these values). 429 You could then enter an object name in the input buffer followed by a dot and using the readline 430 auto-completion sequence (usually 1 or 2 TAB or ESC keystrokes), then a list of 431 available column names would be shown to choose from. 432 With this feature enhancement you can now use an alias name for an object in the SQL buffer and 433 use auto-completion on the alias name as well. For example: 434 435 SYBPROD.sa.tempdb.1> \echo $keyword_completion 436 4 437 SYBPROD.sa.tempdb.1> select * from master..sysdatabases d, 438 SYBPROD.sa.tempdb.2> master.dbo.sysusages u 439 SYBPROD.sa.tempdb.3> where d.<TAB><TAB> 440 d.audflags d.def_remote_loc d.durability d.status2 441 d.audflags2 d.def_remote_type d.logptr d.status3 442 d.crdate d.deftabaud d.name d.status4 443 d.dbid d.defvwaud d.spare d.suid 444 d.defpraud d.dumptrdate d.status d.version 445 SYBPROD.sa.tempdb.3> where d.s<TAB><TAB> 446 d.spare d.status d.status2 d.status3 d.status4 d.suid 447 SYBPROD.sa.tempdb.3> where d.s 448 449 450 So when you type d.<TAB><TAB> (where <TAB> means a keystroke of the TAB key), then sqsh 451 will parse the SQL buffer entered so far and determines that the alias name 'd' actually 452 refers to the master..sysdatabases table. So it dynamically creates a list of columns 453 for this table and shows them on screen. 454 In the second example we have typed 'd.s' already and when we ask for a completion 455 (<TAB><TAB>) then sqsh will produce a list of all column names in sysdatabases that 456 start with a 's'. 457 458 This feature enhancement is contributed by K.M. Hansche. 459 4601b - Enhancement - Column name auto-completion is now capable of handling cross database object names like in 461 the above example. In previous versions of sqsh you could only use column name completion 462 of objects located in the current database. 463 Please note - in order for this feature to work correctly - you need to specify a 464 fully qualified object name as a consecutive string without spaces. In ASE, for example, 465 it is perfectly valid to code: 466 467 select * from master . dbo . sysdatabases d where d.dbid=1 468 469 However, sqsh readline auto-completion is expecting an object name master.dbo.sysdatabases. 470 4712 - Feature - A new boolean variable 'usedbcheck' is defined that may be set to True (default is False). 472 This will result in a check being performed if sqsh is able to access a database that is 473 provided with the -D parameter while sqsh is run in batch mode. This is to prevent a script 474 file is being executed in the wrong database. Suppose a user 'test_user' is trying to execute 475 a batch script in the pubs3 database, but the database 'pubs3' is in 'dbo use only' mode. 476 The test_user is a normal user in this database. The default database of test_user is pubs2. 477 478 ~$ sqsh -Utest_user -Ptest_user_pw -Dpubs3 -C"select db_name()" 479 Msg 923, Level 14, State 1 480 Server 'SYBPROD', Line 1 481 User 6 not allowed in database 'pubs3' - only the owner of this database can access it. 482 Msg 10334, Level 18, State 127 483 Server 'SYBPROD', Line 1 484 Permission related internal error was encountered. Unable to continue execution. 485 Msg 10334, Level 18, State 80 486 Server 'SYBPROD', Line 1 487 Permission related internal error was encountered. Unable to continue execution. 488 489 ------------------------------ 490 pubs2 491 492 (1 row affected) 493 494 Although we try to execute a script (or statement in this case with the -C parameter) in the 495 pubs3 database, we end up in the pubs2 database and thus may inadvertently cause a lot of 496 problems when a script file is executed there. When we enable the variable 'usedbcheck', 497 sqsh will abort if it is not able to set the database to the one specified with the -D 498 parameter. Note that we enable the variable here with -Lusedbcheck=On on the command line, 499 but of course you can also permanently enable the variable in the .sqshrc file. 500 501 ~$ sqsh -Utest_user -Ptest_user_pw -Dpubs3 -C"select db_name()" -Lusedbcheck=On 502 Msg 923, Level 14, State 1 503 Server 'SYBPROD', Line 1 504 User 6 not allowed in database 'pubs3' - only the owner of this database can access it. 505 Msg 10334, Level 18, State 127 506 Server 'SYBPROD', Line 1 507 Permission related internal error was encountered. Unable to continue execution. 508 Msg 10334, Level 18, State 80 509 Server 'SYBPROD', Line 1 510 Permission related internal error was encountered. Unable to continue execution. 511 sqsh: ERROR: Unable to use database 'pubs3' in batch mode 512 513 Note the error message at the last line. Also note that this feature does not work in interactive mode. 514 So when you start sqsh in interactive mode, that is when you get a prompt, then sqsh will not abort and 515 you have to check the error messages and the sqsh prompt to determine the current database scope. 516 517 ~$ sqsh -Utest_user -Ptest_user_pw -Dpubs3 -Lusedbcheck=On 518 Msg 923, Level 14, State 1 519 Server 'SYBPROD', Line 1 520 User 6 not allowed in database 'pubs3' - only the owner of this database can access it. 521 Msg 10334, Level 18, State 127 522 Server 'SYBPROD', Line 1 523 Permission related internal error was encountered. Unable to continue execution. 524 Msg 10334, Level 18, State 80 525 Server 'SYBPROD', Line 1 526 Permission related internal error was encountered. Unable to continue execution. 527 SYBPROD.test_user.pubs2.1> 528 5293 - Fix - In the situation were a login trigger decided a login attempt should be aborted using syb_quit() 530 and sqsh was called with a -D parameter, then sqsh started to loop producing the output: 531 532 Open Client Message 533 Layer 1, Origin 1, Severity 1, Number 50 534 ct_results(): user api layer: external error: The connection has been marked dead. 535 Open Client Message 536 Layer 1, Origin 1, Severity 1, Number 50 537 ct_results(): user api layer: external error: The connection has been marked dead. 538 ... 539 540 This was caused by a callback function that neglected the dead connection while it thought it 541 was still in a login sequence at the time sqsh attempted to execute a 'use <dbname>' command. 542 This is being fixed now so that the callback function will abort sqsh when the connection 543 is marked dead, directly following an unsuccessful login attempt. 544 5454 - Fix - In sqsh-2.2.0 the \reconnect command code was improved to be able to recover from reconnect 546 failures due to timeouts or an abort request by typing ^C, for example. Unfortunately this broke 547 the ability to use \reconnect inside a \do block. This is being fixed now so that you can use a 548 script again like: 549 550 use dbadb 551 \go 552 create table srvadmin (srvname varchar(30), dbuser varchar(30), dbpasswd varchar(30), dbname varchar(30)) 553 \go 554 insert into srvadmin (srvname, dbuser, dbpasswd, dbname) values ('SYBPROD', 'dbaprod', 'dbaprodpw', 'monproddb') 555 insert into srvadmin (srvname, dbuser, dbpasswd, dbname) values ('SYBTEST', 'dbatest', 'dbatestpw', 'montestndb') 556 insert into srvadmin (srvname, dbuser, dbpasswd, dbname) values ('SYBDEV', 'dbadev', 'dbadevpw', 'mondevdb') 557 \go 558 select * from dbadb..srvadmin 559 \do 560 \reconnect -S#1 -U#2 -P#3 -c 561 select srvname=@@servername, loginame=suser_name(), dbname=db_name(), spid=@@spid 562 exec sp__dba_monsrv #4 563 \go 564 \done 565 5665 - Fix - Another contribution by K.M. Hansche is a fix in dsp_html.c to produce correct html5 compatible 567 table output when you use the "\go -m html" command to generate result set output in html format. 568 569 570 All modified source files in release 2.4 571 572 sqsh-2.4/src/cmd_connect.c - Implementation of feature 2 and fix 3. 573 574 sqsh-2.4/src/cmd_do.c - Implementation of fix 4. 575 576 sqsh-2.4/src/dsp_html.c - Implementation of fix 5. 577 578 sqsh-2.4/src/Makefile.in - Implementation of feature 1 to include the sqsh_parser.a object library. 579 In the root directory the files Makefile.in and configure have also been adapted to 580 create and run a Makefile in src/sqsh_parser/ 581 582 sqsh-2.4/src/sqsh_config.h - Expanded the size definition of a password buffer from 30 to 64 characters. 583 Changed the version string to sqsh-2.4. 584 585 sqsh-2.4/src/sqsh_readline.c - Implementation of feature enhancement 1. 586 587 sqsh-2.4/src/var.h - Implementation of feature 2 (define new variable "usedbcheck"). 588 589 sqsh-2.4/src/sqsh_parser/ - A new directory containing the source files to parse a SQL buffer for alias names 590 that are eligible for readline auto-completion. Implementation of feature 1. 591 592 593 594 Changes in sqsh-2.3 595 596 New features, enhancements, bug fixes: 597 5981 - Feature - A new boolean variable $localeconv is introduced that will enable the use 599 of the active Operating System locale with sqsh when displaying the result set 600 for datetime datatypes (DATETIME, SMALLDATETIME, DATE, TIME, BIGDATETIME, 601 BIGTIME) in combination with the $datetime, $datefmt, $timefmt variables and the 602 real, float, numeric, decimal and money/smallmoney datatypes. 603 By default this variable is 0 (off, false) which results in the internal C/POSIX 604 locale being used. 605 (In the sqsh manual page there was a remark with the $datetime variable, stating 606 that the OS locale would always be used to display the datetime results, but as 607 the locale was never actually set in sqsh, the locale would always be C/POSIX by 608 default, anyway). 609 Note that you can set this variable in your .sqshrc file to enable it by default. 610 (\set localeconv=1) 611 612 For example: 613 614 $ export LANG=nl_NL.utf8 615 $ locale -ck LC_TIME 616 LC_TIME 617 abday="zo;ma;di;wo;do;vr;za" 618 day="zondag;maandag;dinsdag;woensdag;donderdag;vrijdag;zaterdag" 619 abmon="jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec" 620 mon="januari;februari;maart;april;mei;juni;juli;augustus;september;oktober;november;december" 621 am_pm=";" 622 ... 623 624 $ locale -ck LC_NUMERIC 625 LC_NUMERIC 626 decimal_point="," 627 thousands_sep="" 628 grouping=-1;-1 629 numeric-decimal-point-wc=44 630 numeric-thousands-sep-wc=0 631 numeric-codeset="UTF-8" 632 633 $ sqsh -Llocaleconv=On -Ldatefmt="%A %e %B %Y" -C"select convert(date,getdate())" 634 635 --------------------------- 636 dinsdag 23 juli 2013 637 638 (1 row affected) 639 $ sqsh -Llocaleconv=Off -Ldatetime="%A %e %B %Y" -C"select getdate()" 640 641 --------------------------- 642 Tuesday 23 July 2013 643 644 (1 row affected) 645 $ sqsh -Llocaleconv=True -C"select 99.99" 646 647 ------- 648 99,99 649 650 (1 row affected) 651 $ sqsh -Llocaleconv=False -C"select 99.99" 652 653 ------- 654 99.99 655 656 (1 row affected) 657 658 Note that sqsh will not take grouping of thousands into account. Only the decimal_point 659 or mon_decimal_point will be used in displaying numerical or monetary values. 660 A big thank you to K.M. Hansche who provided the initial code to enable locale 661 conversions. 662 6632 - Enhancement - Another contribution by K.M. Hansche is some code to enable the use of a \for loop command 664 inside a sqsh function definition (the \func command). 665 6663 - Enhancement - Another contribution by K.M. Hansche is some code to produce html5 compatible output when you use 667 the "\go -m html" command to generate result set output in html format. 668 6694 - Enhancement - Display size and justification of datatypes bigint (CS_BIGINT_TYPE), unsigned smallint 670 (CS_USMALLINT_TYPE), unsigned int (CS_UINT_TYPE) and unsigned bigint (CS_UBIGINT_TYPE) 671 adapted. Previously these datatypes were displayed with length 256 bytes and left 672 justification. Display is now right justified and output size is: 673 674 bigint 20 characters 675 unsigned smallint 5 characters 676 unsigned int 10 characters 677 unsigned bigint 20 characters 678 679 Also the justification of all datetime datatypes is set to right. This was not the case 680 for DATE, TIME, BIGDATETIME and BIGTIME. Where appropriate, the display size is also fixed 681 for these datatypes. 682 6835 - Enhancement - The datatypes bigdatetime and bigtime will now be correctly processed with microseconds 684 taken into account, even when variables $datetime and $timefmt are not set (i.e. default). 685 Basically, the following defaults will be used for BIGDATETIME = "%b %d %Y %l:%M:%S.%q%p" 686 and BIGTIME = "%l:%M:%S.%q%p". See the strftime manual page for a description of these 687 % variables. 688 689 1> \set datetime=NULL 690 1> \set timefmt=NULL 691 1> select convert(bigdatetime,getdate()) 692 2> select convert(bigtime,getdate()) 693 3> go 694 695 ----------------------------- 696 Jul 23 2013 11:52:21.440000AM 697 698 (1 row affected) 699 700 ----------------- 701 11:52:21.440000AM 702 703 (1 row affected) 704 705 706 When you set the $datetime and/or $timefmt variables according to the strftime variables 707 that start with %, you now have to use %q to expand milliseconds or microseconds. In 708 previous versions of sqsh the %u variables specified the number of milliseconds, but as 709 %u also specifies the day number of the week (Monday 1 .. Sunday 7), this is now replaced 710 by %q as this was the only free character left. Note that %q automatically expands to 711 microseconds when a bigdatetime or bigtime datatype is used, and expands to milliseconds 712 with regular datetime and time datatypes. 713 7146 - Enhancement - All missing CS_*_TYPES in metadata description processing are being added now. This means that 715 when you request the result set metadata, the data types of the columns are properly 716 shown, instead of <INVALID>. Also some other missing status information from newer Open Client 717 versions are added as well. For example: 718 719 1> select convert(bigint,1) 720 2> \go -m meta 721 CS_ROW_RESULT 722 CS_BROWSE_INFO = CS_FALSE 723 CS_CMD_NUMBER = 1 724 CS_NUMDATA = 1 725 CS_NUMORDERCOLS = 0 726 COLUMN #1 727 name = 728 namelen = 0 729 datatype = CS_BIGINT_TYPE 730 format = CS_FMT_UNUSED 731 maxlength = 8 732 scale = 0 733 precision = 0 734 status = CS_UPDATABLE 735 count = 0 736 usertype = 43 737 locale = 0x0x8ad2e70 738 CS_TRANS_STATE = CS_TRAN_COMPLETED 739 CS_CMD_DONE 740 CS_CMD_NUMBER = 1 741 CS_ROW_COUNT = 1 742 CS_TRANS_STATE = CS_TRAN_COMPLETED 743 744 7457 - Bugfix - In sqsh-2.2.0 an annoying bug was introduced when displaying binary data. As the string 746 terminator was not properly set in the output string, the output string could contain 747 garbage and scrambled data. This is being fixed now. 748 7498 - Bugfix - When the first character on a line of input is the # character, and it is not a potential 750 temporary table name, then sqsh assumed the line contained only a comment and was discarded 751 from the SQL buffer. This could lead to syntax errors in the following situation: 752 753 /* ############################################ 754 # This is a block of comment that should # 755 # be properly handled and added to the # 756 # SQL buffer that will be send to the server.# 757 # ############################################*/ 758 759 What happened in older versions was that only the first line was put into the SQL buffer, 760 but the other lines were ignored. That led to a missing closing comment statement (*/) and thus 761 to a syntax error on the server. 762 When sqsh now determines a # character on the first position in the input stream, it checks 763 the current SQL buffer for any nested level of C style comments. When the buffer started any 764 comment that is not closed yet, the line of input will just be added to the SQL buffer as is. 765 766 7679 - Bugfix - Older versions of sqsh could only handle a line of input of max 4096 bytes. When readline support 768 was compiled in, and a larger line of input was read from the input stream, that line of input was 769 copied to a fixed buffer of 4096 bytes, resulting in a buffer overflow and a crash (core dump) of 770 sqsh. When readline support was not compiled in, the buffer would be truncated at 4K. 771 Now sqsh uses a flexible input buffer of initial size 16K. When more data is copied into this 772 buffer, it will automatically expand to fit the data. 773 Note, when no readline support is used, the buffer is still limited to 16K, which still might 774 lead to silent truncation. 775 776 777 778 All modified source files in release 2.3 779 780 sqsh-2.3/src/cmd_connect.c - Removed some useless code in autouse processing. 781 782 sqsh-2.3/src/cmd_do.c - Enable \for command inside \func command. (Enhancement 2) 783 784 sqsh-2.3/src/cmd_func.c - Correct error handling for invalid \func calls. 785 786 sqsh-2.3/src/cmd_input.c - Implementation of bugfix 8. 787 788 sqsh-2.3/src/config.h.in - Implementation of feature 1. 789 790 sqsh-2.3/src/dsp_conv.c - Implementation of feature 1 and enhancements 4 and 5. 791 792 sqsh-2.3/src/dsp_desc.c - Implementation of feature 1 and enhancements 4 and 5 and bugfix 7. 793 794 sqsh-2.3/src/dsp_html.c - Implementation of enhancement 3. 795 796 sqsh-2.3/src/dsp_meta.c - Implementation of enhancement 6. 797 798 sqsh-2.3/src/sqsh_config.h - Set version to sqsh-2.3. 799 800 sqsh-2.3/src/sqsh_global.c - Implementation of feature 1. 801 802 sqsh-2.3/src/sqsh_global.h - Implementation of feature 1. 803 804 sqsh-2.3/src/sqsh_init.c - Implementation of feature 1. 805 806 sqsh-2.3/src/sqsh_job.c - Simple code fix. A variable was assigned a value through some 807 hash bucket calculation, but instead of using this variable, the 808 bucket was calculated again when it was needed. 809 810 sqsh-2.3/src/sqsh_readline.c- Implementation for bugfix 9. 811 812 sqsh-2.3/src/var.h - Implementation of feature 1 (define new variable "localeconv"). 813 814 sqsh-2.3/src/var_misc.c - Implementation of feature 1. Change locale settings when value of localeconv 815 is changed from false to true and vice versa. 816 817 818 819 What is new or fixed in version sqsh-2.2.0 820 821 New features, enhancements, bug fixes: 822 8231 - Feature - You can now define a variable $readline_histignore with a colon separated 824 list of keywords, or a regular expression, that readline should ignore and 825 not store in the readline history list/file. So if you define for example: 826 827 \set readline_histignore='go:lo:mo:exit:quit:vi:!!:GO' 828 or 829 \set readline_histignore='"RE:^[glm]o$|^cd |^exit$|^quit$|^vi$"' 830 831 then these words, when typed on a line by itself, will not be stored in the 832 readline history list and cannot by retrieved with ^P, ^N or cursor up, cursor 833 down keys (in emacs mode, k and j in vi mode). This will reduce the number 834 of key strokes to type when you want to retrieve a previous entered command 835 line. Also the last word of the previous command line will be available 836 under the Meta-_ key in readline. So when you type: 837 838 1> select count(*) from master..sysdatabases 839 2> go 840 841 You only have to type arrow up, or ^P only once to retrieve the previous 842 select statement again, otherwise the "go" command would be displayed 843 first. Second, the Meta-_ key would contain the string master..sysdatabases 844 so you do not have to completely retype the table name. Otherwise the meta 845 character would contain the keyword "go". 846 847 1> select name from <Meta-_> 848 849 would be sufficient to get: 850 851 1> select name from master..sysdatabases 852 853 By default the variable $readline_histignore is NULL and all words typed 854 on input will be stored in the readline history. (Depending on 855 $readline_histsize which is 100 by default). Note that when you want to use 856 a regular expression, the string must begin with RE: and the string must be 857 embedded in single quotes and double quotes to prevent early expansion 858 of the string during variable assignment and printing problems if you 859 \echo $readline_histignore because of the pipe characters. The regular 860 expression is evaluated case insensitive, so uppercase GO would also 861 be ignored. See the GNU regex manual for more information on regular 862 expressions. 863 8642 - Feature - The \bcp command accepts a slicenumber or a partition name to bcp into 865 if the table is partitioned. For example, if you have a semantically list 866 partitioned table on year and a partition name py2013 exists: 867 868 $> sqsh -SPROD -Uadmin_prod -PadminPRDpwd -Dproddb 869 PROD.admin_prod.proddb.1> select * from material where year=2013 870 PROD.admin_prod.proddb.2> \bcp -SDTA -Uadmin_dta -PadminDTApwd 871 -i"truncate table tstdb..material py2013" tstdb..material:py2013 872 873 Or when you have a round robin partitioned table and you want to 874 load data into partition slice number 2: 875 876 PROD.admin_prod.proddb.1> select * from cust 877 PROD.admin_prod.proddb.2> \bcp -SDTA -Uadmin_dta -PadminDTApwd tstdb..cust:2 878 879 There is also a new option to the \bcp command, -T, that will disable 880 character set conversion by the client when data transfer from source 881 to target is in progress. When you receive character set conversion 882 errors, you might try this transit option to solve these errors. Otherwise 883 you might set the sqsh and/or \bcp charset parameter identical to the 884 character set of the database server(s), using the -J parameter. 885 As an example sqsh is started using the utf8 character set and connects to 886 an iso_1 database server: 887 888 [mwesdorp@localhost ~]$ sqsh -SJUPITER -Jutf8 889 JUPITER.sa.master.1> cd pubs2 890 JUPITER.sa.pubs2.1> select * into tempdb..titles from titles where 1=2; 891 (0 rows affected) 892 JUPITER.sa.pubs2.1> select * from titles 893 JUPITER.sa.pubs2.2> \bcp tempdb..titles 894 Msg 4847, Level 16, State 1 895 Server 'JUPITER', Line 1 896 BCP insert operation is disabled when data size is changing between client and server character sets. 897 Please use BCP's -Y option to invoke client-side conversion. 898 899 Command has been aborted. 900 901 Open Client Message 902 Layer 1, Origin 3, Severity 0, Number 14 903 blk_init(): blk layer: CT library error: Failed when CT_Lib routine ct_results() called. 904 \bcp: Unable to initialize bulk copy on table 'tempdb..titles' 905 Open Client Message 906 Layer 1, Origin 1, Severity 1, Number 155 907 ct_send_data(): user api layer: external error: 908 This routine cannot be called when the command structure is idle. 909 910 911 JUPITER.sa.pubs2.2> \bcp -T tempdb..titles 912 913 Starting copy... 914 Batch successfully bulk-copied to SQL Server 915 916 18 rows copied. 917 Clock Time (sec.): Total = 0.0183 Avg = 0.0010 (983.98 rows per sec.) 918 919 The first BCP operations fails, while the second BCP operation using the -T 920 option succeeds. The data is just passed on in transit without any 921 character set conversion necessary as both the source and target servers use 922 the same character set. 923 9243 - Feature - Implementation of semicolon_hack2 to allow for multiple semicolons on one 925 line of input. The semicolons will be used as command separator and act as 926 a \go command for regular SQL statements. So now you can do for example: 927 928 echo "cd master;select name from sysdatabases;select name from syslogins;\echo Done;" | \ 929 sqsh -SDTA -Lsemicolon_hack2=On 930 931 When you have both semicolon_hack and semicolon_hack2 enabled, the second 932 one will take precedence. When hack2 is enabled, semicolon_hack may be 933 disabled. Semicolon_hack2 will also use $semicolon_cmd to be executed 934 when a SQL buffer is available for processing. 935 Note that the 'cd' command in this example will execute a use database 936 command. The function is available in the sqshrc-2.2.0 example file in the 937 doc directory. 938 9394 - Feature - When saving the history buffers, first a merge will be performed with 940 the existing history file if variable histmerge=On, to prevent the loss of 941 history buffers saved by another sqsh session. It happened to me too many 942 times that I had one sqsh session open to do some real work and another 943 session to monitor server status with sp_who, sp_lock, sp_showplan, etc. 944 When I then quit the first session followed by quitting the second session, 945 I then would end up with only a bunch of sp_who, sp_lock statements in 946 my history file as the second sqsh session would have completely overwritten 947 the history file again. 948 As usual this new feature is off by default, so you have to explicitly 949 enable it by specifying \set histmerge=On or set it in in the .sqshrc file. 950 It is also recommended to use histunique=On when you enable histmerge. 951 952 Note that this can also lead to strange side effects if you have the 953 hist_auto_save option on and another user is logged in with the same 954 Unix/Linux account and is also using sqsh. This is often the case when multiple 955 DBA's ssh into the same sybase account on the database server hostmachine. 956 If your session automatically saves the history buffers after a configured 957 number of changes, then your history may all of a sudden contains the 958 SQL executed by your colleagues as well, as the history of both sessions will 959 be merged eventually. But after all, basically the same might happen with the 960 bash history file in that kind of situation. 961 9625 - Feature - You can create a TDS trace file and a protocol log file that can be translated 963 with Ribo. When sqsh is build with DEBUG enabled in the Makefile and you enable 964 debugging for TDS with the -lTDS startup flag or execute \set debug=TDS followed 965 by a \reconnect , and the variables debug_tds_logdata and/or debug_tds_capture 966 are set to specify a filename, then CT-library will write extensive logging 967 information. 968 The TDS capture file can be translated with Ribo. To be able to create 969 debug_tds_logdata, sqsh must dynamically link to the Sybase devlib libraries 970 (Add $SYBASE/$SYBASE_OCS/devlib in front of $LD_LIBRARY_PATH), 971 or have been statically linked with these libraries. 972 Note, this option is for administrators who wants to debug Client-Server 973 communication. See Sybase documentation on ct_debug for more information. 974 This features makes use of the ct_debug call that is available from 975 OpenClient and SDK version 12.5.1. 976 9776 - Enhancement - The \connect (and also \reconnect) command installs a SIGINT signal handler 978 in sqsh-2.2.0. This will prevent memory faults and core dumps when you type 979 ^C during \connect or \reconnect. 980 Also when typing ^C during \reconnect will properly return to the original 981 session. This is handy when the server you are trying to connect to does not 982 respond in a timely manner and you do not want to wait for a login timeout. 983 9847 - Enhancement - The use of the $datetime variable is improved in a number of locations 985 to strip of [] which is used as a filter to hide %S (seconds) when 986 smalldatetime datatypes are used in result set processing. Also %u for 987 milliseconds will be replaced by "000" were appropriate. A minor bug is fixed 988 in the display routine using the $datetime variable if %u was used. The 989 final result skipped the first character after %u in the format definition. 990 9918 - Enhancement - New debug choices added for HISTORY (or HIST for short) and TDS. 992 When debugging the ENV subsystem, the printed messages of variable names 993 is improved as it sometimes printed a whole line of a script that contained 994 a variable and that variable was being expanded. In order to use debugging 995 you have to uncomment DEF_DEBUG in the Makefile and start sqsh using the 996 -l parameter or set the $debug variable. 997 9989 - Enhancement - Hide password processing is improved during sqsh startup. First, the whole 999 argument list is searched for -P parameters and only the last one will be used. 1000 In previous versions each -P parameter resulted in the startup of a child 1001 process that wrote to a pipe and the parent process re-executed itself in order 1002 to read from the pipe and hide it's parameter list from the OS process list. 1003 Normally speaking you would only supply the -P parameter once, but in theory 1004 you can supply the -P parameter many times on the command line, but only 1005 the last one will count. 1006 Now only one child process is created (fork) that writes the password to 1007 a pipe and the parent process will now wait for the child to finish with a 1008 call to waitpid() before it continues to re-execute itself and read from the 1009 pipe. This will prevent <defunct> child processes that were not properly 1010 administered as finished by the parent process in previous versions of sqsh. 1011 101210 - Bug fix - When you exit from sqsh, sqsh will now perform a normal disconnect from the 1013 server you are connected to, and only if that fails it uses a CS_FORCE_CLOSE 1014 call to ct_close. The same is true for ct_exit which will use a CS_FORCE_EXIT 1015 only if a normal ct_exit fails. By doing this, sqsh will properly notify the 1016 server that it is disconnecting, by sending a LOGOUT TDS token. 1017 Previous versions only performed a CS_FORCE_CLOSE disconnect, which resulted 1018 in not sending a LOGOUT token. I noticed with previous sqsh versions for example, 1019 that SAP Sybase IQ 15.4 prints error messages in the server logfile when a 1020 session disconnects using the CS_FORCE_CLOSE flag to the ct_close call. 1021 102211 - Bug fix - The \reconnect command will also save the context prior to - and restore 1023 the context after a \reconnect failure, as it already did with the connection 1024 information. In previous sqsh versions the context was left in an undefined 1025 state when a \reconnect to a server failed and the original connection was 1026 restored. That left the connection basically useless and you had to quit sqsh 1027 altogether in that situation. Now you can successfully go on with the original 1028 session. 1029 103012 - Bug fix - Fixed an encryption bug with the \lock command. The \lock command is used to 1031 lock the current sqsh session. Normally speaking a user who is using sqsh in 1032 a xterm in a X-Windows environment, would lock the screen to disable 1033 unauthorized access when the user stepped away from the computer. But when 1034 you start sqsh from a console, you can use the \lock command just to do that. 1035 You can assign a password to the variable $lock, that must be typed to 1036 unlock a sqsh session that was locked. But you can also use the UNIX password 1037 from the login if sqsh was linked with the -lcrypt library. 1038 The \lock command will then use the password from /etc/password or 1039 /etc/shadow. However, the method to encrypt the typed password to be able to 1040 compare it with the stored password was in error. As a consequence the 1041 encrypted passwords never matched and thus the session could never be unlocked 1042 and it could only be killed. 1043 Note that /etc/password and/or /etc/shadow need to be readable by sqsh in 1044 order to read the encrypted password. 1045 104613 - Bug fix - Sqsh stores all its variables in a global environment (g_env) that is basically 1047 a kind of hash table. In some situations sqsh can start a transactional save-point 1048 on this global environment, which means that all changes can be logged and 1049 depending on results can be committed or rolled back. So changes to the variable 1050 list can be an addition of a new variable, deletion of an existing variable or a 1051 modification of the value of a variable. When a variable is queried then sqsh first 1052 searches its global environment, and if the variable does not exist there, then 1053 it goes to the OS environment to try to obtain the variable from there. 1054 When you start sqsh and do not specify a servername with the -S parameter then 1055 sqsh will try to obtain the contents of the DSQUERY variable. As this variable does 1056 not initially exist in the global environment, it is obtained from the OS 1057 environment. 1058 When you then tried to \reconnect to another server using \reconnect -S<newserver> 1059 then sqsh would create a new global environment variable named DSQUERY within a 1060 g_env transaction. When the connection attempt failed, the transaction would be 1061 rolled back and the DSQUERY variable should be deleted from the internal 1062 environment, so that subsequent DSQUERY searches would go to the OS environment 1063 again. However, due to some missing code, the addition of a new variable to g_env 1064 was not logged and thus not deleted during the roll back. 1065 When the connection attempt failed, then the global DSQUERY variable still 1066 contained the name <newserver>. Subsequent DSQUERY lookups would result 1067 in <newserver> instead of the contents of the OS environment variable DSQUERY. 1068 This would then specify the wrong servername in your prompt for example. 1069 1070 When adding new variables during an active save-point, the variable will 1071 now be added to the internal log and when the save-point is rolled-back, 1072 the variable will now be properly deleted. 1073 In sqsh-2.2.0 a number of variables that were initially missing in the global 1074 environment are defined there now from the start. This diminishes the probability 1075 of strange behavior when sqsh looks up a variable and find it accidentally in the 1076 OS environment. These variables are: 1077 1078 autouse, debug_tds_logdata, debug_tds_capture, histmerge, readline_histignore, 1079 script, semicolon_hack2, tds_version. 1080 1081 1082 108314 - Bug fix - When sqsh was started with the -i parameter to specify a script file, 1084 the filename was stored in the ${0} global internal environment, but was 1085 never retrieved from there when expanding ${0}. Instead, expanding ${0} 1086 resulted in retrieving the name of the sqsh program name from another internal 1087 program structure. This is fixed now so that ${0} inside a script file will 1088 correctly expand to the script file name or to the function name when sqsh 1089 is executing a named function. 1090 1091 1092 109315 - Bug fix - Usage message of \do is improved. 1094 Usage message of \hist-load is fixed. 1095 Usage message of \hist-save is fixed. 1096 1097 1098 109916 - Bug fix - Using the \read command to assign a value to a variable by redirecting from 1100 an input file from the sqshrc or session file was broken. This is now fixed. 1101 The corresponding Sourceforge bug report is 1392951. 1102 You can now use the following construct in a session (or sqshrc) file: 1103 1104 \if [ ( "$DSQUERY" = "NEPTUNE" -o "$DSQUERY" = "JUPITER" ) -a "$username" = "sa" ] 1105 \read password < $HOME/.sqsh/sa_universe.pwd 1106 \fi 1107 So you can connect with: sqsh -SNEPTUNE -Usa for example and the connect 1108 procedure will execute the session file and set the correct password from 1109 the file. 1110 Note that you must supply the full pathname to the input file, otherwise 1111 sqsh would try to locate the file in the current directory. That brings me to 1112 the next fix in this area: When sqsh was unable to open the file because it 1113 does not exist, then sqsh would output: "sqsh: No error". 1114 That had to do with the fact that a call to a function to set the error message 1115 also used another function call to produce the filename, and that call intervened 1116 with the error administration by setting the error code back to zero and thus 1117 the \read command was not able to produce the correct error string anymore. Do 1118 still follow me? Well, nevertheless, that is also fixed now and correct error 1119 messages will be produced with redirected files (both > and <). For example, 1120 suppose we want to write to an existing file in /tmp but we do not have 1121 permissions to write to that file: 1122 1123 [101] JUPITER.sa.tempdb.1> sp_who 1124 [101] JUPITER.sa.tempdb.2> go > /tmp/file_owned_by_root 1125 sqsh: Unable to open /tmp/file_owned_by_root: sqsh_open: Permission denied 1126 1127 Older sqsh versions would produce the "sqsh: No error" message. 1128 1129 113017 - Code fix - Solved a couple of compiler warnings that were generated when using gcc -Wall. 1131 Most of them are regarding unused variables, type cast problems, uninitialized 1132 variables, etc. See below for a description of the changes per source file. 1133 1134 1135 113618 - Enhancement - Last minute change: Added "-J charset", "-A packet_size", "-G tds_version", 1137 and "-z language" options to the \connect and \reconnect commands. You can 1138 now do "\reconnect -Jutf8" to change the client charset from the default 1139 to utf8 without totally stopping and starting sqsh with the -J parameter, for 1140 example. 1141 1142 1143This new version of sqsh has been compiled, linked and tested on Linux Ubuntu 10.04 LTS 32 bit, 1144CentOs 5.9 and 6.4 64 bit, Cygwin on Windows 7, Solaris 10 x86_64 32 and 64 bit and AIX 5.3L 64 1145bits with Sybase OpenClient 15.7, 15.5, 15.0, 12.5.4 and 12.5.1 and freetds 0.83 and 0.91. 1146I hope you will enjoy this new version of sqsh as much as I liked developing it. 1147Cheers, Martin W. 1148 1149 1150 All modified source files in release 2.2.0 1151 1152sqsh-2.2.0/src/cmd_bcp.c 1153 Implementation of feature 2 - Bcp into partitioned tables using a partition name or slice 1154 number. New -T option to disable local character set conversion at the client and just transfer 1155 the data in transit from source to target server. 1156 1157sqsh-2.2.0/src/cmd_buf.c 1158 Code fix 17 - Solved a number of compiler warnings. 1159 cmd_buf.c: In function ‘cmd_buf_show’: 1160 cmd_buf.c:421: warning: field width should have type ‘int’, but argument 2 has type ‘long int’ 1161 cmd_buf.c:421: warning: field precision should have type ‘int’, but argument 3 has type ‘long int’ 1162 cmd_buf.c: In function ‘cmd_buf_edit’: 1163 cmd_buf.c:548: warning: implicit declaration of function ‘sqsh_exit’ 1164 cmd_buf.c: At top level: 1165 1166sqsh-2.2.0/src/cmd_connect.c 1167 Implementation of feature 5 and enhancement 6 - Implement a SIGINT signal handler and provide 1168 for TDS debugging. Enhancement 18, added -J, -A, -G and -z options to the \connect and 1169 \reconnect commands. 1170 1171sqsh-2.2.0/src/cmd_do.c 1172 Implementation of bug fix 15 - Improve \do usage information. Also a regular ct_close is 1173 performed before a CS_CLOSE_FORCE is attempted. 1174 1175sqsh-2.2.0/src/cmd_for.c 1176 Code fix, removed unused variable exit_status. 1177 cmd_for.c: In function ‘cmd_for’: 1178 cmd_for.c:53: warning: unused variable ‘exit_status’ 1179 1180sqsh-2.2.0/src/cmd_history.c 1181 Improve display of datetime occurrences in history list (\h -i). 1182 Improve usage information string on \hist-save and \hist-load. 1183 Solved some compiler warnings: 1184 cmd_history.c: In function ‘cmd_history’: 1185 cmd_history.c:142: warning: field width should have type ‘int’, but argument 3 has type ‘long int’ 1186 cmd_history.c:142: warning: field precision should have type ‘int’, but argument 4 has type ‘long int’ 1187 cmd_history.c:145: warning: field width should have type ‘int’, but argument 2 has type ‘size_t’ 1188 cmd_history.c:145: warning: field width should have type ‘int’, but argument 4 has type ‘long int’ 1189 cmd_history.c:145: warning: field precision should have type ‘int’, but argument 5 has type ‘long int’ 1190 cmd_history.c: At top level: 1191 1192sqsh-2.2.0/src/cmd_if.c 1193 Solved a compiler warning by removing variable 'ret': 1194 cmd_if.c: In function ‘cmd_if_exec’: 1195 cmd_if.c:183: warning: unused variable ‘ret’ 1196 1197sqsh-2.2.0/src/cmd_input.c 1198 Implementation of feature 3 - semicolon_hack2 to allow multiple semicolons on the same line 1199 of input. 1200 1201sqsh-2.2.0/src/cmd_jobs.c 1202 Solved a compiler warning regarding type casting. 1203 1204sqsh-2.2.0/src/cmd_lock.c 1205 Implemented a fix for the \lock command when libcrypt is used to compare the password 1206 just typed by the user with the stored password in /etc/password or /etc/shadow. 1207 1208sqsh-2.2.0/src/cmd_loop.c 1209 Just a small code fix. 1210 1211sqsh-2.2.0/src/cmd_misc.c 1212 Solved a couple of compiler warnings: 1213 cmd_misc.c: In function ‘cmd_display’: 1214 cmd_misc.c:94: warning: field width should have type ‘int’, but argument 4 has type ‘long int’ 1215 cmd_misc.c:94: warning: field precision should have type ‘int’, but argument 5 has type ‘long int’ 1216 cmd_misc.c: At top level: 1217 1218sqsh-2.2.0/src/cmd_read.c 1219 Implementation of bugfix 16. Assigning a value to a variable using the \read command and reading 1220 from a file using redirection from file to stdin, for example: 1221 \read variable <$HOME/.sqsh/varvalue 1222 1223sqsh-2.2.0/src/cmd_reconnect.c 1224 Save context beside connection info and restore the context in case of connection failure 1225 to the original context. This will leave the existing connection available. 1226 1227sqsh-2.2.0/src/cmd_reset.c 1228 Solved a compiler warning. See also sqsh_readline.h. 1229 cmd_reset.c: In function ‘cmd_clear’: 1230 cmd_reset.c:54: warning: implicit declaration of function ‘_rl_clear_screen’ 1231 cmd_reset.c: At top level: 1232 1233sqsh-2.2.0/src/dsp_conv.c 1234 Solved a compiler warning and a minor bugfix when displaying %u formatted data in 1235 datetime datatypes for milliseconds. 1236 dsp_conv.c: In function ‘dsp_datetime_len’: 1237 dsp_conv.c:357: warning: too many arguments for format 1238 dsp_conv.c: At top level: 1239 1240sqsh-2.2.0/src/dsp_desc.c 1241 Solved two compiler warnings. 1242 dsp_desc.c: In function ‘dsp_desc_fetch’: 1243 dsp_desc.c:501: warning: operation on ‘p’ may be undefined 1244 dsp_desc.c: At top level: 1245 dsp_desc.c:619: warning: ‘dsp_display_fmt’ defined but not used 1246 1247sqsh-2.2.0/src/dsp_x.c 1248 Solved a couple of compiler warnings. 1249 dsp_x.c: In function ‘dsp_x_init’: 1250 dsp_x.c:295: warning: implicit declaration of function ‘sqsh_exit’ 1251 dsp_x.c:298: warning: implicit declaration of function ‘sqsh_expand’ 1252 dsp_x.c:273: warning: unused variable ‘orig_width’ 1253 dsp_x.c:272: warning: unused variable ‘text_width’ 1254 dsp_x.c:268: warning: unused variable ‘s_cancel’ 1255 dsp_x.c:254: warning: unused variable ‘i’ 1256 dsp_x.c: In function ‘dsp_x_input_cb’: 1257 dsp_x.c:443: warning: unused variable ‘number’ 1258 dsp_x.c:442: warning: unused variable ‘cp’ 1259 dsp_x.c:438: warning: unused variable ‘len’ 1260 dsp_x.c: At top level: 1261 1262sqsh-2.2.0/src/sqsh_config.h 1263 Changed version name to sqsh-2.2.0 1264 1265sqsh-2.2.0/src/sqsh_debug.c 1266 Added a new function to test if a debug level is enabled or not. 1267 1268sqsh-2.2.0/src/sqsh_debug.h 1269 Defined debugging levels HISTORY and TDS 1270 1271sqsh-2.2.0/src/sqsh_env.c 1272 Bug fix 13 implementation. Newly created variables will also be logged 1273 when a save-point is defined. These variables will then be deleted when 1274 the save-point is rolled back. Improved debugging information. 1275 1276sqsh-2.2.0/src/sqsh_env.h 1277 Remove unused function declarations. 1278 1279sqsh-2.2.0/src/sqsh_expand.c 1280 When variable ${?} is expanded it will be retrieved from the correct 1281 global internal environment. 1282 1283sqsh-2.2.0/src/sqsh_history.c 1284 Implementation of feature 4 regarding the histmerge option. 1285 Also fixed some compiler warnings. 1286 sqsh_history.c: In function ‘history_save’: 1287 sqsh_history.c:525: warning: implicit declaration of function ‘umask’ 1288 sqsh_history.c: In function ‘hist_auto_save’: 1289 sqsh_history.c:891: warning: implicit declaration of function ‘sqsh_expand’ 1290 sqsh_history.c: At top level: 1291 1292sqsh-2.2.0/src/sqsh_init.c 1293 Predefine the keyword_refresh global internal variable to improve variable 1294 lookup. 1295 Use a normal ct_close and ct_exit before attempting a FORCE close or exit. 1296 With cleanup during exit also destroy the global internal variable environment. 1297 1298sqsh-2.2.0/src/sqsh_job.c 1299 Fixed a problem with error messages generated in conjunction with the use of 1300 redirected file names (<file or >file). See bugfix 16. 1301 Fixed a compiler warning by adding an additional include file: 1302 sqsh_job.c: In function ‘jobset_run’: 1303 sqsh_job.c:437: warning: implicit declaration of function ‘sqsh_exit’ 1304 sqsh_job.c: At top level: 1305 1306sqsh-2.2.0/src/sqsh_main.c 1307 Improved password handling in -P option. Store script name when using -i 1308 in the appropriate place for later retrieval. 1309 1310 sqsh_main.c: In function ‘main’: 1311 sqsh_main.c:526: warning: suggest parentheses around assignment used as truth value 1312 sqsh_main.c:866: warning: control reaches end of non-void function 1313 sqsh_main.c: At top level: 1314 1315sqsh-2.2.0/src/sqsh_readline.c 1316 Implementation of feature 1 to use variable $readline_histignore to specify a number 1317 of keywords that should not be added to the readline history. 1318 Furthermore solved a couple of compiler warnings regarding typecasts in toupper and 1319 tolower functions. 1320 sqsh_readline.c: In function ‘sqsh_readline_init’: 1321 sqsh_readline.c:129: warning: implicit declaration of function ‘sqsh_exit’ 1322 sqsh_readline.c: At top level: 1323 1324sqsh-2.2.0/src/sqsh_readline.h 1325 Function _rl_clear_screen declared. 1326 1327sqsh-2.2.0/src/var_debug.c 1328 Added HISTORY (short HIST) and TDS as new debugging areas which can be specified 1329 with -l or assigned to the $debug variable. Note that sqsh must be build with 1330 DEBUG enabled in the src/Makefile. 1331 1332sqsh-2.2.0/src/var.h 1333 Added new and missing variables to the global general environment. (g_env). 1334 1335 1336 1337 What is new or fixed in version sqsh-2.1.9 1338 1339 New features, enhancements, bug fixes: 1340 13411 - Feature - New -i option to the \bcp command that will enable you to execute 1342 a SQL command on the target server just before the bulk copy 1343 operation starts. This is very useful to do some initialization 1344 on the target server. You could for example truncate the target 1345 table first, or run a stored procedure to change the target database 1346 options. For example: 1347 1348 $ sqsh -SPROD -Uworkshop -Pmy5ecret 1349 1> select * from proddb..materials 1350 2> \bcp -S DTA -i "truncate table testdb..materials" -N testdb..materials 1351 1352 Starting copy... 1353 Batch successfully bulk-copied to SQL Server 1354 1355 18 rows copied. 1356 Clock Time (sec.): Total = 0.1250 Avg = 0.0069 (144.00 rows per sec.) 1357 1358 13592 - Feature - Introduce the variables datefmt and timefmt that control how 1360 SQL server DATE and TIME datatypes are converted and displayed in 1361 the result set. This is similar to the existing datetime datatype 1362 conversion routines. By default these variables are set to NULL 1363 which means no conversion will be done and the CT-Lib functions 1364 will decide how to display the date and time datatype columns. 1365 Note, the variables can be preset in the .sqshrc file. 1366 For example: 1367 1368 1> \set datefmt='%d %B %Y' 1369 1> \set timefmt='%H:%M:%S' 1370 1> \set datetime='%e %b %Y %H:%M[:%S]' 1371 1> select convert(date,getdate()); 1372 ----------------- 1373 04 February 2013 1374 1375 1> select convert(time,getdate()); 1376 -------- 1377 15:20:27 1378 1379 1> select convert(smalldatetime,getdate()) 1380 ---------------- 1381 4 Feb 2013 15:20 1382 1383 13843 - Enhancement - Client-side password encryption is enhanced to be able to 1385 use RSA encryption if the OpenClient and ASE server support it. 1386 In ASE 15.0.2 a new configuration parameter was introduced, 1387 "net password encryption reqd" that can be set to 1 to force 1388 clients to use CT-Lib password encryption of the password that 1389 is send over the network from the client to the server. Plain 1390 passwords are not accepted over the network in that case. 1391 If the configuration parameter is set to 2, then the client must 1392 even perform stronger RSA password encryption. 1393 If the parameter is set to the default value of 0, then no net 1394 password encryption is required and the password will be send in 1395 plain text to the server if you do not request for encryption when 1396 you connect to the server. 1397 Sqsh will now use the highest type of encryption possible 1398 when the variable $encryption is set to On or 1. You can also start 1399 sqsh with the -X option. In sqsh-2.1.9 it is also possible to use 1400 the -X option with the \connect or \reconnect commands. 1401 The -X option was already present on the \bcp command, but is also 1402 enhanced here to use RSA encryption when appropriate. 1403 14044 - Enhancement - The \bcp command is made to work with OpenClient and bulk copy 1405 library version 15.7. 1406 14075 - Bugfix - When the result set is displayed in CSV format and the data contains 1408 a double quote, it is now preceded with an additional double quote. 1409 That makes it possible to read the data into a spreadsheet program 1410 much more easily. 1411 14126 - Bugfix - Prototype declaration of the readline function unstifle_history 1413 changed from "extern void" to "extern int" as some picky 1414 compiler settings may complain about a redeclaration of this 1415 function from /usr/include/readline/history.h. 1416 14177 - Bugfix - Parsing of the SQSH environment variable for sqsh startup options 1418 failed. In particular when options were used that do not require any 1419 arguments. For example: 1420 1421 $ export SQSH="-X -SDTA -b" 1422 $ sqsh -Usa -P 1423 sqsh: Options must begin with '-' 1424 Use: sqsh .... 1425 1426 1427This version of sqsh has been successfully built and tested on Cygwin 32 bits, 1428Solaris x86_64, Linux Ubuntu 32bits, Linux CentOS 5.2 64 bits, AIX 5.3L 64 bits. 1429Also various versions of Readline, Sybase OpenClient (15.7, 15.5) and freetds 1430(0.83, 0.91) have been used to build and test sqsh-2.1.9. Enjoy!!! 1431 1432 1433 Changed source files in sqsh-2.1.9 1434 1435sqsh-2.1.9/src/cmd_bcp.c 1436 Implementation of feature 1 (-i option) and enhancement 3 (RSA password encryption) 1437 and enhancement 4 (bulk copy library version 15.7 awareness) 1438 1439sqsh-2.1.9/src/cmd_connect.c 1440 Enhancement 3 (RSA password encryption) and addition of -X parameter 1441 to the \connect and \reconnect command. 1442 Also some improvements on password_retry when connecting to a Sybase 1443 Replication Server, Sybase IQ Server or MSSQL server are implemented. 1444 1445sqsh-2.1.9/src/dsp.c 1446 Feature 2, datefmt and timefmt implementation. 1447 1448sqsh-2.1.9/src/dsp.h 1449 Feature 2, datefmt and timefmt implementation. 1450 1451sqsh-2.1.9/src/dsp_conv.c 1452 Feature 2, datefmt and timefmt implementation. 1453 1454sqsh-2.1.9/src/dsp_csv.c 1455 Bug fix 5 implementation. 1456 1457sqsh-2.1.9/src/dsp_desc.c 1458 Feature 2, datefmt and timefmt implementation. 1459 1460sqsh-2.1.9/src/Makefile.in 1461 Removed references to files sqsh_ctx.c and sqsh_ctx.h. These are 1462 not being used and will no longer be distributed in the tar ball. 1463 1464sqsh-2.1.9/src/sqsh_config. 1465 Version string changed to sqsh-2.1.9 1466 1467sqsh-2.1.9/src/sqsh_getopt.c 1468 Bug fix 7 implementation. 1469 1470sqsh-2.1.9/src/sqsh_global.c 1471 Copyright string updated. 1472 1473sqsh-2.1.9/src/sqsh_main.c 1474 Environment variable rcfile is set to NULL when sqsh started with -r option 1475 without a sqshrc file specified. 1476 Temporary environment variable cur_rcfile removed when finished with sqshrc 1477 file processing. 1478 1479sqsh-2.1.9/src/sqsh_readline.h 1480 Bugfix 6 implementation. 1481 1482sqsh-2.1.9/src/var.h 1483 For feature 2, the variables datefmt and timefmt are defined here. 1484 All variables that are defined to NULL by default, must have the ability 1485 to be reset back to NULL with \set varname=NULL . 1486 For those variables that did not have a setting/validation function defined, 1487 this function is now set to var_set_nullstr. 1488 Variables that are also stored in specific storage structures (like maxlen, 1489 datefmt, datetime, bcp_colsep, e.a), along with the g_env environment 1490 setting, are set to the same default value. 1491 1492 For example in sqsh-2.1.8: 1493 1494 1> \set | grep maxlen 1495 maxlen=NULL 1496 1> \echo $maxlen 1497 32768 1498 1499 In sqsh-2.1.9: 1500 1501 1> \set | grep maxlen 1502 maxlen=32768 1503 1504sqsh-2.1.9/src/var_dsp.c 1505 Feature 2 (datefmt and timefmt implementation) 1506 1507 1508 1509 What is new or fixed in version sqsh-2.1.8 1510 1511 New features, enhancements, bug fixes: 1512 15131 - Feature - Dynamic load of keyword list for Readline keyword completion. 1514 Until now you were only able to use a fixed list of keywords that were 1515 provided in a file through the "$keyword_file" variable. 1516 Now you can enable the variable "$keyword_dynamic" and provide a query 1517 using the variable "$keyword_query" that will be executed each time you 1518 log in to a server or change database context using the "use <dbname>" 1519 command. This feature will only work in Sybase ASE and MSSQL servers. 1520 1521 The default query provided with "$keyword_query" is: 1522 1523 "select name from sysobjects order by name" 1524 1525 But you can provide your own query, as long as the first column in 1526 the result set returns a character datatype string. For example: 1527 1528 \set keyword_query="\\ 1529 select name from sysobjects \\ 1530 where type in ('U','V','P','S') \\ 1531 union \\ 1532 select name from sybsystemprocs..sysobjects \\ 1533 where type='P' \\ 1534 order by name" 1535 1536 I have used this feature in an environment with more than 5000 objects 1537 loaded in the completion list each time the database context was 1538 changed, and I did not notice any performance degradation while 1539 switching from database to database or while using the Readline TAB 1540 completion feature. 1541 1542 The second part of this feature automatically completes column names 1543 when you type an object name followed by a dot and then use Readline 1544 TAB completion. For example, if you type 1545 1> .... 1546 2> where syslogins.<TAB> 1547 then sqsh will show all columns of syslogins that you can auto-complete 1548 even further by the Readline module. Of course this will only work for 1549 objects in the current database. Furthermore, the object does not have 1550 to exist in the keyword completion list itself. These two feature 1551 parts work independently of each other, as long as keyword_dynamic and 1552 keyword_completion are enabled. 1553 1554 Note 1: Readline auto-completion uses a TAB, or TAB TAB, or sometimes 1555 ESC ESC. It works in sqsh the same way as you use auto-completion of 1556 filenames in your shell. Settings are controlled in ~/.inputrc or global 1557 /etc inputrc settings. See the GNU Readline library documentation for 1558 more info on auto-completion. 1559 1560 Note 2: In sqsh-2.1.8/doc a file sqshrc-2.1.8 is provided that contains 1561 appropriate settings for the newest features in sqsh. Note that the 1562 color prompt settings in this file are optimized for a (xterm) window 1563 with white background color. Copy this file to ~/.sqshrc and make sure 1564 you have a directory ~/.sqsh and you are completely ready to use sqsh 1565 in its full glory. 1566 1567 15682 - Enhancement - Check on running background jobs or pending job output before 1569 exiting sqsh. If you have running jobs in the background that were 1570 started with "go &", in sqsh-2.1.7 an exit or quit of the parent process 1571 would also kill the child processes. Now a check is performed to see if 1572 there are running jobs or finished jobs with pending output. If this is 1573 the case then a message is displayed: 1574 1575 "You have running jobs or pending job output" 1576 1577 In order to exit sqsh anyway, you have to kill your jobs (\kill) 1578 manually and process pending output (\show) first. 1579 1580 15813 - Enhancement - Tilde expansion on command line. 1582 If you are used to use a ~ in shell commands that will be expanded to 1583 your HOME directory name, then you can now use them in sqsh as well. 1584 The tilde will only be expanded in sqsh commands (and their aliases) 1585 like the "\go" command, not in the SQL buffer. 1586 For example: 1587 1588 ... 1589 3> go -m bcp > ~/bcp_format.dat 1590 1591 Note that this will only work if GNU Readline is compiled into sqsh. 1592 1593 15944 - Enhancement - Password retry loop for MSSQL, DCO/ECDA and Sybase Repserver 1595 connections added. 1596 If you tried to logon to any other server than Sybase ASE, IQ or ASA 1597 and you provided the wrong password, then sqsh terminated immediately 1598 with an error message. Now you get the chance to retype the password 1599 again and again until you are successfully logged in, provided that 1600 $password_retry is enabled (and the server does not lock you out after 1601 an x number of login attempts). 1602 1603 16045 - Bug fix - Improved processing in displaying binary or image data and 1605 fixed a bug when querying column sysprocesses.sid in MSSQL, which 1606 resulted in a buffer overflow error. 1607 Bug report ID: 3079678 1608 1609 16106 - Bug fix - Unable to use "-P -" to provide a password on stdin. 1611 Bug report ID: 3388213 1612 16137 - Bug fix - Nested \if statements did not work 1614 1615 \set x='' 1616 \if [ -z ${x} ] 1617 \echo "Level 1: EMPTY -${x}-" 1618 \set x='a' 1619 \if [ -z ${x} ] 1620 \echo "Level 2: EMPTY -${x}-" 1621 \else 1622 \echo "Level 2: NOT EMPTY -${x}-" 1623 \fi 1624 \else 1625 \echo "Level 1: NOT EMPTY -${x}-" 1626 \fi 1627 1628 This script executed in sqsh-2.1.7 would produce the incorrect results: 1629 1630 Level 1: EMPTY -- 1631 Level 2: EMPTY -a- 1632 Level 2: NOT EMPTY -a- 1633 1634 This is now fixed in sqsh-2.1.8 and the correct results are produced now: 1635 1636 Level 1: EMPTY -- 1637 Level 2: NOT EMPTY -a- 1638 1639 1640This version of sqsh has been successfully built and tested on Cygwin 32 bits, 1641Solaris x86_64, Linux Ubuntu 32bits, Linux CentOS 5.2 64 bits, AIX 5.3L 64 bits. 1642Also various versions of Readline, Sybase OpenClient (15.5, 15.0) and freetds 1643(0.83, 0.91) have been used to build and test sqsh-2.1.8. 1644I hope you enjoy and love this new version of sqsh as much as I do. 1645 1646 1647 1648 Changed files in sqsh-2.1.8 1649 1650sqsh-2.1.8/src/cmd_connect.c 1651 Implements feature 1), by setting an internal variable $keyword_refresh 1652 to 1 in case a Msg 5701 is determined, meaning that a change of 1653 database occurred or an initial login. The variables denotes that the 1654 keyword list should be refreshed in the cmd_input loop. 1655 1656 Also implements enhancement 4), by repeatedly asking for a password in 1657 case a wrong password was supplied for connections to MSSQL, Sybase 1658 ECDA/DCO and or Sybase RepServer. Originally this only worked for 1659 connections to Sybase ASE, IQ and ASA. 1660 1661sqsh-2.1.8/src/cmd_exit.c 1662 Implements enhancement 2), by checking if child jobs are running or if 1663 pending job output is waiting for retrieval. If this is the case then a 1664 message is presented: "You have running jobs or pending job output". 1665 This is to prevent unintentionally killing child processes if you exit 1666 the parent sqsh session. If you still want to exit your parent session 1667 you have to kill all your child jobs first. 1668 1669 Query running/finished jobs: \jobs [-i] 1670 Kill running jobs: \kill <jobnr> 1671 Show output: \show <jobnr> > /dev/null 1672 Wait for jobs to finish: \wait ... 1673 1674sqsh-2.1.8/src/cmd_if.c 1675 Implements a fix for bug report 7). 1676 While parsing an \if statement the body of the if and else branch are 1677 copied to a buffer for subsequent body execution, depending on the 1678 evaluation of the \if statement. When a nested \if statement was 1679 discovered, the nestlevel was increased but the \if statement itself not 1680 copied to this buffer. With this fix, the if and else branch will now 1681 contain the nested \if statements and will successfully execute. 1682 Also the evaluationvalue of the \if statement (true or false) will now 1683 be available in the ${?} variable. 1684 1685sqsh-2.1.8/src/cmd_input.c 1686 Implements feature 1), by executing a query to refresh the Readline 1687 auto-completion keyword list when a change of database occurred. This 1688 is triggered by a variable "$keyword_refresh" that will be set in 1689 cmd_connect.c by the callback handler in case a "use <database>" 1690 statement was executed. 1691 The query that will be run is provided in the variable $keyword_query. 1692 The default query is: "select name from sysobjects order by name". But 1693 you can change that in the .sqshrc file for your own convenience to: 1694 1695 \set keyword_query="\\ 1696 select name from sysobjects \\ 1697 where type in ('U','V','P','S') \\ 1698 union \\ 1699 select name from sybsystemprocs..sysobjects \\ 1700 where type='P' \\ 1701 order by name" 1702 1703 for example. If you type a TAB while entering text, Readline will 1704 search the keyword list for possible completions and presents them or 1705 just completes the word you were typing if there is only one possibility 1706 left. 1707 This feature is enabled or disabled by the variable "$keyword_dynamic". 1708 The default is "Off", so you have to explicitly set the variable to "On" 1709 if you want to take advantage of this new feature. Also the variable 1710 "$keyword_completion" needs to be set with a value greater than zero. 1711 1712sqsh-2.1.8/src/dsp_desc.c 1713 Bugfix 5): Improved handling of binary and image datatypes. 1714 Fix bugreport 3079678. 1715 1716sqsh-2.1.8/src/sqsh_buf.c 1717 Some preprocessing code is moved to sqsh_readline.h for better include 1718 file handling. 1719 1720sqsh-2.1.8/src/sqsh_config.h 1721 Defines a default value that will be assigned to $keyword_query. 1722 "select name from sysobjects order by name" 1723 Also sets SQSH_VERSION to the string "sqsh-2.1.8". 1724 1725sqsh-2.1.8/src/sqsh_global.c 1726 Changed copyright string. 1727 1728sqsh-2.1.8/src/sqsh_job.c 1729 Implements enhancement 3), tilde expansion on the command line. If you 1730 type: 1731 1732 1> select name from syslogins 1733 2> go | grep sa_ > ~/tmp/sa_logins.out 1734 1735 for example, the tilde will be replaced with your home directory. You 1736 can use any valid ~name that can be expanded to the corresponding home 1737 directory of the specified login name, for example ~sybase. Note that 1738 other expansions like ~+ for $PWD or ~- for $OLDPWD will not work in 1739 sqsh, as it does in the Bash shell. You will need Readline compiled in 1740 to use this feature (./configure --with-readline ...) 1741 1742sqsh-2.1.8/src/sqsh_main.c 1743 Implemented a fix for passing on passwords through stdin with -P - 1744 Fix for bug 6). 1745 1746sqsh-2.1.8/src/sqsh_readline.c 1747 Implements feature 1), dynamic column name expansions when you type 1748 a TAB (or double TAB) after an object name followed by a dot. 1749 For example type: select sysprocesses.<TAB> ...then sqsh will show you 1750 all the available column names of the sysprocesses table, provided that 1751 you are in the master database of course. So the object must exist in 1752 the current database. This feature will only work if variables 1753 "$keyword_dynamic" and $keyword_completion are enabled. 1754 Also all the reserved keywords in ASE 15.7 that were not already on the 1755 list, are added to the default keyword completion list. This list will 1756 be used if you do not provide your own list through the "$keyword_file" 1757 variable and "$keyword_dynamic" is disabled. Again, you will have to 1758 compile GNU Readline in sqsh to be able to use this feature. 1759 1760sqsh-2.1.8/src/sqsh_readline.h 1761 Moved some of the prototypes and include file definitions from some C 1762 source files to this local include file. 1763 1764sqsh-2.1.8/src/var.h 1765 Replaced the hardcoded sqsh appname by the preprocessor variable 1766 SQSH_VERSION. This variable is defined in sqsh_config.h and set to 1767 the string sqsh-2.1.8. 1768 1769sqsh-2.1.8/src/var_readline.c 1770 Removed some of the prototypes and the inclusion of 1771 <readline/readline.h> here and replaced them with an include of 1772 "sqsh_readline.h". 1773 1774 1775 1776 What is new or fixed in version sqsh-2.1.7 1777 1778 New features: 1779 17801 - Command \exit accepts a return code (Solution to feature request 1954395). 1781 You can now exit sqsh with an exit code. For example: 1782 1783 [1] SYBASE.sa.master 1> quit 5 1784 ~> echo $? 1785 5 1786 1787 This feature uses a new sqsh variable $exit_value (int) which can be 1788 explicitly set using: \set exit_value=n 1789 or you can use \exit x, (and its aliased counterparts like quit and exit). 1790 The value must be of type integer, greater equal 0. (Most shells only 1791 supports exit values up to 255, and it is your own responsibility to use 1792 exit codes your shell can handle.) 1793 When sqsh is going to terminate, it checks the value of $exit_value, 1794 and when non-zero, it uses this value as exit code. Is $exit_value equal 1795 to zero, then sqsh reverts to standard behavior. That is, it will check 1796 variable $exit_failcount and when 1, it will use the value of 1797 $batch_failcount as exit code. So basically $exit_value takes precedence 1798 over $exit_failcount and $batch_failcount. 1799 18002 - A new variable $xwin_title (string) is introduced to assign a name to an 1801 X-windows result set window. (Solution to feature request 1027627). 1802 1803 You can assign a string to the variable $xwin_title using: 1804 1805 \set xwin_title='$DSQUERY result set $histnum' 1806 1807 Note, You can set this as a default name in your .sqshrc file. 1808 There is also a new option -T to the \go and \rpc commands to temporarily 1809 assign another name string to $xwin_title. For example: 1810 1811 [1] SYBASE.sa.master 1> exec sp_who 1812 [1] SYBASE.sa.master 2> \go -x -T"What is running on server $DSQUERY" 1813 1814 The string value will be used to assign a name to the window. Of course the 1815 option -T is only useful in combination with the -x option. After that, the 1816 original $xwin_title value will be restored. 1817 The feature works for Motif widgets as well as for X/Athena widgets. When 1818 you do not set the $xwin_title variable in your sqsh session and do not use 1819 the -T option along with the -x option, then the name of the created result 1820 set window will default to "main". 1821 18222b- When using the -x option with the \rpc command and Motif is used, the rpc 1823 command is now shown in the SQL pane. As a consequence the SQL buffer is 1824 now cleared and not saved in the history when a \rpc command is issued. 1825 18263 - A new variable $term_title (string) is introduced. When set, sqsh will 1827 alter the name of the xterm window when started up. For example: 1828 1829 \set term_title='$DSQUERY ($hostname)' 1830 1831 Sqsh will prepend the title string with its startup name (argv[0] 1832 parameter). For example: "sqsh SYBPROD1 (ux-prd-5)" 1833 This feature is very useful when you have multiple xterm/sqsh sessions 1834 running connected to different servers. It will be easier to identify 1835 your sessions on the Window Manager's task bar. When sqsh exits normally, 1836 the window title is wiped out. The feature also works for rxvt type windows, 1837 Putty and even Microsoft Windows MS-DOS COMMAND.com and CMD.exe windows, 1838 when using a Cygwin build of sqsh. (Do not use $term_title on a console.) 1839 Often the Shell will also set a window title with the connection name and 1840 path. In bash for example, you can set variable PROMPT_COMMAND in the 1841 .bashrc file, like: 1842 1843 PROMPT_COMMAND='echo -ne "\033]0;${USER}@${HOSTNAME}: ${PWD/$HOME/~}\007"' 1844 1845 So when you exit sqsh, the window title might be changed by the shell to 1846 "sybase@ux-prd-5:/opt/sybase" 1847 18484a- The command history will now keep track of the date and time of the last 1849 access of a history buffer. Also the usage count of a buffer is tracked. 1850 The latter is especially useful if you use the sqsh-2.1.6 histunique 1851 feature. You can retrieve the additional information on buffers by 1852 specifying a new parameter -i to the \history command. For example: 1853 18544b- [19] SATURN.sa.master.1> \history -i 1855 ( 1 - 1/ 1 Jan 1970 01:00:00) select host_name() 1856 ( 2 - 1/ 1 Jan 1970 01:00:00) select * from sysloginroles 1857 ... 1858 (16 - 2/ 8 Jan 2010 22:14:41) sp_helpdevice 1859 (17 - 3/ 9 Jan 2010 16:17:33) select convert(varchar(30),name),type,crdate 1860 from sysobjects order by name 1861 (18 - 12/ 9 Jan 2010 17:04:51) sp_who 1862 1863 1864 The first number is the number of the history buffer. The second number is a 1865 count of executions of the buffer. (This is not necessarily true as you can 1866 also \reset a buffer without executing it, but still will be stored in the 1867 history list (see 6)). The third item in the info list is the date and time 1868 of the last buffer access. The remaining part is the content of the buffer 1869 itself. You can influence the display format of the date and time by setting 1870 the variable $datetime to a strftime format string. 1871 1872 [19] SATURN.sa.master.1> \echo $datetime 1873 %e %b %Y %H:%M:%S 1874 [19] SATURN.sa.master.1> select getdate() 1875 [19] SATURN.sa.master.2> go 1876 1877 -------------------- 1878 9 Jan 2010 19:46:56 1879 1880 (1 row affected) 1881 1882 The contents of $datetime also has impact on how datetime columns will be 1883 shown in sqsh. When set to default, sqsh will show datetime columns as: 1884 1885 [19] SATURN.sa.master.1> \set datetime='default' 1886 [19] SATURN.sa.master.1> \echo $datetime 1887 default 1888 [19] SATURN.sa.master.1> select getdate() 1889 [19] SATURN.sa.master.2> go 1890 1891 ------------------- 1892 Jan 9 2010 7:48PM 1893 1894 1895 The extra information on buffer access time and usage count will also be 1896 stored and retrieved in the history files. When you start sqsh-2.1.7 for 1897 the first time using existing history data, the date will be set to 1 jan 1898 1970 and a usage count to 1. When you open a sqsh-2.1.7 format history file 1899 in an older version of sqsh, you will notice an extra line in the buffer 1900 data: 1901 1902 --> History Info (1263053091:12) <-- 1903 1904 for example. The line itself will be interpreted as a SQL comment, so has no 1905 effect on buffer execution and will be saved again upon exit, so no data 1906 is lost and the history can be loaded again in sqsh-2.1.7 as normal. 1907 19084c- There is also a new parameter -x to the \history command to specify a number 1909 of most recent command buffers to show. For example "\history -x 5" will 1910 only show the 5 most recent buffers. An alias defined as 1911 "\alias \hi='\history -i -x20'" will show the 20 most recent buffers with 1912 full info displayed when you execute the \hi command. 1913 19144d- Buffers only containing blanks, newlines, tabs etc, will not be added to 1915 the history list when the buffer is reset (using the \reset command). 1916 19175 - Ignore Ctrl-\ (SIGQUIT) signals to prevent accidental termination of sqsh 1918 with a stack trace when running in interactive mode. 1919 19206 - A new command \clear is implemented as a companion of the \reset command. 1921 Where \reset writes the current buffer to the history list before flushing 1922 the buffer, \clear only empties the buffer, without writing to the 1923 history. Also, when readline is compiled in, the screen is cleared, as ^l 1924 does. The \clear command is also aliased to 'clear'. 1925 19267 - A new read only variable $$ is introduced to show the sqsh host process id. 1927 Use "\echo $$" or "\echo ${$}" to show the process id, which should be the 1928 same as the value of the master.dbo.sysprocesses.hostprocess column for the 1929 current spid when connected to Sybase ASE from a Unix host. I.e.: 1930 1931 select hostprocess from master..sysprocesses where spid=@@spid; 1932 19338 - A new command \hist-save is introduced to save the complete history to the 1934 current $history file, or save it to a new file specified in the 1935 "\hist-save [filename]" command. Also a new command \hist-load will load 1936 a history file and append it to the current list of history buffers: 1937 "hist-load [filename]". If no filename is specified the file in variable 1938 $history will be used to load from. 1939 19409 - A new variable $hist_auto_save (int) is introduced. When the history is 1941 changed more often than the value of this variable, the history is 1942 automatically saved to the current $history file. This prevents that you 1943 potentially lose a lot of history data when your sqsh session crashes 1944 unexpectedly. 1945 194610- The \buf-del command introduced in sqsh-2.1.6 now accepts a range 1947 specification of buffers to delete. For example "\buf-del 1-10" will delete 1948 the first 10 history buffers. No result messages will be written anymore. 1949 Be warned that the buffers may get re-numbered when you delete one or 1950 more buffers from the history. 1951 195211- The $session variable will now be expanded so it may contain variables 1953 itself. E.g.: \set session=$HOME/.sqsh/.session_${DSQUERY} 1954 195512- Improved TDS version request handling when using startup option -G. 1956 Besides the versions 4.0, 4.2, 4.6, 4.9.5 and 5.0, also versions 7.0 1957 and 8.0 are recognized when compiling sqsh with freetds-0.82. When you 1958 request a specific TDS version, the variable $tds_version will be defined 1959 and when that variable exists, sqsh will obtain the actual TDS version in 1960 effect when the connection is established, and put that value back into the 1961 $tds_version variable. So it may be that you request version 4.2, but still 1962 get version 4.0, depending on the client-server capabilities. 1963 Note that sqsh will not perform any input validation on the input of the -G 1964 parameter. So when you specify an invalid value, the default version of 5.0 1965 will be used without any warning. Normally speaking you do not need to 1966 specify a TDS version (Tabular Data Stream, a Sybase and Microsoft protocol 1967 used in client-server communication). The option -G can be useful to test 1968 client-server capabilities, especially when using freetds. 1969 197013- Improved network authentication handling. Introduced a new command \snace 1971 to report the session expiration interval for a network authenticated 1972 session. 1973 Support for delegation (CS_SEC_DELEGATION) security service added. 1974 197514- The \go command now accepts comments on the command line in C-Style 1976 notation /* */ and the ANSI SQL comment syntax: -- 1977 The comment tokens must be enclosed in white space: /*This will not work*/ 1978 /* This is OK */ 1979 The -- comments will ignore everything up to End of Line. 1980 With this new feature it is now possible to run Sybase installation scripts 1981 that use constructions like: go /* } */ 1982 or: go -- } 1983 1984 1985 Bug fixes in sqsh-2.1.7 1986 19871 - Successive \bcp operation may fail because of binding issues in bulk copy 1988 library operations. Solved by using calloc instead of malloc when 1989 allocating buffers for bulkcopy transfer. 1990 (Corresponding bug report: 2920048) 1991 19922 - Fix for bug report 1959260 supplied by Stephen Doherty. When using 1993 '\go -m bcp' a bcp out of a non-null char(1) or varchar() column with 1994 only blank data, should produce a blank in the output, irrespective of the 1995 $bcp_trim setting, in order to prevent subsequent bulk copy in errors. 1996 19973 - Resizing the X windows result set window (go -x) with Motif widgets, only 1998 resized the "Done" button. This is being fixed now with patch 1290313 by 1999 John Griffin. 2000 20014 - When using X/Athena widgets for \go -x, always create scrollbars for the 2002 result windows. (X11 without Motif). 2003 20045 - When using an output file with the -o option, stderr messages where not 2005 captured in the output file but written to the terminal/xterm/tty. 2006 If the output file already existed, it was not truncated when opened. So 2007 it could happen that the output file still contained data from a previous 2008 sqsh run when the output data produced in the last run was less then 2009 that of the first run and thus the file was not totally overwritten. 2010 20116 - Fixed a problem with the password hiding feature. (Solution to feature 2012 request 1577984, bug report 1487118 and patch 2607434). Courtesy of 2013 David Wood. 2014 20157 - Fixed a problem with the default initialization of sqsh variables 2016 $histunique and $ignoreeof. (Corresponding bug report 2871368) 2017 20188 - Switching the variable $bcp_trim from off to on failed. Part of patch 2019 2061950 (thanks to Klaus-Martin Hansche) fixed this issue. 2020 20219 - The usage message of \go did lack a description of the -p option. 2022 This one is added now. 2023 202410- When creating a result set using the vertical display option (\go -m vert) 2025 it could happen that if a non-null column received no data (caused by 2026 data corruption or TDS protocol errors), the next column would be printed 2027 on the same line. If no column data is received, a newline is now always 2028 outputted. 2029 In the same module fixed a minor error: when the column data exceeds the 2030 screen width and has to wrap onto the next line, the indentation alignment 2031 came 1 blank short. Is also fixed now. 2032 203311- In the background jobs area a lot of improvements are implemented making 2034 running background processes (\go &) in sqsh-2.1.7 much more reliable. 2035 2036a) When two or more batch jobs where started in the background, the deferred 2037 output files from already running jobs were removed. For example: 2038 2039 1> dbcc checkdb (order_prod) 2040 2> go & 2041 Job #1 running [349] 2042 1> dbcc checkdb (cust_prod) 2043 2> go & 2044 Job #2 running [763] 2045 2046 When using $defer_bg = 1, a deferred output file will be created for each 2047 individual job. However, a cleanup procedure executed during the startup of 2048 the child process accidentally unlinked output files of already running 2049 brother and sister processes. 2050 2051b) The batch was not saved in the history. So the dbcc commands in the previous 2052 example would not be saved in the history buffers. 2053 2054c) SIGCHLD signals were not always handled by the designated signal handler. 2055 As a consequence, the batches were never reported finished and output could 2056 not be shown with the \show command. 2057 2058d) The batch related command \jobs now accepts an option -i to show additional 2059 information on the jobs. Also input parameters for \show and \wait 2060 are checked for correctness. Erroneous parameters could otherwise produce 2061 floating point exceptions or other unexpected results. 2062 2063 As an example, take a look at the next sqsh-2.1.6 session: 2064 2065 ~> sqsh-2.1.6 -SNEPTUNE 2066 1> \echo "This sqsh version $version is not background job reliable" & 2067 Job #1 running [12472] 2068 1> \jobs 2069 Job #1: 30-Jan-10 13:11:16 (5 secs) 2070 1> \wait 1 2071 \wait: Object does not exist 2072 1> \show 1 2073 \show: Job 1 has not completed 2074 1> \wait -1 2075 Segmentation fault (core dumped) 2076 2077 Although the child process is finished, it is not correctly signaled, so we 2078 do not get a notification that the job is finished. When we try a "\wait 1", 2079 the job cannot be found and "\show 1" claims the job is not finished yet. 2080 When we finally try a "\wait -1" sqsh terminates with a segmentation fault. 2081 The same session in sqsh-2.1.7: 2082 2083 ~> sqsh-2.1.7 -SNEPTUNE 2084 1> \echo "This sqsh version $version is background job reliable" & 2085 Job #1 running [16448] 2086 1> \jobs 2087 Job #1: 30-Jan-10 13:17:59 (1 secs) 2088 Job #1 complete (output pending) 2089 1> \wait 1 2090 Job #1 complete (output pending) 2091 1> \wait -1 2092 \wait: No jobs pending 2093 1> \show 1 2094 This sqsh version sqsh-2.1.7 is background job reliable 2095 209612- When sqsh is started with additional parameters to be passed on to the 2097 -i input script, parameters ${#} and ${*} were not properly set and 2098 references to ${0} in interactive mode produced segmentation faults. 2099 Also the $* parameter which should produce a list of all parameters was 2100 not correctly displayed. For example, the contents of an input script named 2101 example.sqsh: 2102 2103 \echo $# 2104 \echo $* 2105 \echo ${0} ${1} ${2} ${3} ${4} 2106 2107 sqsh -SDBPRD1 -Usa -i example.sqsh a b -Psecret c 2108 2109 Should now correctly produce: 2110 2111 3 2112 a b c 2113 sqsh a b c 2114 211513- When executing a SQL command with \go -x while no X11 support was compiled 2116 into sqsh, the result buffer of the query would not be canceled. This led to 2117 errors in subsequent batches. Fixed by returning a DSP_FAIL instead of 2118 DSP_SUCCEED after the error message was printed. 2119 212014- When specifying a new cmdend alias for the \go command, the alias was just 2121 defined in the wrong direction. For example "sqsh ... -cGO" would define an 2122 alias \go for the GO command. So when you entered GO, nothing happened as it 2123 was not recognized as a sqsh command. When executing \go, an error was 2124 generated because sqsh now tried to find the internal command GO which does 2125 not exist. The definition of the alias must be reversed, i.e. GO is an alias 2126 for the \go command, so when you enter GO, sqsh will execute \go. 2127 212815- When using sqsh build for Motif and you run a query with "\go -x", sqsh 2129 would create a X window with an unlimited size for the SQL batch section. 2130 If you have a large batch, this could eat up all the windows space and no 2131 space left to show the results. 2132 The SQL batch window is now limited to 10 rows and you have to scroll the 2133 window section to see the rest of the batch. 2134 213516- When a connection is killed or a server performs a shutdown, the connection 2136 to the server is lost. This will eventually be detected by sqsh and a client 2137 callback handler will call sqsh_exit. However, the callback handler did not 2138 return a CS_FAIL or CS_SUCCEED, so CS/CT-lib thinks the callback handler 2139 is still active and produces a warning message that the client did not 2140 exited from CT-lib, when the context is dropped. 2141 The callback handler will now return value 254 (abort) and sqsh_exit will 2142 not try to drop the CS/CT-lib context in this case. 2143 Also in very rare circumstances sqsh_exit could be called recursively, 2144 resulting in a segment violation as pointers to destroyed memory structures 2145 were not nullified. 2146 214717- Implemented fix 1289922 by John Griffin to prevent unexpected sqsh shutdown 2148 on Solaris when child process terminates. 2149 2150 2151 2152 Changed files in sqsh-2.1.7 2153 2154(Note, the Fx refers to the implemented feature and Bx refers to the bug fix.) 2155 2156alias.h 2157 F6: Defined alias 'clear' for the \clear command. 2158 2159cmd.h 2160 F6: Added definition of CMD_RESETBUF and assigned this to \reset 2161 Existing CMD_CLEARBUF action now assigned to \clear 2162 Commands \\clear and \\hist-save defined. 2163 F13: Command \\snace added to Show Network Authenticated Credential 2164 Expiration interval when using Kerberos. 2165 2166cmd_bcp.c 2167 Improved signaling by saving current signaling context when 2168 defining SIGINT interrupt handler. Restoring afterwards. 2169 When compiling sqsh with freetds-0.82, only BLK_VERSION_110 2170 can be used. 2171 CMD_CLEARBUF renamed to CMD_RESETBUF. 2172 Fix for B1 implemented. 2173 2174cmd_buf.c 2175 Implementation for F10 (\buf-del range specification) 2176 CMD_CLEARBUF renamed to CMD_RESETBUF. 2177 2178cmd_connect.c 2179 F11. Expand variable $session so that $session may contains variables 2180 in its definition itself. E.g. 2181 \set session=$HOME/.sqsh/.session_$DSQUERY 2182 F12. Obtain effective tds_version setting when requesting for a specific 2183 TDS version. Add version 7.0 and 8.0 of TDS when using freetds. 2184 F13. Kerberos support improvements; \snace command implementation. 2185 B16. Return 254 in case of a lost connection. 2186 2187cmd_do.c 2188 CMD_CLEARBUF renamed to CMD_RESETBUF. 2189 2190cmd_exit.c 2191 F1. Specify a return code to the \exit command. 2192 2193cmd_for.c 2194 CMD_CLEARBUF renamed to CMD_RESETBUF. 2195 Error message redefined. 2196 2197cmd_func.c 2198 CMD_CLEARBUF renamed to CMD_RESETBUF. 2199 Error message redefined. 2200 2201cmd_go.c 2202 F2. Implemented option -T to specify an $xwin_title string. 2203 B9. Added description of -p (statistics option) in help info. 2204 CMD_CLEARBUF renamed to CMD_RESETBUF. 2205 F14. Filter out comment arguments from the command line. 2206 2207cmd_history.c 2208 F4. Extended buffer info (date/usage count) and most recent x 2209 buffers to show (\history -i -x options) 2210 F8. Added implementation of the \hist-load and \hist-save commands. 2211 2212cmd_if.c 2213 Signaling improvements. 2214 2215cmd_input.c 2216 Signaling improvements. 2217 CMD_CLEARBUF renamed to CMD_RESETBUF. 2218 F6. Implemented action for \clear command. 2219 B11. Write the SQL buffer to history too when batch is run in 2220 background. 2221 2222cmd_jobs.c 2223 B11. Implemented \jobs -i option for additional job information. 2224 2225cmd_kill.c 2226 B11. Improved parameter verification to \kill command. 2227 2228cmd_reset.c 2229 F6. Implementation of new \clear command. 2230 CMD_CLEARBUF renamed to CMD_RESETBUF. 2231 2232cmd_rpc.c 2233 F2. Implementation of -T option to specify an XWin title to the \rpc 2234 command. Changed some of the error messages. (Changed \go to \rpc). 2235 When a Motif X-Windows result set is generated, the rpc command is 2236 also shown in the SQL pane. The cmd_rpc function returns CMD_CLEARBUF 2237 to clear and disgard the current SQL buffer. The previous versions 2238 returned CMD_RESETBUF to add the SQL buffer to the history. However, 2239 sqsh now 'abuses' the SQL buffer to pass on the \rpc command to the 2240 Motif window. 2241 2242cmd_show.c 2243 B11. Improved parameter verification to the \show command. 2244 2245cmd_wait.c 2246 B11. Improved parameter verification to \wait command. 2247 Pass correct job number to jobset_get_defer function when 2248 \wait -1 was used. 2249 2250cmd_while.c 2251 CMD_CLEARBUF renamed to CMD_RESETBUF. 2252 2253dsp_bcp.c 2254 B2. Fix provided by Stephen Doherty. 2255 2256dsp_vert.c 2257 Fixes for B10. 2258 2259dsp_x.c 2260 Solution to B3, B4, B13 and B15 and implementation of F2. 2261 2262sqsh_buf.c 2263 F10. Implementation of \buf-del range deletes. 2264 2265sqsh_config.h 2266 Renumbered version from sqsh-2.1.6 to sqsh-2.1.7 2267 2268sqsh_error.h 2269 Fixed a coding error with values of SQSH_E_RANGE and SQSH_E_BADSTATE. 2270 Values were out of sync with corresponding message numbers in 2271 error message table sg_sqsh_errtable. Fortunately due to the way 2272 the messages were used, would probably never lead to a real problem. 2273 2274sqsh_expand.c 2275 B12. Fix for expanding variable $* in sqsh functions or interactively 2276 accessing ${0} resulting in segmentation fault (\echo ${0}). 2277 F7. Added expanding variable $$ to procid (${$}). 2278 Enabled patch from Michael Chepelev to prevent core dumps 2279 when referring to variables with special characters like $. 2280 2281sqsh_fd.c 2282 Improve SIGCHLD signaling by making sure the correct signal 2283 handler is activated. 2284 2285sqsh_fork.c 2286 Fix for B11. 2287 2288sqsh_global.c 2289 Initialize a new global C variable g_interactive to determine if 2290 sqsh is initially started with stdin and stdout connected to a tty. 2291 Default value is False. 2292 Adapted the copyright message to include 2010. 2293 2294sqsh_global.h 2295 Defined the global variable g_interactive. 2296 2297sqsh_history.c 2298 F4, F8 and F9. Implemented feature additional buffer info, and history 2299 automatic save feature (hist_auto_save). 2300 F4b. Do not save buffers to history with only IFS characters 2301 ("\f\n\r\t\v "). 2302 F10. Implement buffer range deletes. 2303 2304sqsh_history.h 2305 F4. Define extra structure members hb_dttm and hb_count (hisbuf_t) 2306 to keep track of additional buffer info. Structure member h_change 2307 in history_t. 2308 F8. Define status values HISTSAVE_INIT, HISTSAVE_LOAD and HISTSAVE_FORCE 2309 to implement hist_auto_save. 2310 F10. Define function history_range_del. 2311 2312sqsh_init.c 2313 F3. Reset windows title ($term_title) upon exit. 2314 Use g_interactive to determine if history must be saved and readline 2315 functions cleaned up. 2316 B16: prevent segmentation violations with recursive calls to sqsh_exit 2317 by nullifying pointers to destroyed memory structures. Always call 2318 ct_close even if the connection is dead. 2319 Do not call cs_ctx_drop to drop the context when a connection was lost 2320 due to a server shutdown, a killed session or network issues. This 2321 prevents message: 2322 CS-Library error: severity(1) layer(2) origin(1) number(31) 2323 cs_ctx_drop: cslib user api layer: external error: 2324 The context structure cannot be dropped because the application has 2325 not exited from ct. 2326 2327sqsh_job.c 2328 Logical fix in memory cleanup. 2329 B11. Improve child SIGCHLD signaling of background jobs. 2330 Improved \wait processing in case of missed SIGCHLD signals. 2331 Also fixed an error in finding a job in a hash table for the "\wait id". 2332 command (Error in hash bucket calculation), and fixed a segmentation 2333 fault when using the "\wait -1" command. 2334 2335sqsh_main.c 2336 F1. Implement feature \exit x ($exit_value). 2337 F3. Implement feature term_title. 2338 B6. Fix for password hiding feature using flexible named pipes. 2339 B5. Fix for -o output file: Truncate existing file and redirect stderr. 2340 B12. Fix error in parameter count passed on when -i is used 2341 Improve determination of an interactive session, i.e. stdin and stdout 2342 connected to a tty and what to do with history and readline in non- 2343 interactive mode. 2344 F5. Ignore SIGQUIT signals in interactive mode. 2345 B14. Fix handling of -c cmdend aliases for the \go command. 2346 F13. Default value of 'u' assigned to $secure_options if -V did not 2347 specify any options. Also improved help info. 2348 2349sqsh_sigcld.c 2350 B11. Improved SIGCHLD signaling of background jobs. 2351 2352sqsh_stdin.c 2353 B17. Fix possible unexpected shutdown of sqsh when a child process 2354 signals SIGCLD and sqsh is built without readline support. Only 2355 reported a problem on Solaris (Solaris 2.8 SPARC platform ?). 2356 2357var.h 2358 Variable $appname changed from sqsh-2.1.6 to sqsh-2.1.7 2359 B7. $histunique and $ignoreeof initialized to 0 instead of 'Off'. 2360 F1, F2, F3, F9. New variables $exit_value, $xwin_title, $term_title 2361 and $hist_auto_save defined. 2362 2363var_dsp.c 2364 B8. Fix problem when switching variable $bcp_trim from Off to On. 2365 2366 Notes 2367 23681) If you have background jobs running and you issue a Ctrl-C in your current 2369running foreground task, to interrupt processing, be aware that the SIGINT 2370signal is also sent to the child processes, and that they will terminate 2371as well. Although the child process resets all its signals with sig_restore, 2372and asks to ignore SIGINT interrupts at interrupt stack level 0 during startup, 2373the process will still setup (a) SIGINT handler(s) itself, before actively 2374running a SQL batch, just as the parent does. So when a SIGINT interrupt 2375arrives, the child will respond accordingly, which means stop processing in 237699% of the cases. So be careful with Ctrl-C when running background jobs. 2377 2378If you have multiple background jobs running and perform a "\wait <job_id>" on 2379a specific job, you may have to do a specific "\wait <job_id>" or multiple 2380"\wait -1" commands as well to get signaling information for the other 2381processes, if they have finished during the time period of the first executed 2382\wait. The explicit wait for a specific job/pid will temporarily disable the 2383signal handler for SIGCHLD events. If other child processes terminate during 2384this period, their signals will be suspended, until they are picked up by a 2385waitpid which will be issued by the \wait command. 2386 2387 23882) Sessions that uses LDAP or Kerberos user authentication should be able to 2389use background jobs. During testing of Kerberos connections, I came across the 2390situation that the name of the security mechanism in the libtcl.cfg file was 2391specified in uppercase "CSFKRB5" while the name of the secmech in the 2392$SYBASE/config/objectid.dat file is "csfkrb5". 2393When you just specify -Z or -Zdefault to setup a secure connection, you leave 2394it up to OpenClient to select the appropriate secmech. So OpenClient will pick 2395the first available entry, "CSFKRB5". 2396During session establishment sqsh request the real name of the security 2397mechanism being used, and that one is the secmech name from the objectid.dat 2398file. That name is put in the sqsh variable $secmech. 2399So the real secmech is stored in $secmech, and that is also the variable used 2400for the security mechanism specified during \reconnect. When this secmech name 2401is different from the libtcl.cfg security entry, it cannot be found during 2402\reconnect, and the login fails. Using the same name with the same case in 2403the libtcl.cfg file for your desired security mechanism as in objectid.dat 2404(csfkrb5) will solve this issue. 2405 2406 24073) Connections that use SSL/TLS should also be able to use background jobs. 2408When a login trigger checks if a login is already connected and does not allow 2409multiple connections for the same login, for example, then the background job 2410feature will not work. 2411 2412 24134) Build and run tests: 2414- Sqsh-2.1.7 successfully built and tested on Windows-XP with Cygwin 1.7.1-1 2415 and Sybase OpenClient 15.0 EBF17012 ESD #19. (32bit) 2416- On the same platform sqsh-2.1.7 also successfully builds and runs using 2417 freetds-0.82 instead of Sybase OpenClient. 2418 Note, do not use \bcp operations with freetds as unexpected results 2419 and even database corruption may occur. Use the sqsh "go -m bcp" option to 2420 create a bulkcopy file and use the freebcp utility to bulk copy in, instead. 2421 Explained in one of the next notes. 2422- Fedora Core 6, using Sybase OpenClient 15.0 EBF15821 ESD #13. 2423- Fedora Core 6, using freetds-0.82. 2424- Ubuntu 8.04 LTS, using Sybase OpenClient 15.0 EBF16297 ESD #16. 2425- Ubuntu 8.04 LTS, using Sybase OpenClient: Sybase CTISQL Utility/15.5/P/ 2426 DRV.15.5.0/Linux Intel/Linux 2.6.9-55.ELsmp x86_64/BUILD1550-003/OPT 2427- Solaris 10 x86 64bit, using 32 and 64 bit compiled sqsh versions with Sybase 2428 OpenClient 12.5.1/P-EBF13168 ESD #12 2429- Solaris 10 x86 64 bit, using 64 bit compiled sqsh with Sybase OC 2430 Sybase CTISQL Utility/15.5/P/DRV.15.5.0/i386/Solaris 10/BUILD1550-003/OPT/ 2431 2432 24335) sqsh-2.1.7 was successfully tested connecting to Sybase ASE 15.5, 15.0.x, 2434IQ 12.7, IQ 15.1, RepServer 15.2 all on Windows XP and Linux, 32 bits. 2435The freetds version also successfully connects to Microsoft SQL server 2005. 2436The sqsh -X option or $encryption=On variable setting will not work with Sybase 2437IQ. 2438 2439 24406) I found out during code inspections that there is an undocumented variable 2441${$?}. The variable contains the result of the last executed \if statement. 2442The \if statement would be run as a child process running the 'test' command. 2443The result can be 0 for False, 1 for True or 2 means some error occurred 2444(test expression syntax error for example). I wonder if the usage of the "$?" 2445parameter name was chosen on purpose or was a small coding mistake and was 2446meant to be just "?". Well it does not hurt to keep it this way, as long as you 2447are aware that conditional statements do not set variable "?" as a normal shell 2448would. 2449 2450 24517) Be aware that freetds does not support all datatypes Sybase ASE supports, 2452like the DATE and TIME datatypes (4 byte size). When you have a table with a 2453DATE or TIME column and query the table using freetds, the data will be 2454presented as a datetime datatype (8 bytes). So consider the next example: 2455 24561> create table freetds (id INT NOT NULL, insdate DATE NOT NULL) 24572> go 24581> insert freetds values (1, getdate()) 24592> go 2460(1 row affected) 24611> select * from freetds 24622> go 2463 id insdate 2464 ----------- -------------------- 2465 1 31 Jan 2010 00:00:00 2466 2467(1 row affected) 2468 2469When reconnecting with a Sybase OCS capable version of sqsh the query result is: 2470 24711> select * from freetds 24722> go 2473 id insdate 2474 ----------- ----------- 2475 1 Jan 31 2010 2476 2477(1 row affected) 2478 2479When you select the data using the bcp output format and write it to a file 2480(using the freetds version of sqsh again): 2481 24821> select * from freetds 24832> go -m bcp > freetds.dat 2484 2485The contents of the file are: "1#31 Jan 2010 00:00:00". (The # is the column 2486separator.) When you try to bcp this data back into the table using freetds, 2487it assumes the datatype is a 8 bytes datetime datatype. However, the real 2488datatype is a 4 byte DATE datatype. When you are lucky, the server will 2489detect the error, but it may lead to data corruption as well when loading 2490larger blocks of data, especially when using the \bcp command directly. 2491 2492~> freebcp tempdb..freetds in freetds.dat -SNEPTUNE -Usa -P... -t"#" -r"\n" -c 2493 2494Starting copy... 2495Msg 4837, Level 16, State 1 2496Server 'NEPTUNE', Line 1 2497 Bad row data received from the client while bulk copying into object 2498 813176728 partition 813176728 in database 2. Received a row of length 2499 14 whilst maximum or expected row length is 10. 2500 2501Msg 4837, Level 16 2502General SQL Server error: Check messages from the SQL Server 2503 2504Msg 3621, Level 10, State 0 2505Server 'NEPTUNE', Line 1 2506 Command has been aborted. 2507 2508bcp copy in failed 2509 2510MSSQL 2005 only supports DATETIME and SMALLDATETIME datatypes. When using these 2511datatypes there should be no problem in using \bcp. 2512 2513 25148) Job runtime issues of background jobs. When you run a job in the background 2515its starttime is stored in the job memory structure. Besides a list of job 2516structures, sqsh maintains a list of running child pids. When you execute \jobs 2517to see which jobs exists, the job runtime is calculated from the current 2518system time. However, when the child process finishes, it is registered as 2519complete by the signal handler in the pid list only. The job structure is not 2520modified immediately. It is the read-eval-print loop of sqsh that polls the 2521status of running jobs, and when found that a child process is finished, the 2522corresponding job is marked complete and its end time marked. This means if you 2523do not press enter on the sqsh command line regularly, the waittime accumulates 2524in the total job run time. To solve this, the signal handler should keep track 2525of the process end time, that can then be used by the job handler. 2526Basically, if \jobs report a job (done - x secs), the time is the difference 2527of the job start time and the job completion notification, not the real run 2528time of the background process. 2529 2530 2531 2532 What is new in version sqsh 2.1.6. 2533 25341) Color support for prompt and text, using color specification 2535 in the definition of the prompt and prompt2 variables. 2536 2537 Color codes are presented in a string such as: {1;2;3}. If sqsh encounters a { 2538 in the prompt string it will assume a colorcode is supplied and will act 2539 as such. No checks will be performed on the validity of the colorcode 2540 definition itself. The color definition consists of three values separated by 2541 a semicolon. 2542 The first code defines the Color Attribute Code with possible values: 2543 00=none 01=bold 2544 The second value defines the Text Color Code: 2545 30=black 31=red 32=green 33=yellow 34=blue 35=magenta 36=cyan 37=white 2546 The third value defines the Background Color Code: 2547 40=black 41=red 42=green 43=yellow 44=blue 45=magenta 46=cyan 47=white 2548 The last colorcode for the background may be ommitted. 2549 2550 Not all color and attribute combinations will present good results, depending 2551 on your terminal type and color scheme. But the following values work right 2552 for me on a xterm with a creamy white background color in Linux as well as 2553 rxvt in Cygwin with a black background window: 2554 Prompt: Blue text in white background "{0;34;47}" 2555 Command text: Yellow text "{0;33}" 2556 2557 sqsh will translate the colorcodes to an actual colorcode string that 2558 is presented to readline: for example "\001\033[0;36;47m\002" 2559 Note that if you want to use curly brackets in your prompt, you have to 2560 escape colorization by specifying a double brace, like {{...}}. For example: 2561 2562 \set prompt_color='{0;34;47}' 2563 \set text_color='{0;33}' 2564 \set prompt='$prompt_color{{$DSQUERY.$username.$database.$lineno}}>$text_color ' 2565 2566 Note, colorsupport is only available if sqsh is compiled and linked with readline 2567 support enabled (./configure --with-readline). 2568 25692) History unique support using the histunique boolean variable. Can be set in 2570 the sqshrc file or on the fly with the \set command. When turned on, sqsh 2571 maintains a MRU-LRU order of executed buffers and does not store duplicate 2572 command buffers. For example, observe the following situation: 2573 2574 LINUX1502.user1.master.1> \h 2575 (1) sp_who 2576 (2) grant role mon_role to sa_role 2577 (3) select * from monProcessActivity 2578 (4) select @@authmech,show_sec_services() 2579 (5) select @@servername,@@authmech,show_sec_services() 2580 LINUX1502.user1.master.1> sp_who 2581 LINUX1502.user1.master.2> go 2582 ... output ommitted 2583 LINUX1502.user1.master.1> \h 2584 (1) grant role mon_role to sa_role 2585 (2) select * from monProcessActivity 2586 (3) select @@authmech,show_sec_services() 2587 (4) select @@servername,@@authmech,show_sec_services() 2588 (5) sp_who 2589 2590 sp_who is the last executed command and the buffer - originally the last in the list - 2591 is now on top of the list. 2592 2593 Equalness of buffers is determined by means of a checksum calculated on the contents 2594 of the buffer. If you change only one byte (add or remove a blank for example), the 2595 new buffer compared to the old buffer will be considered different. 2596 25973) Addition of \buf-del command to remove history buffers from the 2598 history list, because you do not want them to be stored on disk 2599 when you exit sqsh, or if the buffers are taking up unnessecary 2600 space when you are developing and constantly changing some SQL 2601 statement. For example: 2602 2603 ASE1502.sa.master.1> \history 2604 (1) exec sp_password myPforSA,NewPforU, user1 2605 (2) sp_displaylogin user1 2606 (3) .... 2607 2608 If you want buffer 1 removed, you can type: 2609 2610 ASE1502.sa.master.1> \buf-del 1 2611 Buffer 1 destroyed and list renumbered 2612 26134) Variable ignoreeof determines what happens when ^d is typed on an empty line 2614 when readline support is enabled. By default, sqsh is terminated. When the 2615 option is turned on (using the sqshrc file), a message is shown when you type 2616 CTRL-D: Use "exit" or "quit" to leave the sqsh shell. 2617 This is quite similar to setting "set -o ignoreeof" in the bash shell. 2618 26195) Login and query timeout values can be specified using the variables login_timeout 2620 and query_timeout versus the command line parameters -T and -Q. These are equivalent 2621 to the isql options -l for login timeout and -t for query timeout. By default the 2622 variables are defined NULL, meaning the old behaviour of sqsh. But you can specify 2623 a default value for login timeout of 60 seconds in sqshrc to get identical behaviour 2624 as isql provides: \set login_timeout=60 2625 2626 For example trying to connect to a server that is not started: 2627 2628 sqsh -SSYBIQ -UDBA -PSQL -T5 2629 Open Client Message 2630 Layer 1, Origin 2, Severity 2, Number 63 2631 ct_connect(): user api layer: internal Client Library error: Read from the server has timed out. 2632 SYBIQ: Login timeout detected, aborting connection attempt 2633 2634 Running a query that results in a table scan and does not return 2635 within 30 seconds: 2636 2637 sqsh -SASE1502 -Usa -P -Q30 2638 ASE1502.sa.master.1> cd tix 2639 ASE1502.sa.tix.1> select count(*) from E_TIX; 2640 Open Client Message 2641 Layer 1, Origin 2, Severity 2, Number 63 2642 ct_results(): user api layer: internal Client Library error: Read from the server has timed out. 2643 ASE1502: Query or command timeout detected, command/batch cancelled 2644 ASE1502: The client connection has detected this 1 time(s). 2645 2646 2647 You can also set an integer variable "max_timeout". When the number of timeouts exceeds the threshold 2648 specified by max_timeout, then sqsh will be aborted: 2649 2650 ASE1502.sa.tix.1> \set max_timeout=2 2651 ASE1502.sa.tix.1> !! 2652 ASE1502.sa.tix.1> select count(*) from E_TIX 2653 ASE1502.sa.tix.2> go 2654 Open Client Message 2655 Layer 1, Origin 2, Severity 2, Number 63 2656 ct_results(): user api layer: internal Client Library error: Read from the server has timed out. 2657 ASE1502: Query or command timeout detected, session aborted 2658 ASE1502: The client connection has detected this 2 time(s). 2659 ASE1502: Aborting on max_timeout limit 2 2660 Open Client Message 2661 Layer 2, Origin 1, Severity 1, Number 31 2662 cs_ctx_drop: cslib user api layer: external error: The context structure cannot be dropped 2663 because the application has not exited from ct. 2664 2665 26666) A couple of sqsh variables specifying a filename or directory are now properly expanded so you can 2667 use environment variables in their definition in the sqshrc file. 2668 The variables are: keyword_file, readline_history, tmp_dir and interfaces 2669 For example, when in your sqshrc file the following lines occur: 2670 \set readline_histsize=150 2671 \set readline_history='$HOME/.sqsh/sqshread_$DSQUERY' 2672 then sqsh will create a new readline history file for each server you connect to using "sqsh -S..." 2673 and will reuse that file the next time you start sqsh and you can still retrieve lines entered 2674 from a previous session, or search backwards using the readline ^R feature (in emacs mode), etc. 2675 26767) Advanced Network authentication support 2677 In version 2.1.5 experimental Kerberos support was added using the -K and -R options. -K was merely a switch 2678 to set Kerberos on. In sqsh 2.1.6 a more advanced implementation of network authentication is introduced, 2679 although still experimental. 2680 By using the parameters -K, -R, -V, -Z you can make use of your defined network security settings. (libtcl.cfg). 2681 The named options are identical to the ones defined for isql. 2682 -K keytab_file name for DCE 2683 -R server principal name when Servername specified in interfaces differs from the real servername 2684 -V specify security options to use (cimorq) 2685 -Z requested security mechanism name defined for Kerberos, DCE or PAM in your libtcl.cfg file. 2686 (So, the original -K option in 2.1.5 now specifies a keytab file for DCE in 2.1.6) 2687 For example, connecting to a remote server using Kerberos (which happens to be the default, i.e. first entry 2688 in libtcl.cfg [SECURITY] tab in this example): 2689 2690 ~> sqsh -SLINUX1502 -Uuser1 -RFC6A1502 -Z 2691 LINUX1502.user1.master.1> select @@servername,@@authmech,show_sec_services(); 2692 ---------------- ----------- ------------------------------------------------------------------------ 2693 FC6A1502 kerberos unifiedlogin mutualauth integrity confidentiality detectreplay detectseq 2694 2695 See chapter 16 "External Authentication" from the Sybase System Administration Guide volume 1 for more 2696 information. 2697 2698 [user1@linux-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -Zcsfkrb5 2699 Open Client Message 2700 Layer 7, Origin 9, Severity 5, Number 1 2701 ct_connect(): security service layer: internal security control layer error: 2702 Security service provider internal error -1765328352 occurred. 2703 [user1@user1-fc6a ~]$ kinit 2704 Password for user1@LOCALDOMAIN: 2705 [user1@user1-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -Zcsfkrb5 2706 FC6A1502.user1.master.1> select program_name from sysprocesses where spid=@@spid; 2707 program_name 2708 ------------------------------ 2709 sqsh-2.1.6 2710 2711 (1 row affected) 2712 2713 Note that you can specify -V and or -Z without having to specify a value. Is the value empty for -Z 2714 or contains the value 'default' then the default will be used, which is the first active security driver 2715 in the libtcl.cfg file. 2716 When the connection succeeds, sqsh will store the real name of the security mechanism in the variable 2717 secmech. For example: "\echo $secmech" may show csfkrb5. The option -V takes a list of characters from the 2718 possible values of 'cimorq'. If you do not want to set a security service protocol then you have to specify 2719 -Vx for example. 'x' is not an existing option related to a security service and as a value is provided, 2720 sqsh will not fall back to the default of all options. When -V is empty however, then all possibly supported 2721 security protocols for the selected secmech will be enabled. The key letters are: 2722 2723 c - Enable data confidentiality service 2724 i - Enable data integrity service 2725 m - Enable mutual authentication for connection establishment 2726 o - Enable data origin stamping service 2727 q - Enable out-of-sequence detection 2728 r - Enable data replay detection 2729 2730 If you specify -V and/or -Z, sqsh will assume network authentication is tried and no password is required. 2731 If you have a network authenticated connection and want to \reconnect using normal ASE authentication with 2732 username and password, you have to reset the network authentication variables by specifying -Znone 2733 2734 For example: 2735 [user1@linux-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -Z 2736 FC6A1502.user1.master.1> \echo $secmech 2737 csfkrb5 2738 FC6A1502.user1.master.1> \reconnect -SASE1502 -Usa -P 2739 Open Client Message 2740 Layer 7, Origin 9, Severity 5, Number 8 2741 ct_connect(): security service layer: internal security control layer error: 2742 Consistency checks performed on the credential failed (minor status 0). 2743 FC6A1502.user1.master.1> \reconnect -SASE1502 -Usa -P -Znone 2744 ASE1502.sa.master.1> 2745 2746 The first \reconnect fails because sqsh still wants to try network authentication. However, no user principal 2747 for 'sa' exists or no ticket is set and the connection fails. The second \reconnect succeeds as the -Znone 2748 reset appropriate variables. 2749 2750Variables and the sqshrc file: 2751 2752There are a couple of new environment variables defined, which can be set in the sqshrc file or on 2753the fly on the command line using the \set command, although changing a value will actually take effect during 2754a reconnect. Only histunique and ignoreeof can be activated or deactivated immediately. 2755 2756Variable name Default value Command line equivalent 2757------------- ------------- ----------------------- 2758appname sqsh-2.1.6 -N (introduced in 2.1.5) 2759histunique Off 2760ignoreeof Off 2761login_timeout NULL -T 2762query_timeout NULL -Q 2763max_timeout NULL 2764keytab_file NULL -K 2765principal NULL -R 2766secmech NULL -Z 2767secure_options NULL -V 2768 2769The default value for appname is set to sqsh-2.1.6 so you can distinguish from users using an older version 2770of sqsh. (See also one of the previous examples where we selected the program_name from sysprocesses. 2771In previous version of sqsh you could set the TDS version using the -V parameter. This parameter is now being 2772used for Network Authentication. You can now use parameter -G to set the TDS version. 2773 2774Use: sqsh [-a count] [-A packet_size] [-b] [-B] [-c [cmdend]] [-C sql] 2775 [-d severity] [-D database] [-e] [-E editor] [-f severity] 2776 [-G TDS version] [-h] [-H hostname] [-i filename] [-I interfaces] 2777 [-J charset] [-k keywords] [-K keytab] [-l level] [-L var=value] 2778 [-m mode] [-n {on|off}] [-N appname] [-o filename] [-p] 2779 [-P [password]] [-Q query_timeout] [-r [sqshrc]] [-R principal] 2780 [-s colsep] [-S server] [-t [filter]] [-T login_timeout] 2781 [-U username] [-v] [-V [cimorq]] [-w width] [-X] [-y dir] 2782 [-z language] [-Z secmech] 2783 2784 -a Max. # of errors before abort -m Set display mode (normal) 2785 -A Adjust TDS packet size -n Set chained transaction mode 2786 -b Suppress banner message on startup -N Set Application Name (sqsh) 2787 -B Turn off file buffering on startup -o Direct all output to file 2788 -c Alias for the 'go' command -p Display performance stats 2789 -C Send sql statement to server -P Sybase password (NULL) 2790 -d Min. severity level to display -Q Query timeout period in seconds 2791 -D Change database context on startup -r Specify name of .sqshrc 2792 -e Echo batch prior to executing -R Network security server principal 2793 -E Replace default editor (vi) -s Alternate column separator (\t) 2794 -f Min. severity level for failure -S Name of Sybase server ($DSQUERY) 2795 -G TDS version to use -t Filter batches through program 2796 -h Disable headers and footers -T Login timeout period in seconds 2797 -H Set the client hostname -U Name of Sybase user 2798 -i Read input from file -v Display current version and exit 2799 -I Alternate interfaces file -V Requested network security options 2800 -J Client character set -w Adjust result display width 2801 -k Specify alternate keywords file -X Enable client password encryption 2802 -K Network security keytab file (DCE) -y Override value of $SYBASE 2803 -l Set debugging level -z Alternate display language 2804 -L Set the value of a given variable -Z Network security mechanism 2805 2806