How to keep white spaces while executing insert in cfquery?

Question

Data is selected from one table and inserted into another table. This data happens to be key to first table, yet foreign key to the second table. Using cfquery, select and insert works without any issues. But when this data happens to contain more than one space, for example "XXX ZZZ" (there are three space in the middle) I will get a foreign key error when inserting data. Using db2, the error is -530.

I created a record with 2 spaces, same error. Created one space, with no error. This seem to prove that when inserting with cfquery, ColdFusion keeps one space, and removes any others if they exist. I even try to use replace(data," "," ","all") or chr(32) to replace the space, but still no luck passing more than one space and inserting successfully.

Hope you guys could help on solving this. Thanks!

<cfquery name="GetCategories" datasource="#DbSource#" username = "#DbUserId#" password = "#DbUserPswd#">
    SELECT category, rtrim(category) as tcategory
    FROM role_category
    ORDER BY category
    FOR FETCH ONLY
</cfquery>

Populate dropdown, tried following 3 ways, neither works
1
<select name="category" id="category" size = "4"  onChange="someFunction();">
  <cfoutput query="GetCategories">   
   <option value ="#category#" >#category#</option>
  </cfoutput>
</select>
2
<select name="category" id="category" size = "4"  onChange="someFunction();">
  <cfoutput query="GetCategories">   
   <cfset cat = replace(GetCategories.category," ","&nbsp;","all")>
   <option value ="#cat#" >#cat#</option>
  </cfoutput>
</select>
3
<select name="category" id="category" size = "4"  onChange="someFunction();">
  <cfoutput query="GetCategories">   
   <cfset tcat = replace(GetCategories.tcategory," ","&nbsp;","all")>
   <option value ="#tcat#" >#tcat#</option>
  </cfoutput>
</select>

in the action file:

 <cfquery name="InsertUserRole" datasource="#DbSource#" username = "#DbUserId#" password = "#DbUserPswd#">
        INSERT INTO role(role,desc, category)
        VALUES ('#_role#','#form.desc#','#form.category#')
 </cfquery>

The error message from Coldfusion is : DB2 SQL Error: SQLCODE=-530, SQLSTATE=23503, SQLERRMC=CATEGORY1, DRIVER=3.63.123

Update from comments:

I tried adding this, but that did not do it:

<cfqueryparam cfsqltype="cf_sql_char" value="#form.category#">

Please consider using a question mark when asking a question. I have no clue as to what you are asking. — Evik James, Mar 20 '15 at 15:41
We need to see some code. If it uses variables, please include samples of the values you are trying to insert and the complete error message. Also posting the [DDL](https://en.wikipedia.org/wiki/Data_Definition_Language) (relevant fields only) would not hurt. — Leigh, Mar 20 '15 at 15:42
You need to show us some code. As written (even after the edit) we do not have enough information to even begin to help you. — Scott Stroz, Mar 20 '15 at 17:01
Query parameters will probably solve this problem. The fact that foreign key values are stings containing spaces suggests that your database design has room for improvement. — Dan Bracuk, Mar 20 '15 at 17:34
@EvikJames, I see no place in the question where a question mark would be grammatically correct. — Dan Bracuk, Mar 20 '15 at 17:35
@Dan Bracuk, I have put the code there now, not sure how could I apply queryparameters, in insert query? — BS2009, Mar 20 '15 at 17:43
Might it be relevent that you show us a form field named "Avail_Categories" and try to insert the value of "form.category"? — Dan Bracuk, Mar 20 '15 at 18:59
@DanBracuk , that was not it. until the minute before inserting. it still has the spaces, but when executing insert, somehow coldfusion remove the extra spaces. I know is not the database issue. Because I can execute the same insert statement in qmf and inserts the value with extra spaces. So it got to be with ColdFusion somehow. — BS2009, Mar 20 '15 at 19:14
Still, it helps to post all the relevant info (column data types, dump of actual form field values, generated sql, etcetera..) *within* the question itself. Otherwise, we can only guess about what is or is not happening and why :) See [sscce.org](http://www.sscce.org/). *execute the same insert statement* So you are still getting the same error? What is the data type of the category column? Also, can you post the insert sql statement you executed? — Leigh, Mar 20 '15 at 19:40
@Leigh, sorry, first time to post a question. I have put all the related code into the question area, the data type for the column is Char(30). The inserting sql statement is at the end of the code in the question. — BS2009, Mar 20 '15 at 19:54
I meant the insert sql that you say worked in the database. Try running a `select` against the first table, filtering on #form.category#. Both with cfqueryparam and without it ie `select .... where col = '#form.category#'`. Then cfdump both of query objects and post the results. — Leigh, Mar 21 '15 at 00:30
I think the question that begs to be asked is: why do your PKs have spaces in them? That is something I have not heard, nor seen, before. — Scott Stroz, Mar 22 '15 at 03:48
Totally understand.I would ask the same question too, except I don't have control of the data I get from the database, what I need to do is to insert them back into database, simple as that and I am not able to do it. Space in PK in not rare in DB2, but with more than one white space connected together is rare, and I think should exist as well. — BS2009, Mar 23 '15 at 11:46

score -1 · Answer 1 · answered Mar 20 '15 at 17:13

-1

Hey bs2009, As far as what you have described in your question, it seems that the problem is related to the size of your Foreign Key. Please check if the Foreign Key in second table has same size as the Primary Key in first table. For eg. if Your primary key in first table is defined as CHARACTER(10) then the foreign key in second table should also be defined as CHARACTER(10).

answered Mar 20 '15 at 17:13

Pankaj

1,731
1
13
15

The error code spells out what the issue is - 'An insert or update operation attempted to place a value in a foreign key of the object table; however, this value was not equal to some value of the parent key of the parent table.' from https://www-304.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n530.dita – Scott Stroz Mar 20 '15 at 17:17
[Scott Stroz](/users/789209/scott-stroz). If you read the first paragraph of his question he clearly mentioned that he is getting error on putting more that one space between the characters and that data is not getting inserted. He is getting foreign key error. – Pankaj Mar 20 '15 at 17:24
@Pankaj, not the size problem, it works fine if the data value has not more than one spaces in it. And never work when it has more than one space connected together. – BS2009 Mar 20 '15 at 18:00
[Scott Stroz](/users/789209/scott-stroz). The link was really helpful, but still my answer does not deserve a down vote. As the link specifies that the problem is related to non equality of foreign and primary keys.. – Pankaj Mar 20 '15 at 18:01
[Scott Stroz](/users/789209/scott-stroz). Cosider a scenario where you have a primary key of size 10 and foreign key of size 7. If you insert a primary key value of size 7 in foreign key, you will not get any error. But if you insert a primary key value of size 10 in foreign key, you will get error. That was the reasoning behind my answer. – Pankaj Mar 20 '15 at 18:12
1

I understand your reasoning. But your assumption was wrong. As such, your answer does not solve the problem. Therefore.... Down vote. – Scott Stroz Mar 20 '15 at 21:45

How to keep white spaces while executing insert in cfquery?

1 Answers1