Insert statement for SQL

[richardurban]

This was not obvious to me as an SQL newbie. If you want to insert the values of a row in a grid into your SQL table, you cannot reference them directly, you must use arguments:

with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""
dim uuid as c
request.variables.uuid = uuid
Dim cn as sql::connection
Dim args as SQL::Arguments

args.add("New_code",DataSubmitted.New_code)
args.add("Quantity",DataSubmitted.Qty)
args.add("Uuid",uuid)
flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c
sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID) Values (:New_code,:Quantity,:Uuid)"
flag = cn.execute(sqlCommand,args)

If you don't use arguments, and just, for example, use the sqlCommand without arguments:

sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID) Values (DataSubmitted.New_Code,DataSubmitted.Qty,uuid)
flag = cn.execute(sqlCommand)"

no values will be inserted into your sql table. That syntax worked in DBF, but it does not work in SQL.

[Steve Workings]

Re: Insert statement for SQL

Richard - I presume you've found this:

http://www.downloads.alphasoftware.c...SQLTables.html

Also, use args.set() instead of args.add(). The former will .add if it doesn't exist, and use existing if it exists.

[GGandhi]

Re: Insert statement for SQL

This was not obvious to me as an SQL newbie. If you want to insert the values of a row in a grid into your SQL table, you cannot reference them directly, you must use arguments:

i don't think that is true, using arguments prevents any sql injection from outside. so it is the preferred method of inserting, updating records.

DataSubmitted.New_Code,DataSubmitted.Qty,uuid)

they don't seem right, unless you have defined those variables and assigned the grid variables to them such as
DataSubmitted.New_Code = e._dataSubmitedNew.Code something like that.

[richardurban]

Re: Insert statement for SQL

Steve, yes I saw that, thanks for including that valuable reference. Gandhi, If you are, in the "CanUpdateRecord" server-side event window of a grid, when you click the "Insert into script" link above the top of the window, that is the format that is inserted (DataSubmitted.New_Code) not e.dataSubmittd.New_Code.
If you reference actual values in the SQL insert statement, that works, such as '12345', but referencing the DataSubmitted value directly, as I mentioned above, does not.

[christappan]

Re: Insert statement for SQL

You could do this, putting your variables in a character string like you would elsewhere, though there are good reasons for using arguments:

sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID) Values ('" + DataSubmitted.New_Code + "'," + DataSubmitted.Qty + ",'" + uuid + "')"
flag = cn.execute(sqlCommand)"

Note the single quotes--I'm assuming here that new code and uuid are character types and quantity is numeric.

That's to say you can hardcode things, even with arguments. So you may at some point want to do something like this (note the "Status" "Active" part):

sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID,STATUS) Values (:New_code,:Quantity,:Uuid,'Active')"
flag = cn.execute(sqlCommand,args)

[richardurban]

Re: Insert statement for SQL

The

function itemadd as c (e as p)with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""

dim uuid as c
Request.Variables.uuid=uuid 
Dim cn as sql::connection 
Dim args as SQL::Arguments

args.set("New_code",e._currentRowKeys[1])
args.set("Quantity",e._currentRowDataNew.Qty)
args.set("Uuid",uuid)
flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c
sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID) Values (:New_code,:Quantity,:Uuid)"
flag = cn.execute(sqlCommand,args)


e._set.QTY.value = "0"

end with
end function

fails to retrieve the uuid cookie, although I see in Firebug that the cookie is there. Or perhaps the syntax is wrong for writing to the SQL table. Does anyone know why it does not work? I am using this on a button that makes an Ajax callback and opens a grid in a pop up window.

[GGandhi]

Re: Insert statement for SQL

Request.Variables.uuid=uuid

i have not done much with page variables or with cookies yet, but
do you think you should assign the uuid
as
uuid = Request.Variables.uuid
instead, so it will pick up the value?

[JerryBrightbill]

Re: Insert statement for SQL

First, your function syntax is incorrect, and the function won't run

function itemadd as c (e as p)with PageVariables

Should be

function itemadd as c (e as p)

• If the function is called by an Ajax Callback, then Result.Cancel = .f. and Result.ErrorHTML = "" have no meaning in the context.
• The code is also trying to set the value of the request variable from Uuid, instead of the opposite (Request.Variables.uuid=uuid ).
• The cookie value as well as the "e" values are all character, so they must be converted to the proper data type when setting the arguments.
• All values should be passed as arguments and any errors should be trapped and sent back in an alert message.
• You should ALWAYS close a connection when processing is completed.

Assuming that Uuid is a uniqueidentifier, and quantity is a number, this would be a starting point.

function itemadd as c (e as p)
dim uuid as c
'--------- Check if cookie exists and set Uuid FROM the request variable. Trim any leading and trailing spaces so the value will be a valid uniqueidentifier
if variable_exists("Request.Variables.uuid")
	uuid  = alltrim(Request.Variables.uuid)
end if 
Dim cn as sql::connection 
Dim args as SQL::Arguments

