Inexplicable error in Sybase program and its solution 01-5-25 03:13:29
The Sybase database is one of the most popular large-scale databases in the UNIX environment. I discovered some Sybase internal rules in the process of developing and maintaining software in Sybase. It is easy to cause misunderstandings in programming. the goal of. The following questions and solutions thereof will be described below:
1. In Sybase11.5, two fixed length char (x) = "aaa", char (y) = "bbb"; char (x) char (y)! = "Aaabbb"
Declare @ Val_1 Char (8)
Declare @ val_2 char (1)
SELECT @ Val_2 = 'x'
SELECT @ VAL_1 = "0000"
SELECT @ var_1 = @ VAL_1 @ Val_2
SELECT @ var_1
Our desired result is 0000X, and actually has 0000.
Solution 1: When we will "select @ var_1 = @ Val_1 @ Val_2", when we see the results we expect. why? When a char (n) is stored in the Sybase version, the corresponding number of spaces are added to the true value. In this example, the 0000 is stored in @ var_1 (there are four spaces after 0000). You can add the following two sentences to verify:
Declare @ val3 char (10)
SELECT @ VAL3 = @ VAL_1 @ VAL_2
SELECT @ VAL3
The result you will get is 0000 x (there are four spaces after 0000)
Workaround 2: Change CHAR to VARCHAR can also achieve the desired purpose.
2 Column. example:
1> CREATE TABLE TMP (AA INT, BB INT)
2> Go
1> CREATE TABLE B_TMP (AA INT, BB INT)
2> Go
1> CREATE PROC TMPSTORE
2> as
1> INSERT B_TMP SELECT * FROM TMP
2> Return
3> Go
1> ALTER TABLE TMP Add CC Char (8) NULL
2> Go
1> ALTER TABLE B_TMP Add CC Char (8) NULL
2> Go
1> sp_recompile TMP
2> Go
1> INSERT TMP VALUES (12,1234, "abcdefg")
2> Go
1> EXEC TMPSTORE
2> Go
1> SELECT * from B_TMP
2> Go
sc
----------------------------------- 12234 NULL
Why is the CC field is null, not "abcdefg"? After adding the table structure with ALTER TABLE, the stored procedure containing "Select * from Tablename", recompiling with sp_recompile TableName, still cannot be identified by the new column. The solution is only one: delete rebuild.
