Result Set Blank on Server

[Ted Thomas]

I have a UX component with 2 Xbasic functions. The first function processes MySQL records and calls the second function to process additional MySQL records. My functions work as expected when running in test (working preview or live preview). However, when I run the UX component off of the server, the result set that is used to process records in the second function is returned is empty. Both xbasic functions open a connection to the db and close the connection when done. Both functions use different names for the connection and result set.

flag = cn1.execute(sql,args)

if flag = .f. then 
	update_takeoff = "alert('Could not connect to database to Insert the Job Estimates. Error reported was: " + js_escape( cn1.CallResult.text) + "');"
	cn1.Close()
	end 
end if

dim rs1 as sql::ResultSet
rs1 = cn1.ResultSet

flag = rs1.nextRow()

while flag
	...process my data

	flag2 = cn1.execute(sqlupdate,args)
	
	if flag2 = .f. then 
		update_takeoff = "alert('Could not connect to database. Error reported was: " + js_escape( cn1.CallResult.text) + "');"
		cn1.Close()
		end 
	end if

	flag = rs1.NextRow()
end while

I can manually run the sql directly in MySQL without an issue. I can run the code from my ux component in live preview and the functions work. From some debugging by writing data to a file while running from the server, I know that the flag results for executing the sql is T. I also know that the result set is emplty. I cannot determine why the sql runs fine in live preview and not from the server.

If anyone has thought why the result set is empty on the server, please let me know.

[GGandhi]

Re: Result Set Blank on Server

my usual question is
the MySQL server for both preview and from the server are the same, i.e the connection string will be the same
if they are different then you will need to set the connection string alias to properly adjust to the new environment.
say for example for preview if you use localhost and for the server unless it is also localhost then you will need alias.( or a fixed destination for both )

[Ted Thomas]

Re: Result Set Blank on Server

I have the connection strings correct on server and dev environment. The first function does similar sql calls and works correctly, the second function does not return any results (so no records are updated). Unfortunately my issue is not the connections. It must be something in the sql statement that I am not seeing but it does not make sense that the sql works in live/working preview and not on the server.

[Ted Thomas]

Re: Result Set Blank on Server

I have narrowed down the problem and it seems to be a sequence problem.

If I try to update the records that were created in the first xbasic function with new values in the second function, the second function fails to find any results. However, if I run the second function by itself (I.e. directly from the UX - not being called from the first function), the records are updated correctly.

This Fails - Function1 does create records but function 2 does not update the records
Function function1 as c (e as p)

Open connection to sql data

insert into MySQL a header record
insert into MySQL detail records
update MySQL header totals based on detail records

Txt = function2 (ci, ji, est_id, takeoff_id)

End function


Function function2 as c (ci, ji, est_id, takeoff)

Open connection to sql data

Select MySQL detail records into a record set

while record set not EOF
Process record
update MySQL detail record
end while

update MySQL header totals based on detail records

End Function

This Works and updates records(calling function 2 separately not thru function1 where the records are initially created)

function fucntion3 as c (e as p)

dim ji as N = convert_type(e.dataSubmitted.est_takeoff_job_id,"N")
dim ci as n = convert_type(e.dataSubmitted.est_takeoff_company_id,"n")
dim takeoff as n = convert_type(e.dataSubmitted.est_takeoff_id,"n")
dim est_id as n = convert_type(e.dataSubmitted.take,"n")

function3 = function2(ci, ji, est_id, takeoff)

end function

[GGandhi]

Re: Result Set Blank on Server

this could very well be a dumb question.

I did not realize that you are working with UX component, if you are, why do you need to write all the code?
if you bind the tables, alpha will update without that many lines. if the tables are related lot easier even if not, then it is just as easy.
there are some videos in version 11 where they talk about inserting data in one table then another one, please take a look.
or if you post your table structure, someone will definitely help you sort out things.

also I see this is in desktop, is this correct?

[Ted Thomas]

Re: Result Set Blank on Server

If it were just that easy. The process that I have developed is to help estimators create estimates very quickly. The UX component is bound to a table the contains all the quantities required for an estimate. Once the quantities are entered, the estimator can select from different product templates and different pricing databases. Once the have this information selected, they click a button to create the estimate. The estimate is created using the quantities entered in the ux component. In addition, some quantities entered are used in a formula to determine the quantity to use in the estimate (i.e. some quantities are entered as exact measurement and then a waste factor is applied when it is used in the actual estimate).

I created the process in a UX component so that I can roll it out to mobile shortly so we can create estimate in the field in a matter of seconds. However, we plan to start using in in the office before we roll it out to the field.

[GGandhi]

Re: Result Set Blank on Server

yes it looks complicated.
if you are willing to share some table information people, more knowledgeable than I and more experienced than I would certainly help you, I am sure of this message board.

[Ted Thomas]

Re: Result Set Blank on Server

I found the problem but not the solution. I always thought that when you execute a MySQL statement from xbasic that the flag result of .T. meant that the sql ran and was completed. I was testing "flag2 = cn1.execute(sql,args)" and getting .T.. So thought that my records were inserted into the table and I could proceed to the next step.

Since the server does not execute debug(1) statement, the code runs much faster and the data in MySQL execute did not have time to get inserted (i.e. did not complete) and so the second function did not see any results. My quick fix was to pause my function for a few seconds using sleep(3.00).

The sql statement that insert the records looks like the following (with multiple sql statements being executed in one cn1.execute(sql,args) statement. I need to find out how to test that all the sql statements executed (I have not had any luck with cn.nextresult(). I know I can split the sql statement into multiple statement and execute each one independently (which is what I will end up doing). Thanks for our thought on this issue.

sqltxt = <<%sqltxt%
SELECT
   @cnt := (
      SELECT
         COALESCE (max(t1.job_estimate_id), 0) + 1 AS id
      FROM
         job_estimate_master t1
      WHERE
         t1.company_id = :ci
      AND t1.job_id = :ji
   );

INSERT INTO job_estimate_master .....;

INSERT INTO job_estimate_detail (company, job, ...)
   (SELECT   :ci, :ji, ... );
   
SELECT sum(detail_qty) ... INTO @subtotal, @tax, @cost, @grand, @opt FROM job_estimate_master
	INNER JOIN job_estimate_detail ...
        WHERE
		job_estimate_master.company_id = :ci
		AND job_estimate_master.job_id = :ji
		AND job_estimate_master.job_estimate_id = @cnt
	GROUP BY
	job_estimate_master.company_id,
	job_estimate_master.job_id,
	job_estimate_master.job_estimate_id;
	
UPDATE job_estimate_master ...
	WHERE
		job_estimate_master.company_id = :ci
	AND job_estimate_master.job_id = :ji
	AND job_estimate_master.job_estimate_id = @cnt;
%sqltxt%