args.set("New_code",e._currentRowKeys[1])

'--------  Set Quantity argument to numeric
if variable_exists("e._currentRowDataNew.Qty")
	args.set("Quantity",convert_type(e._currentRowDataNew.Qty,"N"))
else
	itemadd = "alert('Quantity value was not available');"
	exit function
end if
'--------- Check if Uuid is a uniqueidentifier and throw error and stop processing if it isn't.  
if isNonNullGuid(uuid)
	args.set("Uuid",convert_type(uuid,"K"))
else 	
	itemadd = "alert('UUID value was not available');"
	exit function
end if

flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c
sqlCommand = "insert into cust_ord_web (NEW_CODE,QUANTITY,UUID) Values (:New_code,:Quantity,:Uuid)"
flag = cn.execute(sqlCommand,args)

'---------- ALWAYS close the connection
cn.close()

'---------- Execute failed, so send back the CallResult error text
if Flag = .f.
	itemadd = "alert('"+js_escape(cn.CallResult.text)+"');"
else
	e._set.QTY.value = "0"
end if
end function

The function prototype comments also show a way to see what values were sent to the function

'itemadd = "alert('You submitted a value of: " + js_escape(e._currentRowDataNew.lastname) + "');"

Using that syntax, for testing you could just have this at the start of the function to send back an alert box with the values.

'------------- Special section to validate passed in Values, but not run the insert
dim uuid as c
dim cQuantity as c
dim Values as c

'--------- Check if cookie exists 
if variable_exists("Request.Variables.uuid")
	uuid  = alltrim(Request.Variables.uuid)
end if 
if variable_exists("e._currentRowDataNew.Qty")
	cQuantity  = e._currentRowDataNew.Qty
end if 


values = "Cookie: "+Uuid+crlf()+"New Code: "+e._currentRowKeys[1]+crlf()+"Quantity: "+cQuantity
itemadd = "alert('" + js_escape(Values) + "');
exit function
'------------ End Special section

[richardurban]

Re: Insert statement for SQL

Thanks so much. Yes, I initially copied the code from a server side event, so thanks for pointing out the wrong syntax and other errors. The Quantity and New_code values are working OK and are being written to the SQL table with the code as it is written, but the uuid value is not.
Using the code above, the uuid is now being written to the SQL table. However, I did not use the code to check for a unique uuid type value, as the value i use does not include the "-" separator, and therefore caused an error to be falsely generated for that test. The main problem was the reversed uuid variable definition, although a smilarly reversed definition used to work somehow in the server side.

[richardurban]

Re: Insert statement for SQL

I am having trouble understanding the correct way to formulate and display in the browser the call result for SQL commands when there are errors in the in the after dialog validate event in a dialog.
Would something like this, taken from an older thread work, or is this the wrong context or syntax:

flag = cn.Execute(sqlInsertStatement,args)


dim lcCallResultText as C

lcCallResultText = "\"alert('"+js_escape(cn.CallResult.text)+"');\""

if flag = .f. then 
		aa = e.rtc.A_AjaxResponses
		aa[].text = eval(lcCallResultText) 'displays the message returned by sql server
		aa[..].id = 100  'the order in which AjaxResponses are sent to the browser

else
	if cn.AffectedRows() = 1 then 
		aa = e.rtc.A_AjaxResponses
		aa[].text = "alert('Notice, Record was creadted.');"
		aa[..].id = 100  'the order in which AjaxResponses are sent to the browser

	else
		aa = e.rtc.A_AjaxResponses
		aa[].text = "alert('Notice, Record was not created.');"
		aa[..].id = 100  'the order in which AjaxResponses are sent to the browser

	end if

[richardurban]

Re: Insert statement for SQL

From Steve Wood in another thread, use this to see the SQL results written to a file:

save_to_file(cn.callresult.text,"c:\errors.txt") ' writes "Success" or an error message if it failed.

Make sure that you have write permission to the directory where you are saving the errors; you may not be able to write to the C: directory, so in that case use another location.
To complete this discussion, can anyone give the correct method for sending the cn.callresult.text content to the browser in the server side after dialog validate event of a dialog?

[Jim Coltz]

Re: Insert statement for SQL

To complete this discussion, can anyone give the correct method for sending the cn.callresult.text content to the browser in the server side after dialog validate event of a dialog?

The way to send back JavaScript is by setting the e.javascript property.

What I do is add a message box to the e.javascript property to show what the message is.

Simple method. "some_error" is a logical variable flag that gets set when there is an error.

if some_error then
     e.javascript = "alert('"+js_escape(cn.callresult.text)+"');"
end if

Better method since when using JavaScript alerts the user may check the "don't show again" option in some browsers and your message will then not be seen.

if some_error then
     e.javascript = "A5.msgBox.show('Error','"+js_escape(cn.callresult.text)+"','o');"
end if

[richardurban]

Re: Insert statement for SQL

Thanks, Jim.
Richard Urban