Having weird query issue

Communities ColdBox HMVC
1

I am submitting a simple form to enter a new business and I send it to the function create.

function create( event, rc, prc ) {
var id = createUUID();

	query.from("citidel_consern")
		.insert( {
		"concernID" : #id#,
		"concernName": rc.concernName,
		"businessAddressOne": rc.businessAddressOne,
		"businessAddressTwo": rc.businessAddressTwo,
		"businessCity": rc.businessCity,
		"businessState": rc.businessState,
		"businessZipcode": rc.businessZipcode,
		"businessPrimaryPhone": rc.businessPrimaryPhone,
		"Country": rc.Country,
		"timeInBusiness": rc.timeInBusiness,
		"Description" : rc.Description,
		"datecreated": now(),
		"userIDfk": auth().getUserId()
	});

	query.from( "user_concern" )
		.insert( {
			"userIDfk" = auth().getUserId(),
			"concernIDfk" = #id#
		} );
	

	relocate( "concerns" );
}

The first insert does just fine but the second one blows up:

Parameter index out of range (3 > number of parameters, which is 2).

Database

Datasource citidel5
Name-Value Pairs —
SQL Sent Parameter index out of range (3 > number of parameters, which is 2).

?

INSERT INTO ``user_concern (concernIDfk, userIDfk) VALUES ( '297846 New Drive' , '' )|
|SQL State|S1009|
|NativeErrorCode|0|
|Driver Error Message|INSERT INTO user_concern (concernIDfk, userIDfk) VALUES (‘297846 New Drive’, ‘’)|
|Exception Detail|Parameter index out of range (3 > number of parameters, which is 2).|
|Additional Info|Struct (ordered)|

the one value it tries to put into the user_concern database isn’t even the correct value. I can’t seem to figure this one out, it seems like it should be simple.

thank you for your help,

Rick

2

Don’t reuse qb instances. They are transients and hold state. You need to 1) get a new qb instance each time through getInstance, 2) get a new qb instance using the newQuery method, 3) reset the qb instance using the reset method, 4) cloning the existing qb instance and using the new clone using the clone method, or 5) use a WireBox provider which will create a new instance every time you call a method on the injected object (convenient but a little advanced).

3

Thank you so much elpete!!! I do have the injection at the top of the component, shouldn’t that create a new instance of query?

component {

property name=“query” inject=“QueryBuilder@qb”;

4

Sure, it does create a new instance at the time your own component is initialized. It obviously won’t create a new instance every time you access variables.query. See Eric’s suggestions for resetting an existing query builder object.

Also, it’s just me, but I would avoid using the variable name query as it might cause confusion with the new query() syntax from the CF engine itself.

5

Thank you so much for your help!!! I’m going to get this right yet!!!

1 Like