insert record into remote sql database

[Flommer]

The frustration continues... I am trying to use a dialog component to insert a new record into a sql server DB. I am getting a "result stack underflow" error when submitting the dialog. Per advice from the forum, i tried to use the insert xbasic script genie to create the code for the aftervalidate event. Here is the result:

'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::MAXXVMFG")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if

'Specify that we are using Portable SQL syntax
cn.PortableSQLEnabled = .t.

'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments

args.add("TYPE","'M'")
args.add("PART_ID","'t'")
args.add("PART_DESCRIPTION","'g'")
args.add("PART_REVISION","'f'")
args.add("WO_BASE_ID","'v'")
args.add("WO_QTY",5)

dim sqlInsertStatement as c 
sqlInsertStatement = <<%txt%
INSERT INTO [Z_CIR]  (TYPE, [PART_ID], [PART_DESCRIPTION], [PART_REVISION], [WO_BASE_ID], [WO_QTY]) VALUES (:TYPE, :PART_ID, :PART_DESCRIPTION, :PART_REVISION, :WO_BASE_ID, :WO_QTY)
%txt%

dim flag as l 
flag = cn.Execute(sqlInsertStatement,args)
if flag = .f. then 
	ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
else
	if cn.AffectedRows() = 1 then 
		ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL)
	else
		ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL)
	end if 
end if 

	
'Table has an auto-increment field. Get the value that was assigned to this field and store it in
'a variable.	
dim LastIdentityValue as a
if cn.AffectedRows() = 1 then 
	LastIdentityValue = cn.LastInsertedIdentity()
end if 
'Now, close the connection 
cn.close()

The values in the args.add lines are the only things I have tried changing. I actually want the currentform.controls variables here ( args.add("PART_ID",currentform.controls.part_id)?? ), I think, but I am not sure of the syntax. The genie does not make these control variables available when mapping the columns. I tried these simple defined values just for testing as well as control variables but the same error pops up. I really have no idea what is causing the error. Any advice would be great

Thanks
Erik

[Lenny Forziati]

Re: insert record into remote sql database

You can't use ui_msg_box() in the Application Server.

[Flommer]

Re: insert record into remote sql database

Ah... So the genies aren't aware of which enviroment they are coding for? I actually am finding it quite confusing figuring out from the documentation what is usable where...

I'll take out the error messaging stuff and try again..

Thanks

Erik

[Al Buchholz]

Re: insert record into remote sql database

sqlInsertStatement = <<%txt%
INSERT INTO [Z_CIR] (TYPE, [PART_ID], [PART_DESCRIPTION], [PART_REVISION], [WO_BASE_ID], [WO_QTY]) VALUES (:TYPE, :PART_ID, :PART_DESCRIPTION, :PART_REVISION, :WO_BASE_ID, :WO_QTY)
%txt%

Doesn't TYPE need to be [TYPE]?

[Flommer]

Re: insert record into remote sql database

I noticed that too and thought is was strange. The only thing unusual about that field is that nulls are not allowed. The lack of brackets is how the genie produced it and it seems to work..

Removing the ui_msg_box items allowed me to insert records when using

args.add("PART_ID","'t'")

but it still doesn't work when trying

args.add("PART_ID",currentform.controls.part_id)

the part_id control is a character variable.. Does it need quotes?

As a side note, I have not published the web app so I am only testing in live preview. Maybe that affects how the dialog variable becomes available..

More likely it is my misunderstanding of the required syntax..

Erik

[JPMG1]

I have a similar situation where I want to insert all records in current table filter into existing remote SQL table.
Tried manipulating the Xbasic of an existing insert action script .
Would really appreciate any help on this one

thanks

[madtowng]

It should be fairly simple to dump all the records into a csv file, then do a load data local infile into the remote table.