Ajax Call Back for MySQL INSERT FAILS?

[Gustavo1478]

I have a button that performs and ajax callback to a function in my XBASIC FUNCTION DECLARATIONS in a UX form:

'Add CONTRACTOR to look-up Table
function AddContractor as c (e as p)
	debug(1)
	dim cn as sql::Connection
	dim args as sql::Arguments
	dim sqlINSERT as c

	args.set("arg1", e.dataSubmitted.LicenseNumber)
	args.set("arg2", e.dataSubmitted.ContName)
	args.set("arg3", e.dataSubmitted.ContAddress)
	args.set("arg4", e.dataSubmitted.ContCity)
	args.set("arg5", e.dataSubmitted.ContState)
	args.set("arg6", e.dataSubmitted.ContZip)
	args.set("arg7", e.dataSubmitted.ContPhone)
	args.set("arg8", e.dataSubmitted.ContFax)
	args.set("arg9", e.dataSubmitted.ContRepFName)
	args.set("arg10", e.dataSubmitted.ContRepMName)
	args.set("arg11", e.dataSubmitted.ContRepLName)
	args.set("arg12", e.dataSubmitted.ContRepTitle)
	args.set("arg13", e.dataSubmitted.CreatedOn)
	args.set("arg14", a5ws_getcurrentuser())
	args.set("arg15", Now())
	args.set("arg16", a5ws_getcurrentuser())
	
	flag = cn.open("::Name::DAS140")
		If flag = .f. then
			e.javascript = "alert('Could Not Establish Connection');"
		end if

	sqlINSERT = "INSERT INTO contractors (CONT_LICENSE, CONTRACTOR, CONT_ADDRESS, CONT_CITY, CONT_STATE, CONT_ZIP, CONT_PHONE, CONT_FAX, CONT_REP_FNAME, CONT_REP_MI, CONT_REP_LNAME, CONT_REP_TITLE, CREATEDON, CREATEDBY, MODIFIEDON, MODIFIEDBY) VALUES (:arg1, :arg2, :arg3, :arg4, :arg5, :arg6, :arg7, :arg8, :arg9, :arg10, :arg11, :arg12, :arg13, :arg14, :arg15, :arg16)"

	flag = cn.Execute(sqlINSERT, args)
		If flag = .f. then
			e.javascript = "alert('Record Not Entered');"
		else
			e.javascript = "{dialog.object}.runAction('updateCONTRACTOR_LUID');"
		end if
	flag = cn.Close()
	
end function

Using the debug statement, I find that the cn.Execute(sqlINSERT, args) FAILS but do not know why? Also, the alert window to indicate "RECORD NOT ENTERD" does not come up either? Ive checked my columns in the database to make sure I didn't misspell anything, Ive checked my column types and all looks good to me.

Is there anything else I can check to figure out why this isnt working?

[lgrupido]

Re: Ajax Call Back for MySQL INSERT FAILS?

The alert does not come up because you don't set e.javascript in an xbasic function.

to return javascript, you set the function to the value of the javascript you want to return, so ...

At the end of the function put:

AddContractor = "alert('stuff');"

My guess is your run action isn't working either!

Note: Instead of making arg15 = now(), just put current_timestamp right in the insert statement. I'm not sure setting it to an argument and then putting it through like that will work.

so ...

:arg14, current_timestamp, :arg16

with no arg15 at all.

Finally, the sql execute will return an error code if you grab it. (Lookup the exact command.)

But you want to do something like this:

function = "alert('" + js_escape(cn.resultseterror) + "') ;"

You want to wrap it in js_escape because the error code will return some single quotes in there.

[GGandhi]

Re: Ajax Call Back for MySQL INSERT FAILS?

one thing I can think of, is
a5ws_getCurrentUser() is server function, it may not be available when you run in your development machine in debug mode.
to test, just set it to some arbitrary name like your name and test it if it works when you publish change it back to a5ws_getCurrentUser()

also I do not know why you need flag = cn.close()
you can just close it with
cn.close()

[Davidk]

Re: Ajax Call Back for MySQL INSERT FAILS?

Ivan,

Since you're using debug(1) to step through your code, you can examine the properties of your "cn" object in the deubber.

As soon as your code hits the debug statement... and you're in the debugger... type cn into the Expression area of the debugger. You'll get a message in the Value column that cn doesn't exist but that's ok. Keep stepping through the code and cn will be set and then executed. Once execute you can examine and expand the cn object... getting at cn.CallResult.Text which will give you the full error.

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

Ivan,

Since you're using debug(1) to step through your code, you can examine the properties of your "cn" object in the deubber.

As soon as your code hits the debug statement... and you're in the debugger... type cn into the Expression area of the debugger. You'll get a message in the Value column that cn doesn't exist but that's ok. Keep stepping through the code and cn will be set and then executed. Once execute you can examine and expand the cn object... getting at cn.CallResult.Text which will give you the full error.

Thanks David, I was trying to get the error code show up as a pop-up window but having trouble. I was able to step through the code like you said and view the text, heres what I got:

