Sql help. Please

[kstans]

I have the following code in xbasic. Field 1 updates with out a problem
Field 2 gives an error message. I've spent 3 days on this now and need another set of eyes.

if table.isopen("[PathAlias.ADB_Path]\used_inventory")
	a_tbl = table.get("[PathAlias.ADB_Path]\used_inventory")
else
	a_tbl = table.open("[PathAlias.ADB_Path]\used_inventory")
	meopen=.T.
end if


DIM a5_operation_order as C
a5_operation_order = ""
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = ".T."
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")

update.fields = 2
update.field1 = "CURRENTLOCATION"
update.expr1 = "if(Stock_number>\"\",if(sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\")>\"\",sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\"),Store_Location),\"\")"
update.field2 = "CURRENT_LOCATION_DATE"
update.expr2 = "if(Stock_number>\"\",if(sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\")>\"\",sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Stock_Date\"), Original_Stock_Date),Original_Stock_date)"
a_tbl.update()

[Stan Mathews]

Re: Sql help. Please

The expressions seem identical other than the field names. What error are you getting? Is the data type of CURRENT_LOCATION_DATE the same as Stock_date and Original_Stock_date?

[kstans]

Re: Sql help. Please

That is why it's driving me crazy. I can't find an error anywhere.

Yes the three fields are all Date fields.

Here is what I get

2003 - 'Can't connect to MySQL server on '50.97.68.28' (10048)'


If I change update.fields to 1 and rem out update.field2 and expr2 it works fine.

[Stan Mathews]

Re: Sql help. Please

If you swap out and just run the date update does it work? Might be a timing issue.

[kstans]

Re: Sql help. Please

I had not thought of that. I will try it and get back.

[kstans]

Re: Sql help. Please

That worked. So how do I put a delay in

[Stan Mathews]

Re: Sql help. Please

Don't think you can. May have to run them separately and sequentially.

You might try updating some other field, as #2, updating it with itself, and move the date update to #3. Just a thought.

You could always try reversing your #1 and #2 to see if that makes any difference.

[kstans]

Re: Sql help. Please

Ok I tried it with an additional update in between the 1 and 3 that didn't work

I reversed 1 and 2 updates. Same problem

I copied all the code and ran it twice with 1 update per. No go.

then I copied one of the updates to the top of the code and left the other at the bottom. (there are other updates and summary tables being created so it take some time)

that didn't work either.

is it not closing the sql connection between update fields? could that be it?

[GGandhi]

Re: Sql help. Please

i am just curious, i am sure there are lot of things i won't understand.
looks like you have a local dbf file and for updating you are doing lookup into a mysql table. may i ask you why?
is it possible to have active link table locally to the same mysql table and do the lookup to that table thus avoiding a call to lookup every time you need.
if that is not possible, then is it possible to write a call to the sql table and get the data into a variable and then use that variable for lookup? ( i think each look up is taking time and the table remains open for alpha to close eventually, that is what i am guessing)
i am just curious as i said earlier.

[Pat Bremkamp]

Re: Sql help. Please

Try putting the code sleep(0) between the updates.

[Stan Mathews]

Re: Sql help. Please

Could be worth trying

XBASIC_WAIT_FOR_IDLE()

if the sleep(0) doesn't help.

[Pat Bremkamp]

Re: Sql help. Please

Here is another thought. If you open the connection prior to the update statements, then you can refer to it in your sqllookup, then close it after. Then both statements will use that connection. For example something like
dim cnx as SQL::Connection
if cnx.open("A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'")
update.fields = 2
update.field1 = "CURRENTLOCATION"
update.expr1 = "if(Stock_number>"",if(sql_lookup(cnx,"vehicle_history","Out_date is NULL AND Stock_Number ='"+Stock_Number+"'","Current_Location")>"",sql_lookup(cnx,"vehicle_history","Out_date is NULL AND Stock_Number ='"+Stock_Number+"'","Current_Location"),Store_Location),"")"
update.field2 = "CURRENT_LOCATION_DATE"
update.expr2 = "if(Stock_number>"",if(sql_lookup(cnx,"vehicle_history","Out_date is NULL AND Stock_Number ='"+Stock_Number+"'","Current_Location")>"",sql_lookup(cnx,"vehicle_history","Out_date is NULL AND Stock_Number ='"+Stock_Number+"'","Stock_Date"), Original_Stock_Date),Original_Stock_date)"
a_tbl.update()
cnx.close()
end if

[Stan Mathews]

Re: Sql help. Please

That's a good one. I had never noticed that you could pass an actual connection pointer instead of just the connection string.

Yours is probably the best idea but for another approach, the updates could be separated into functions and called sequentially.

if upd1()
	'completed ok
else
	msgbox("Update 1 did not finish normally.")
	end
end if
if upd2()
	'completed ok
else
	msgbox("Update 2 did not finish normally.")
	end
end if
end

function upd1 as L ()
	upd1 = .F.
	if table.isopen("[PathAlias.ADB_Path]\used_inventory")
		a_tbl = table.get("[PathAlias.ADB_Path]\used_inventory")
	else a_tbl = table.open("[PathAlias.ADB_Path]\used_inventory")
		meopen=.T.
	end if
	DIM a5_operation_order as C 
	a5_operation_order = ""
	query.order = a5_operation_order
	DIM a5_operation_filter as C
	a5_operation_filter = ".T."
	query.filter = a5_operation_filter
	query.options = "I"
	query.description = "Temporary Query"
	i_indx = a_tbl.query_create("N",query.filter,query.order)
	update.fields = 1
	update.field1 = "CURRENTLOCATION"
	update.expr1 = "if(Stock_number>\"\",if(sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\")>\"\",sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\"),Store_Location),\"\")"
	a_tbl.update()
	upd1 = .T.
end function

function upd2 as L ()
	upd2 = .F.
	if table.isopen("[PathAlias.ADB_Path]\used_inventory")
		a_tbl = table.get("[PathAlias.ADB_Path]\used_inventory")
	else a_tbl = table.open("[PathAlias.ADB_Path]\used_inventory")
		meopen=.T.
	end if
	DIM a5_operation_order as C 
	a5_operation_order = ""
	query.order = a5_operation_order
	DIM a5_operation_filter as C
	a5_operation_filter = ".T."
	query.filter = a5_operation_filter
	query.options = "I"
	query.description = "Temporary Query"
	i_indx = a_tbl.query_create("N",query.filter,query.order)
	update.fields = 1
	update.field1 = "CURRENT_LOCATION_DATE"
	update.expr1 = "if(Stock_number>\"\",if(sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Current_Location\")>\"\",sql_lookup(\"{A5API=MySQL,Server='xx.xx.xx.xx',Port='3306',UserName='xxxx',Password=':xxxx',Database='autocross'}\",\"vehicle_history\",\"Out_date is NULL AND Stock_Number ='\"+Stock_Number+\"'\",\"Stock_Date\"), Original_Stock_Date),Original_Stock_date)"
	a_tbl.update()
	upd2 = .T.
end function

[kstans]

Re: Sql help. Please

Thanks Pat. Opening the connection first with cnx.open worked.

I didn't know you could do that.
Thanks again.

p.s. it's also faster