Hi,
I am having a problem getting character (string) variables into a
dataframe. I seem to have no problem with numeric fields, but character
fields are appearing as a "1".
In my problem I need to retrieve some information from an SQL database
using RODBC one line at a time and then update the respective row.
I have tried converting the object returned from the query into various
classes but to no avail.
Here is a selection of the code:
I am updating table2 in this case which only has two rows.
The query is executed for the row and stored in y. The contents of y
are then meant to update the various columns.
> for (x in c(1:2)) { y <-
sqlQuery(EFXchannel,paste(getData,total2$TRAN_NUMBER[x]))
+ y <- as.list(y)
+ total2$TRN_DT[x] <- as.numeric(y[1]) +ISOdatetime(1970,1,1,1,0,0)
+ total2$CARDNUM[x] <- as.character(y[2])
+ total2$TRN_AMT[x] <- as.double(y[3])
+ total2$MER_CNTY_CD[x] <- as.numeric(y[4])
+ total2$SIC_CD[x] <- as.numeric(y[5])
+ total2$MER_NM[x] <- as.character(y[6])
+ print(y[6])
+ total2$TRN_POS_ENT_CD[x] <- as.character(y[7])
+ total2$RULE_HIT[x] <- as.numeric(y[8])}
MER_NM
1 XXXXX RENT A CAR
MER_NM
1 HOTEL XXXXX
> total2
TRAN_NUMBER TRN_DT TRN_AMT MER_CNTY_CD SIC_CD MER_NM
TRN_POS_ENT_CD RULE_HIT
1 720001861 1272544032 600 724 7512 1
1 1
2 720001377 1272493155 86 250 7011 1
1 1
>
As you can see MER_NM and TRN_POS_ENT_CD do not update.
For information this is the type of information returned by the query
> z <- sqlQuery(EFXchannel,paste(getData,720001377))
> z
TRN_DT TRN_AMT MER_CNTY_CD
SIC_CD MER_NM TRN_POS_ENT_CD RULE
1 2010-04-28 23:19:15 86 250 7011 HOTEL
XXXXX K 1
> class(z)
[1] "data.frame"
Any assistance or alternate methodologies would be much appreciated.
Thanks,
Chris