{Code=195,Text=Database API specific error�� ,NativeCode=1292,NativeText=Incorrect datetime value: '12/23,Syntax=,API=,RowsAffected=0,Success=.F.,Error=.T.,Canceled=.F.,Warnings=.F.,RowsReturned=-1}

Im guessing its because the field "CreatedOn" on my form is formatted to display Now(), but I MySQL does not store the dateTime value in that format correct?
Capture.PNG

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

Basically what I am trying to accomplish here is when a new contractor is entered into a myPROJECTS form (which holds contractor, awarding_agency, project address etc.), the contractor info gets appended to a contractor look-up table (if it is a new contractor).

Once the contractor is appended I want to fill in the ContractorID field in the projects form (the PK ID from the contractor look-up table). Currently I have a button that opens the "new contractor entry form" to be pre-populated to the fields in the project form for contractor, the user then hits submit, remembers the ContractorID , returns to the project form to fill in the ID and continues to fill out the myProjects form. I wanted to automate this whole process with a button press.
1.Append to Contractor Look-up
2.Get PKID
3.Fill in ID on ProjectsForm.

[lgrupido]

Re: Ajax Call Back for MySQL INSERT FAILS?

Just put the now() into the statement directly.

ie:

:arg14, now(), :arg16

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

What I dont understand is I have this:

'Insert Box 3 Note into notes_sql
function Box3 as c (e as p)
	dim cn as sql::Connection
	dim args as sql::Arguments
	dim sqlINSERT as c

	args.set("project_number_fk", e.dataSubmitted.ProjectNumber)
	args.set("processor", a5ws_getcurrentuser())
	args.set("date",Date())
	args.set("type","RECEIVED")
	args.set("note", "Received DAS140 Box 3 Complete. Will file away.")
	args.set("createdon", Now())
	args.set("createdby", a5ws_getcurrentuser())
	args.set("modifiedon", Now())
	args.set("modifiedby", a5ws_getcurrentuser())
	
	flag = cn.open("::Name::DAS140")
		If flag = .f. then
			alert("Could Not Establish Connection")
		end if

	sqlINSERT = "INSERT INTO notes_sql (project_number_fk, processor, date, type, note, createdon, createdby, modifiedon, modifiedby) VALUES (:project_number_fk, :processor, :date, :type, :note, :createdon, :createdby, :modifiedon, :modifiedby)"

	flag = cn.Execute(sqlINSERT, args)
	'	If flag = .f. then
	'	e.javascript =	"window.alert('Record Not Entered');"
	'		else
	'	e.javascript =	"window.alert('Record Entered Successfuly');"
	'	end if
	flag = cn.Close()
end function

In the same Xbasic Function Declaration and it works with no problems?

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

Okay, well I got the INSERT to work but now, How do I get the PK value of the inserted record, and populate a field in the current UX with it?

[lgrupido]

Re: Ajax Call Back for MySQL INSERT FAILS?

Because you are doing the coding yourself, you will have to find the PK yourself - Alpha can't really help you.

Try something like:

select max(pkfield) as pkfield from mytable where project_num = :projectnum and createdby = currentuser

That will give you the highest pk value created by that user - it should be the one you just inserted.

This is dependent on each user having a unique userid. (No sharing!)

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

Because you are doing the coding yourself, you will have to find the PK yourself - Alpha can't really help you.

Try something like:

select max(pkfield) as pkfield from mytable where project_num = :projectnum and createdby = currentuser

That will give you the highest pk value created by that user - it should be the one you just inserted.

This is dependent on each user having a unique userid. (No sharing!)

So something like this

flag = cn.Execute(sqlINSERT, args)
		If flag = .f. then
			addContractor = "alert('Oops! Boken? " + js_escape(cn.CallResult.text) + "');"
		else
			dim sqlSELECTMAXPKID as c
			sqlSELECTMAXPKID = "SELECT MAX(contractor_id) as pkid FROM contractors WHERE createdby = :arg14"
			AddContractor = "{dialog.object}.setValue('Contractor_ID',"+js_escape(sqlSELECTMAXPKID)+");"
		end if
	flag = cn.Close()

[GGandhi]

Re: Ajax Call Back for MySQL INSERT FAILS?

that is not necessary

whether grid or ux, the last inserted id is available after you submit the record. look at the function where you insert the data it will tell the mechanism for dbf and sql.
look in the function run down few lines you should how to get the last inserted id.

[lgrupido]

Re: Ajax Call Back for MySQL INSERT FAILS?

The last PK is available after submitting a record, but he is NOT submitting a record, he is doing an insert from a function. (Or am I missing something here?)

What PK values are you looking to get?

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

that is not necessary

whether grid or ux, the last inserted id is available after you submit the record. look at the function where you insert the data it will tell the mechanism for dbf and sql.
look in the function run down few lines you should how to get the last inserted id.

But I would not want the precise last record inserted, as there are multiple users, Larry was on the right track with this, Each user does have their own UNIQUE username. Ive set this following code but I dont think Im using the WHERE clause in the filter correctly, and Im also not sure how to set the ID in order to use it. Heres what Ive got so far that runs only if the insert new record was a success:

	flag = cn.Execute(sqlINSERT, args)
		If flag = .f. then
			addContractor = "alert('Oops! Boken? " + js_escape(cn.CallResult.text) + "');"
		else
			dim sqlSELECTMAXPKID as c
			dim PKID as n
			dim args as sql::Arguments
			
			args.set("arg17", a5ws_getcurrentuser())
			sqlSELECTMAXPKID = "SELECT MAX(contractor_id) FROM contractors WHERE createdby = :arg17"
			
			flag = cn.Execute(sqlSELECTMAXPKID, args)
				If flag = .f. then
					AddContractor = "alert('PKID Not Selected. " + js_escape(cn.CallResult.text) + "');"
				else
					PKID = sqlSELECTMAXPKID				
					AddContractor = "{dialog.object}.setValue('Contractor_ID',"+js_escape(PKID)+");"
				end if
		end if
	flag = cn.Close()

[Gustavo1478]

Re: Ajax Call Back for MySQL INSERT FAILS?

The last PK is available after submitting a record, but he is NOT submitting a record, he is doing an insert from a function. (Or am I missing something here?)

What PK values are you looking to get?

Larry you are correct, Im inserting a record with a button press, not submitting a form.