How to specify field data type when reading from a SQL database

Question

I have a SQL table called "Customers". In it is a CustomerNumber field with values like "0001234567", i.e. they are made up of numbers only, but some include leading 0s. So, when I try to run something like

sqlFetch(channel=myconn, sqtable="Customers", stringsAsFactors=FALSE)

it returns my customers table where the CustomerNumber field is numeric (instead of character), and consequently I lose all those leading 0s.

Is there a way I can specify the fieldtype for a column or an alternative solution that won't truncate all my leading 0s?

Archaeopteryx

Answer 1

You can control the types of columns more fully using the as.is argument, which is documented in the Details section of ?sqlFetch as well as at the linked documentation for ?sqlQuery and ?sqlGetResults.

Basically, it is either a vector of logicals or a vector of numeric or character indices specifying which columns to leave untouched. This vector will be recycled as necessary.

(Note that RODBC will clobber columns stored in the database with type.convert even if the C API correctly returns char or varchar as the data type of the column in the database. The maintainer has not responded to any of my 4-5 emails on this issue over the past year, and I have since simply used a forked version of RODBC with the needed one line modification ever since.)

Disaccord

4-5 emails? I think I'd've given up after the 2nd one ;) – Archaeopteryx

@Archaeopteryx Well, around half were to r-sig-db, the other half to the maintainer specifically. :) – Disaccord

Answer 2

You could try specifying a query string and you could even cast the field if you're still having problems.

Sub Main()
    Dim myConn As String = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnStr")
    Dim sqlConnection As SqlConnection = New SqlConnection(myConn)

    Dim strSQL As String = "select cast(CustomerNumber as varchar(30)) as CustomerNumber_varchar, * from Customers"
    Dim myds As DataSet

    sqlConnection.Open()

    Dim cmd As SqlCommand = New SqlCommand(strSQL, sqlConnection)
    cmd.CommandTimeout = 60

    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    myds = ConvertDataReaderToDataSet(myReader)
    myReader.Close()
End Sub


Public Function ConvertDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet
    Dim dataSet As DataSet = New DataSet()
    Dim schemaTable As DataTable = reader.GetSchemaTable()
    Dim dataTable As DataTable = New DataTable()
    Dim intCounter As Integer
    For intCounter = 0 To schemaTable.Rows.Count - 1
        Dim dataRow As DataRow = schemaTable.Rows(intCounter)
        Dim columnName As String = CType(dataRow("ColumnName"), String)
        Dim column As DataColumn = New DataColumn(columnName, _
            CType(dataRow("DataType"), Type))
        dataTable.Columns.Add(column)
    Next
    dataSet.Tables.Add(dataTable)
    While reader.Read()
        Dim dataRow As DataRow = dataTable.NewRow()
        For intCounter = 0 To reader.FieldCount - 1
            dataRow(intCounter) = reader.GetValue(intCounter)
        Next
        dataTable.Rows.Add(dataRow)
    End While
    Return dataSet
End Function

Linn

Wrong language, I think. – Disaccord

Wrong language for sure. – Obed