Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18

    Unanswered: Confused about Implicit Data Conversion Error

    Good Afternoon

    Hope that somone can shed some light...

    I am using the HitSoftware driver to pass data to an AS400 from a SQL 7 database. Data makes it fine to 2 of the 4 tables but I cannot get the syntax correct to even get out of the S/P edtitor in SQL for the other 2.

    I have fields in the SQL S/P defined as VARCHAR. The target fields on the AS400 are ALPHA. So I figured the insert statement to look like:

    INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
    VALUES (@v_action, @v_modelNumber, @v_modelDesc, '1', 1, 'EA', @v_acctClass, @v_modelyear, @v_engRevision, @v_spectype)

    However, when I try to close the S/P window I get the error:

    Error 257: Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

    So, I changed the insert statement to this:

    INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
    VALUES (CONVERT(binary, @v_action), CONVERT(binary, @v_modelNumber), CONVERT(binary, @v_modelDesc), CONVERT(binary, '1'), 1, CONVERT(binary, 'EA'), CONVERT(binary, @v_acctClass), CONVERT(binary, @v_modelyear), CONVERT(binary, @v_engRevision), CONVERT(binary,
    @v_spectype))

    Now the S/P closes without the error. But when I send it to the AS400 I get unrecognizable characters in the fields.

    Then I started thinking...I am not trying to do anything with binary fields.
    So I am really lost. Please help.

    Thanks,
    Ed 330-273-7521

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have the DDL of the table you're inserting in to?

    Seems like a column may be binary....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    The INVF30 field is zoned decimal. The 1 with no CONVERT in the insert statement is that value I am trying to use.

    If I change one of the CONVERT functions from binary to char where the target field is ALPHA, the implicit conversion comes. Just doesn't seem right...if I am dealing with ALPHA and VARCHAR how is it getting an error dealing with binary.

    Thanks,
    Ed

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What database is on the AS400?

    ALPHA?

    Is there a VARALPHA?

    Anyway, just for kicks, convert the varcharts to char(max length) and see what happens..

    Also have you done this successfully before?

    Can you compare the 2 AS400 structures to see how they're different?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    Hey There -

    I do not know what kind of db is on the 400. "They" kind of said "here is that tables you need to write to and the Hit Software driver we are evaluating". I did look at the table structure and the fields are CHAR not
    the mis-informed ALPHA i mentioned earlier.

    So, shouldn't VARCHAR to CHAR work? I did try declaring variables defined as CHAR and then Selecting the VARCHAR values into the variables. Then used those to send to the 400. Figured CHAR going to CHAR might net a different behavior. But, I could not get out of the S/P editor window due to the implicit erorr binary message.

    I have never done this before. Currently, the production system is using Symbiator to perform the data transfer task. Symbiator is going away here and they need to get the data to the 400 via an alternative means.

    Ed

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry for not being helpful...

    but can you do a select of the data from the as400 and see what it look like?

    (My guess is it's DB2)

    And place the results in to a temp table....


    Also you say it worked for 2 tables already...

    I would ask, what's different between these tables....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    OK...

    I dumped the file to a spreadsheet and it appears to be in binary. I don't know how to convert binary and have to figure out how to make it
    "readable".

    One of the other tables only has 1 field and it is CHAR(15). The SQL source is VARCHAR(15). Second table has 3 fields that are CHAR and the SQL sources are all VARCHAR.

    I can read the data fine and did not have to convert VARCHAR to binary just to get it to go across to a CHAR field.

    very bizzare and frustraing at the same time

    Ed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •