Datatypes. A Perl T-SQL comparison

Win has asked for the wisdom of the Perl Monks concerning the following question:

Data conversions seem to be very easy in Perl. So why then do other languages struggle so much with them. The T-SQL code will not allow me to compile code that converts a varchar string type into an int, even when the varchar is an interger and can be nothing else (because of my business logic that the SQL admittedly does not know about). Even so, it makes a solution to the problem unsolvable I think. Please prove me wrong. The code below will not compile. Instead I get ‘Incorrect syntax near 'CAST'’.


DECLARE @number_of_years AS NVARCHAR(2)

if @Method LIKE 'RSD_____%' or @Method LIKE 'RMS_______%' begin
    SET @number_of_years = (@yearto-@yearfrom)+1 
end


if @Method LIKE 'RSD_t%' begin

    if (SELECT SUBSTRING(@Method, 21, 1)) = '0' begin
        SET @number_of_years = (SELECT SUBSTRING(@Method, 22, 1))  
    
    end
    else begin
        SET @number_of_years = (SELECT SUBSTRING(@Method, 21, 2))
    end

end 


CAST (@number_of_years AS INT)
[download]

Comment on Datatypes. A Perl T-SQL comparison Download Code

Replies are listed 'Best First'.
Re: Datatypes. A Perl T-SQL comparison by demerphq (Chancellor) on Nov 16, 2005 at 14:46 UTC
Im unaware of the CAST function in T-SQL. Which doesn't mean it doesn't exist, just that I dont know about it. But there a "convert" function, and the following example that uses it proves you wrong. `select convert(numeric(10),"1000")+1` [download] Remember, you asked us to do this... What does this thread have to do with Perl again? --- $world=~s/war/peace/g	[reply] [d/l]
Re: Datatypes. A Perl T-SQL comparison by mpeppler (Vicar) on Nov 16, 2005 at 16:37 UTC
This has nothing to do with perl, but the real reason why you get "incorrect syntax near cast" is that you need to use CAST in a select statement - just using CAST by itself makes no sense. So something like `select CAST(@number_of_years AS INT)` [download] would produce reasonable output. Of course me I'd have declared @number_of_years as INT, and then used convert() or cast() in the assignments directly... Michael	[reply] [d/l]
Re: Datatypes. A Perl T-SQL comparison by Perl Mouse (Chaplain) on Nov 16, 2005 at 15:20 UTC
Data conversions seem to be very easy in Perl. So why then do other languages struggle so much with them. Because they have different requirements. Perl's datatypes are flexible, but they come with a price. It takes longer to get to the value (more pointers to walk), and it consumes a lot of memory (IIRC, the overhead of a string that has never been used in numeric context is 24 bytes; that's 24 bytes more than the data it's holding, it's even more if the string has been used in numeric context). It's also easier to get the 'wrong' typed value in them. T-SQL is a language geared to query databases. A VARCHAR takes more space than an INT (except perhaps very small VARCHARs, because it needs to record the size somewhere. An INT takes 4 bytes (or 8, but a fixed amount) - nothing more, nothing less. An INT variable will only store an INT - no checking whether it's a number or a string (and hence, no bits needed to record this - or spending time figure out what it is). Database variables are lean: that way, you can pack more of them in a page, which needs your cache expires less often, which means you can access your data faster. It also means you need less disk space (and no, disk space isn't cheap. Sure, it's cheap if you buy a disk for you PC to store your porn collection on - but it's not if you're using disk cabinets without single points of failure), and hence, less tapes. There are pick-up trucks, and there sports cars, and there are tiny fuel economical cars. There's a market for all of them. It's the same with variables. `Perl --((8:>*`	[reply]
Re: Datatypes. A Perl T-SQL comparison by thor (Priest) on Nov 16, 2005 at 14:24 UTC
Not that this is a perl question, but if your string contains only numbers, you should be able to do an implicit conversion. Observe: `declare @var nvarchar(3) set @var='123' select @var-10` [download] thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply] [d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Datatypes. A Perl T-SQL comparison by jfroebe (Parson) on Nov 16, 2005 at 16:15 UTC
Hi, You have a valid question but I think you're asking the question badly. I think you're trying to determine if you need to bind the parameters and specify the data types... if you're using DBD::Sybase, the answer would be no. Just bind the values normally. DBD::Sybase uses the underlying Sybase API calls to handle ?-style placeholders. For select/insert/update/delete statements DBD::Sybase calls the ct_dynamic() family of Client Library functions, which gives DBD::Sybase data type information for each parameter to the query. I mention DBD::Sybase, because Transact SQL is used in Sybase and Microsoft databases. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]