Recent changes to support-requests

#4 sqsh doesn't work with ASE!5

2013-08-27T16:09:52.266000Z

status: open --> closed
Group: --> v1.0 (example)

#11 Issue with sql update when single quote in data

2013-06-07T14:45:58.760000Z

After reading through the question again, I would like to add some additional comments to explain what is happening here. First please note that

1) Sqsh does not expand $variables or escape characters in between a single quoted string.
2) Everything in the buffer outside single quoted strings will go through expansion, including double quoted strings, only if $expand is true.
3) By default expansion is on, which can be controlled by the sqsh variable $expand. To turn expansion off use:

1> \set expand=[0|Off|False]

or start sqsh with the parameter -Lexpand=0 on the command line.
4) sqsh use double backslashes as escape sequence and does not understand single backslash escape characters.
5) You can use the -e command line parameter with sqsh to make it echo the command buffer it is sending to the server to stdout. You can also switch the variable $echo on (\set echo=On) just to accomplish the same. This might sometimes explain what is really going on under the hood with buffer expansion and what command is finally sent to the server.

So what happened with your update statement then?

update custom.maintenance set MessageText='test\'s datastore1_$23$'

Well, just before the SQL buffer is submitted to the server it is expanded by default. During expansion $variable names are substituted and escaped characters are skipped from interpretation (but the escape sequence \ itself is removed). So when sqsh encounters the first quote in the buffer it assumes the beginning of a string that should be skipped from expansion and when it encounters the second quote it assumes end of quoted string and resumes normal expansion again. Sqsh does not understand a single backslash as an escape sequence. So it interprets the \' as end of the single quoted string and starts expanding the remainder of the SQL buffer. Then it finds a $ sequence which it interprets as an escaped ( so it trims of the \. The same happens with the $. Then it finds a quote again and assumes a new beginning of a quoted string. But then finally end of buffer is reached and the buffer

update custom.maintenance set MessageText='test\'s datastore1_(23)'

is sent to the server. It is left to the server to interpret the \' escape sequence. When you leave out the \' from the command, sqsh skips expansion of the whole string 'tests datastore1_$23$' and your backslashes remain there.

So what can you do to circumvent this problem then? 1) Set expand to off. 2) Use 2 single quotes in sequence to escape a single quote. For example:

'test''s datastore1_$23$'

would work as well. 3) Code enough backslashes for expansion. A single backslash will not be expanded, Two backslashes escape the third character but will be removed from the buffer themselves. So whatever follows a \ remains there. So for example \\ will result in \, but \\ will result in \ because the first two backslashes escape the third backslash and the fourth backslash will be just interpreted as is. However 5 backslashes just result in one, the first three produce that one \ and the last two will just be trimmed off. To demonstrate this look at the following SQL and the output it produces:

1> select "one" ="\"
2> select "two" ="\"
3> select "three"="\\"
4> select "four" ="\\"
5> select "five" ="\\\"
6> select "six" ="\\\"
7> select "seven"="\\\\"
8> go -f -m vertical
one: \
two:
three: \ four: \
five: \ six: \
seven: \\

