MySQL Forums
Forum List  »  Newbie

Foxpro Environment to MySQL in queries
Posted by: Jean Peter Paredes
Date: March 14, 2023 09:21PM

i am using Visual Foxpro and often times i extract parent tables from MySQL into a cursor in Visual Foxpro.

now the problem sometimes is that i needed to query a separate child table with matching ID from the Parent Table already in Visual Foxpro Environment which MySQL of course cant see cursors from that environment.

so what i would do normally is gather all the ID from the Parent table in Foxpro and put it in a variable that i can later use in my Query for the child table with the use of the "?varname" ... the issue with this is there might be a character limit in foxpro for variables and so bigger parent tables might have part of it not included in the variable.

next option, using foxpro i would create a temporary variable in MySQL, scan the parent table in foxpro and 1 by 1 concat the ID to the variable in MySQL to create a comma separated value of all parent IDs stored in a variable in MySQL, then in my Query for the child table i can filter it using FIND_IN_SET( @varname ) .. the problem is sometimes, depending on the size of the table, this is very SLOW.

so i would then try using IN as Select * from childtable where ID IN ( @varname ) but IN cannot seem to see some of the IDs in the comma separated value variable in mysql, there seems to be a limit.

tried also creating a temporary table in MySQL holding the filtered IDs from the Parent table then using Select * from childtable where ID IN ( select ID from tmpParentTable ) but this is also slow depending on data size.

i find that im running out of good options for an optimized way to filter result of a query for a Child Table in MySQL filtered by columns from a table that exists in Foxpro environment only.

any suggestions ?

Options: ReplyQuote


Subject
Written By
Posted
Foxpro Environment to MySQL in queries
March 14, 2023 09:21PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.