FORCING SQL Query Return Types (DBI)

Hi. I am an SQL-DBA, not a PERL-programmer, but I have been approached by a programmer who has difficulty with return types from SQL.

He queries a table on the SQL-server storing many values as floating point numbers (most or all can be represented as integers). The programmer uses CAST(column_name AS INT) on several columns in his query, iterates the results (loading them into an array of variables) and prints them with an automatic string conversion: print("$colvar1; $colvar2...").

He is quite upset with my SQL-server for returning floats when he has cast his columns to int. I believe my poor SQL-server is already doing what it can, and I know that the perl programmer can make explicit casts on the assigned variables, but I'm not off the hook until the query returns integer values all around. Unless that can for some reason not be done. I might eventually be able to convince him that some formatted print method is the ideal solution, and that this is not a failure of the SQL-server.

Specifications:

Data source: MS SQL Server 2008

ODBC Provider: Microsoft SQL Server DSN

PERL Version 5.8.7

Module: DBI

My questions to you:

A) Where is this behaviour introduced?

Behaviour: Not getting the appropriate type from a CAST in SQL

Suggested layers: ODBC provider, Perl runtime / DBI

B) Is it possible to get appropriately typed data directly from the SQL-query?

B2) How?

C) What is the ideal method for fetching floating point numbers from sql and displaying them as integer values?

I haven't posted any code, as solutions need not be based on existing code. If you think that code would provide information not otherwise available in this post, I can get at it, but I'll only do so if it makes a big difference. (If I ask for access to his scripts, it becomes personal. Again.)

All advice is appreciated.

A) I have not use CAST(...) in my sql calls, so I can't say for sure if the source of your issue is the particular SQL server you're using or Perl's db driver (DBD::ODBC), but I suspect it's a combination of both.

B) Yes, use bind params and specify the type.
B2) http://search.cpan.org/~mjevans/DBD...Parameter_Types

C) See: the int function or the printf and sprintf functions
printf will print the var directly and sprintf is used to assign the value to another var for later use. Their syntax is identical.

Thank you. I feel that "printf" looks like the best method. Although the developer expects me to solve this through SQL-server. What I've read about parameter binding so far, suggests that the ODBC-driver would be able to provide a data type. Perhaps the CAST-function's ability to return various types of data causes this particular ODBC-driver to be unspecific about what it returns.

I'll play around with both ends and anything inbetween for a while, and see what I can learn whilst solving the angry programmer's problem.

Your angry programmer must not know very much Perl since what you're needing is entry level knowledge and 5 minutes of reading the documentation for the ODBC module.

But in the end, he actually found the answer he was looking for: The MS SQL Server ODBC Driver regional settings are massively flawed and break lots of applications - turn it off.

While he could have made his script work independently of this, he is a much happier man now that he has made a useful change to his ODBC configuration (proving to him that his script was not at fault, which is always the chief issue).

Thank you for the help!