But this SQL was sent to a Sybase ASE server which does not interpret escape characters. When you connect to a MSSQL server that does interpret \ escape characters, this might complicate things even more. Your initial insert statement for example contains many single quotes, two of them escaped, which led to the situation, according to sqsh, that $ and $ was inside single quotes and did not get expanded by sqsh and the string was correctly sent to the server. The server then inserted ( in the database as it interpreted the single backslash escape character to escape a backslash (\). So probably just a coincidence that the insert worked fine here.

Hope this clarifies some of the internal sqsh behaviour with expansion.

Cheers,
Martin.

Not a bug but a question

2013-04-22T04:12:10Z

Hi Marin, am trying to use a function as below but variable substitution is not happening, is this because of single quotes ?

Unfortunately, object_id() (from mssql) needs it within single quotes, any tips ?

\func -x show_frag
\if [ $# -ne 1 ]
\echo "usage: show_frag <table>"
\return 1
\fi

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(db_name()), OBJECT_ID('${1}'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
go
\done

Issue with sql update when single quote in data

2012-11-16T23:54:56Z

If you look at the below insert statement, I am escaping the parenthesis with two backslashes to insert the record in the database and that works fine. After that, when I update the same record in that field, when I change the value from 22 to 23 with the two backslashes, it no longer inserts the $ in the db. So what seems to be the issue? Any help would be very much appreciated.
DEVPNCOMS..1> insert into custom.maintenance (KeyField, Device, sModeType, StartTime, EndTime, Flag, EnteredBy, Reason, DevComp, sModeScope, MessageText, LOB) values('Device_serverx_MessageText_test\'s datastore1_\\(22\$_1353107520_1353108420', ‘serverx’, 'Device', 1353107520, 1353108420, 1, 'gnangia','test','Server','MessageText','test\'s datastore1_\$22\$','XXXX');
DEVPNCOMS..1> go
DEVPNCOMS..1> select MessageText from custom.maintenance
DEVPNCOMS..2> go

test's datastore1_$22$

DEVPNCOMS..1> update custom.maintenance set MessageText='test\'s datastore1_\$23\$'
DEVPNCOMS..2> go
DEVPNCOMS..1> select MessageText from custom.maintenance
DEVPNCOMS..2> go

test's datastore1_(23)

I did some further testing and found that if I use 4 backslashes before the parenthesis, it inserts a single backslash - same as the initial insert - but this is only true if I have a single quote in the value prior to the parenthesis. If there is no single quote or the single quote is after the parenthesis, I only need two backslashes, which seem to be very inconsistent.

$width limitation of 256 should be removed/increased

2010-06-06T07:10:36Z

Hi,

older versions of sqsh (as far as I remember) did not have a $width variable and did not automatically wrap lines. That was incredibly nice together with using less -S as the pager: you could scroll left/right with your arrow keys in less to see the entire line, everything nicely lined up with no wrapping.

Current versions of sqsh seem to automatically wrap lines based on the $width variable. The maximum value of that is 256, which for many purposes is too short, for example sp_help <tablename> wraps for me on the description of the indices. There's many system tables as well for which a select * from result in lines longer than 256 characters, not to mention user tables.

I'd like to propose an option to either disable wrapping in sqsh, or to allow much larger allowed values for $width.

make file error on Linux

2008-10-15T16:00:28Z

Linux 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:56:44 EST 2007 x86_64 x86_64 x86_64 GNU/Linux

make[1]: Entering directory `/software/sqsh-2.1.5/src'
gcc -L/opt/sybase12_5/OCS-12_5/lib cmd_alias.o cmd_bcp.o cmd_buf.o cmd_connect.o cmd_do.o cmd_echo.o cmd_exit.o cmd_for.o cmd_func.o cmd_go.o cmd_help.o cmd_history.o cmd_if.o cmd_input.o cmd_jobs.o cmd_kill.o cmd_lock.o cmd_loop.o cmd_misc.o cmd_read.o cmd_reconnect.o cmd_redraw.o cmd_reset.o cmd_return.o cmd_rpc.o cmd_set.o cmd_shell.o cmd_show.o cmd_sleep.o cmd_wait.o cmd_warranty.o cmd_while.o var_ctlib.o var_date.o var_debug.o var_dsp.o var_hist.o var_misc.o var_passwd.o var_readline.o var_thresh.o dsp.o dsp_bcp.o dsp_csv.o dsp_conv.o dsp_desc.o dsp_horiz.o dsp_html.o dsp_meta.o dsp_none.o dsp_out.o dsp_pretty.o dsp_vert.o dsp_x.o sqsh_alias.o sqsh_args.o sqsh_avl.o sqsh_buf.o sqsh_cmd.o sqsh_compat.o sqsh_ctx.o sqsh_debug.o sqsh_env.o sqsh_error.o sqsh_expand.o sqsh_fd.o sqsh_filter.o sqsh_fork.o sqsh_func.o sqsh_getopt.o sqsh_global.o sqsh_history.o sqsh_init.o sqsh_job.o sqsh_readline.o sqsh_sig.o sqsh_sigcld.o sqsh_stdin.o sqsh_strchr.o sqsh_tok.o sqsh_varbuf.o sqsh_main.o -lblk -lcs -lct -lsybtcl -lcomn -lintl -ldl -lm -o sqsh
/usr/bin/ld: skipping incompatible /opt/sybase12_5/OCS-12_5/lib/libblk.a when searching for -lblk
/usr/bin/ld: cannot find -lblk
collect2: ld returned 1 exit status
make[1]: *** [sqsh] Error 1
make[1]: Leaving directory `/software/sqsh-2.1.5/src'
make: *** [build-subdirs] Error 2

thanks!

Segmentation fault for select with large floating point valu

2008-03-06T14:37:05Z

Hi,

I get a segmentation fault when I select from a table containing a certain floating point numbers.

host-13: sqsh
sqsh-2.1.4 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2006 Michael Peppler
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
[22] sqldev15.tibbetts.1> create table foo (col1 double precision);
[23] sqldev15.tibbetts.1> insert into foo values (-1e120);
(1 row affected)
[24] sqldev15.tibbetts.1> select col1 from foo;
col1
--------------------
-999999999999999980003468347394201181668805192897008518188648311830772414627428
725464789434929992439754776075181077037056.000000

(1 row affected)
Segmentation fault

This happens on both Solaris, sqsh built with Sun C compiler 5.7, and Linux, sqsh built with gcc-3.4.6.

I suspect this is a buffer overflow issue with the space allocated for CS_FLOAT and CS_REAL datatypes in dsp_desc.c. 64 bytes are malloc, but the values requires more to properly display using the %f format specifier in sprintf.

unable to link sqsh on cygwin

2006-05-16T19:43:54Z

The linking trips up on libdl which doesn't exist on
cygwin. Attempted to substitute with libsybct with no
luck. OpenClient 15

$ make
....
sqsh_main.c: In function `hide_password':
sqsh_main.c:889: warning: comparison between pointer
and integer
gcc -L/usr/X11R6/lib
-L/cygdrive/c/sybase15/OCS-15_0/lib cmd_alias.o
cmd_bcp.o cmd_buf.o cmd_connect.o cmd_do.o cmd_echo.o cm
d_exit.o cmd_for.o cmd_func.o cmd_go.o cmd_help.o
cmd_history.o cmd_if.o cmd_input.o cmd_jobs.o
cmd_kill.o cmd_lock.o cmd_loop.o
cmd_misc.o cmd_read.o cmd_reconnect.o cmd_redraw.o
cmd_reset.o cmd_return.o cmd_rpc.o cmd_set.o
cmd_shell.o cmd_show.o cmd_slee
p.o cmd_wait.o cmd_warranty.o cmd_while.o var_ctlib.o
var_date.o var_debug.o var_dsp.o var_hist.o var_misc.o
var_passwd.o var_re
adline.o var_thresh.o dsp.o dsp_bcp.o dsp_csv.o
dsp_conv.o dsp_desc.o dsp_horiz.o dsp_html.o dsp_meta.o
dsp_none.o dsp_out.o dsp
_pretty.o dsp_vert.o dsp_x.o sqsh_alias.o sqsh_args.o
sqsh_avl.o sqsh_buf.o sqsh_cmd.o sqsh_compat.o
sqsh_ctx.o sqsh_debug.o sqs
h_env.o sqsh_error.o sqsh_expand.o sqsh_fd.o
sqsh_filter.o sqsh_fork.o sqsh_func.o sqsh_getopt.o
sqsh_global.o sqsh_history.o sq
sh_init.o sqsh_job.o sqsh_readline.o sqsh_sig.o
sqsh_sigcld.o sqsh_stdin.o sqsh_strchr.o sqsh_tok.o
sqsh_varbuf.o sqsh_main.o -l
m -lsybct -lXaw -lXt -lXext -lXmu -lX11 -lreadline
-lcurses -o sqsh
/usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../../i686-pc-cygwin/bin/ld:
cannot find -lsybct
collect2: ld returned 1 exit status
make: *** [sqsh] Error 1

Fix??

Jason L. Froebe

Error msg when connecting to Replication Server

2006-02-24T09:47:31Z

Hi,

I've got the following problem since using sqsh 2.1.3
( AIX ).
When I establish a connection to a Replication Server
(12.6), an error is displayed :
./sqsh -Usa -SEXPRS1 -Ppass
sqsh-2.1.3 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004 Michael Peppler
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
No SRV_OPTION handler installed.
Open Client Message
Layer 1, Origin 1, Severity 1, Number 183
ct_options(): user api layer: external error: An error
was returned from the server while
setting the options, check the server message for
details.
[3] EXPRS1..1>

There was no such error with sqsh 2.1 on Solaris.

Best regards,

Language/Charset warnings

2005-10-28T15:11:11Z

When connecting to an ASE server with sqsh - if there
is a non iso_1 character set ( in my case utf8) etc,
several warning messages are returned - 5704, 5703 &
5701 - according to one of the solved cases on Sybase'
website , these can be suppressed in OpenSwitch -
using :

SUPPRESS_CHARSET = 1 - suppresses 5704
SUPPRESS_DBCTX = 1 - suppresses 5701
SUPPRESS_LANG = 1 - supresses the language
change

I was wondering whether this kind of suppression could
be added to SQSH so that the messages are
suppressed when connecting to ASE?