Converting integers to String in Sybase

I had to manually insert some data into a sybase DB recently to correct a recurring faulty programming logic. An application was attempting to insert 4 rows into the database. Of these rows, 1 row of data was already available in the database, so a primary key violation was raised by the application and the rest of the data never made it into the sybase DB. Since this was a recurring problem I simply took the previous SQLs written by some one else to fix this and inserted 3 records into the DB.

Here is what one of the SQLs did

Begin

...

insert into table values (str(@variable),'value',1,12.6)

...

End


The @variable here was a primary key. This value is obtained dynamically each time as an integer and then converted to a string. For the sake of discussion lets assume it was 11576. The SQL worked pretty well and everything seemed to be going fine until I executed the following query on the sybase DB

select * from table where primary_key = '11576'

To my shock the row from the table was never returned. I did not understand why, because this same SQL was executed previously to fix the problem. I modified the SQL to the following

select * from table where some_other_value = 'value' and something = 1

Now the row was returned and I could see quite clearly that the primary_key was still 11576. Since it was a string i began to suspect that spaces might be a problem. Another modification

select * from table where primary_key like '%11576'

This time the row was returned again. After going through the documentation for the str function, i figured it out. SELECT STR( 12345 ) would return '(5 spaces)12345'. When some one wrote this SQL they had assumed that str(number) would return the exact string representation of that number. Instead I used convert ( varchar(5), @variable ) and things worked as expected. You could also use SELECT STR( 1234.56, 6, 1 ) to return 1234.6. Here the length and the decimal places are mentioned.

Lessons learnt here

• Never assume that a previous fix is flawless
• Consult the documentation when in doubt