[xheartonfire43x]

I am working on Coldfusion 5 and have been having a problem lately. We are building an import routine and one of our imports aren't working properly. It is supposed to submit a .txt file and then run it through the merrant text driver to create a datesource out of it and it isn't working.

<CFQUERY datasource="serviceimport2" name="mydata" USERNAME="#myusername#" PASSWORD="#mypassword#">
select 
field_17 as fname,
field_16 as lname,
field_25 as zip,
field_28 as hphone,
field_31 as ophone,
field_23 as city,
field_24 as state,
field_21 as address1,
field_22 as address2,
field_1 as customerid
from #session.myimportfile# 
</CFQUERY>

that's the query that's not working. IT is the to phone number fields that are erroring out.

This post has been edited by xheartonfire43x: 13 January 2009 - 11:12 AM

[CR250]

Can you post the errors message?

[xheartonfire43x]

CR250, on 13 Jan, 2009 - 10:13 AM, said:

Can you post the errors message?

ODBC Error Code = 22005 (Error in assignment)

[MERANT][ODBC Text driver]Invalid character value.

SQL = "select field_17 as fname, field_16 as lname, field_25 as zip, field_28 as hphone, field_31 as ophone, field_23 as city, field_24 as state, field_21 as address1, field_22 as address2, field_1 as customerid from 090113Name10_1784282265.txt"

Data Source = "SERVICEIMPORT2"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:99).

[sansclue]

xheartonfire43x, on 13 Jan, 2009 - 10:16 AM, said:

[code]ODBC Error Code = 22005 (Error in assignment)
[MERANT][ODBC Text driver]Invalid character value.

Is that the full error or is it "Invalid character value for cast specification". What kind of values are in the phone number columns all text, all numbers or a mix of both:

1. all text example "(999) 999-9999"
2. all numbers example "9999999999"
3. mix of both "(999) 999-9999" and 9999999999

[xheartonfire43x]

sansclue, on 13 Jan, 2009 - 11:49 AM, said:

xheartonfire43x, on 13 Jan, 2009 - 10:16 AM, said:

[code]ODBC Error Code = 22005 (Error in assignment)
[MERANT][ODBC Text driver]Invalid character value.

Is that the full error or is it "Invalid character value for cast specification". What kind of values are in the phone number columns all text, all numbers or a mix of both:

1. all text example "(999) 999-9999"
2. all numbers example "9999999999"
3. mix of both "(999) 999-9999" and 9999999999

Nope that is an exact copy of the rror leaving out my server information.
The phone numbers are all in "9999999999" format. No other characters but numbers.

[sansclue]

xheartonfire43x, on 13 Jan, 2009 - 11:59 AM, said:

Nope that is an exact copy of the rror leaving out my server information.
The phone numbers are all in "9999999999" format. No other characters but numbers.

Hmm, maybe the driver is trying to fit 9999999999 into one of its number types and cannot.

1) Does your file have a header
2) Is this a fixed file format that will not change? If so you could try setting up an .ini file to give the driver some hints.

3) Out of curiosity, what database type are you using for other queries? Any chance it is an MS database?


One other thing. What happens if you put a text character in front of the phone number values (ie instead of 9999999999 try "T9999999999" or "(999)9999999"). If that works it would indicate a data type conversion problem.

[xheartonfire43x]

sansclue, on 13 Jan, 2009 - 12:17 PM, said:

xheartonfire43x, on 13 Jan, 2009 - 11:59 AM, said:

Nope that is an exact copy of the rror leaving out my server information.
The phone numbers are all in "9999999999" format. No other characters but numbers.

Hmm, maybe the driver is trying to fit 9999999999 into one of its number types and cannot.

1) Does your file have a header
2) Is this a fixed file format that will not change? If so you could try setting up an .ini file to give the driver some hints.

3) Out of curiosity, what database type are you using for other queries? Any chance it is an MS database?


One other thing. What happens if you put a text character in front of the phone number values (ie instead of 9999999999 try "T9999999999" or "(999)9999999"). If that works it would indicate a data type conversion problem.

It is a fixed value thing so I did go ahead and create a schema for it but now that instead of using field_1 i used mydata.f1 because i queried all of it but it is now not resolving f1 to be the first column. And no there is no header.

[sansclue]

xheartonfire43x, on 13 Jan, 2009 - 12:43 PM, said:

It is a fixed value thing so I did go ahead and create a schema for it but now that instead of using field_1 i used mydata.f1 because i queried all of it but it is now not resolving f1 to be the first column. And no there is no header.

Not detecting the values or detecting the wrong column as the first column? I am pretty sure .ini give you absolute control over how the file is interpreted. Assuming it is setup properly, and includes the right file name. If it is still not working, can you post your .ini file?

[xheartonfire43x]

sansclue, on 13 Jan, 2009 - 12:54 PM, said:

xheartonfire43x, on 13 Jan, 2009 - 12:43 PM, said:

It is a fixed value thing so I did go ahead and create a schema for it but now that instead of using field_1 i used mydata.f1 because i queried all of it but it is now not resolving f1 to be the first column. And no there is no header.

Not detecting the values or detecting the wrong column as the first column? I am pretty sure .ini give you absolute control over how the file is interpreted. Assuming it is setup properly, and includes the right file name. If it is still not working, can you post your .ini file?

Well the first thing that I need to pul from the fil is the 17th column. So i put in mydata.f17 and it gives me this error

Error Diagnostic Information

An error occurred while evaluating the expression:

#mydata.f17#

Error near line 15, column 13.

Error resolving parameter MYDATA.F17

The column F17 is not present in the query named MYDATA. It is likely that you have misspelled the name of the column.

The error occurred while processing an element with a general identifier of (#mydata.f17#), occupying document position (15:12) to (15:23).

[sansclue]

Quote

Well the first thing that I need to pul from the fil is the 17th column. So i put in mydata.f17 and it gives me this error

Error Diagnostic Information

An error occurred while evaluating the expression:

#mydata.f17#

Error near line 15, column 13.

Error resolving parameter MYDATA.F17

The column F17 is not present in the query named MYDATA. It is likely that you have misspelled the name of the column.

The error occurred while processing an element with a general identifier of (#mydata.f17#), occupying document position (15:12) to (15:23).

Well it is hard to tell without seeing the file, but dump the query. What columns does it contain? Also, are you sure the file name in your .ini file matches the file name of the uploaded file? It may not work otherwise.

This post has been edited by sansclue: 13 January 2009 - 02:17 PM

[xheartonfire43x]

Quote

Well it is hard to tell without seeing the file, but dump the query. What columns does it contain? Also, are you sure the file name in your .ini file matches the file name of the uploaded file? It may not work otherwise.

I did a dump and found out that the columns were not actually being called as f1, f2, f3, etc. I had been giving the columns names in the .ini file and when I had originally given the names they were hyphens so when I initially used those names it didn't work. I changed the hyphens to underscores and it works just fine. Thank you.