Need Help to use TableUpdate with odbc to I Series 2

[PatMcLaughlin]

I am trying to create a process that will send an 8 character string to an I series database. I created a Remote View called "jtview" and tied it to a connection called JT2GOCONN. In the Connection Designer - JT2GOCONN I selected the Data Source, entered the Userid and Password and the Database Name. I set Display ODBC login prompts to [Never] and took the defaults for all other settings. I clicked on Verify Connection and the connection was successful. In the View Designer, I designated the table, chose * as selected fields. Under the Update Criteria Tab, I put a check mark in the write column as well as the key column under Field Name JTID (the name of the field in the database).

My script reads as follows:

m.jtid = "bbc40105"
   INSERT INTO jtview FROM MEMVAR
   BROWSE
   TABLEUPDATE(.T., .T., 'JTVIEW')
RETURN

Now my problem... If I open the foxpro 9 program and run this, it cannot find jtview. If I go to the Project Manager|Data|Remote Views|jtview and press the Browse button, it will display the table and now my script will run without error. Then all at once I start getting an error (at this point it seems to start at random) as follows:

Connectivity error: [IBM][iSeries Access ODBC Driver][DB2UDB]SQL0803 - Duplicate key value specified.

Once this error message appears, I have to go to the Windows Task Manager to End the Foxpro9 program. Now I am back to square one.

Again I am in over my head and do not know what to fix where. Can you point me in the right direction?

 

[Dan Freeman]

For finding the view, the database containing the view has to be open. (Expanding it in the project manager's treeview opens it in an interactive session.)

OPEN DATABASE YourDatabase

You can control how the update statement sent to the back end is constructed in the view designer. It sounds like you want "Updated fields only".

 

[JRB-Bldr]

Assuming that you do indeed have the associated Database open you should first just try to BROWSE the Remote View.

You should be able to do this from the Command Window.

Now again assuming that you can BROWSE the Remote View, then things are OK at the most basic level.

Now if you get an error message "Duplicate key value specified" then your backend data table probably has a field which is set to be Unique in some manner and you are attempting to INSERT a value into the field which already exists so is dis-allowed.

Check through the backend's 'native' interface for the existence of the field content that you are attempting to INSERT.

Good Luck,
JRB-Bldr

 

[PatMcLaughlin]

Thank you both as the "Open Database" took care of the initial problem and jrbbldr's explanation is exactly true, which adds a new problem. Since the data sent from the front end will from time to time match the data on the back end, (which we do not want to duplicate) is there a way to filter the data or ignore the error message within the code?

 

[Mike Lewis]

Pat,

You need to add some error-trapping to your VFP code.

Firstly, change the second parameter of your TABLEUPDATE()from .T. to .F. This will cause the function to return .F. if the update fails (because of a duplicate key value or any other reason).

You can then call AERROR() to find the cause of the failure. If the error code is 1526, it means that it was the back end that generated the error. You can then find out what the error was, and take action accordingly.

An example:

m.jtid = "bbc40105"
INSERT INTO jtview FROM MEMVAR
BROWSE
IF NOT TABLEUPDATE(.T., [b].F.[/b], 'JTVIEW')
  AERROR(laErr)
  IF laErr(1) = 1526
    * The back end reported an error,
    * laErr(3) will contain the error message, and
    * laErr(5) will contain the error code
  ENDIF
ENDIF

I can't tell you the error code that the back end will return, as that is vendor-specific. But if you can find the code for "Duplicate key value specified", you can take the approopriate action.

Hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[PatMcLaughlin]

Mike: I ran your code and trapped the error code -803 But at this point it appears to be too late in the code to do anything as it has the foxpro locked up. You cannot even quit foxpro without using the Task manager and End Program. Do I need to search the backend data first before I Insert the data into the view from MEMVAR?

 

[Mike Lewis]

Pat,

There's nothing in the code I gave you that would cause VFP to lock up (although it might be a good idea to remove the Browse, but that probably isn't the cause of the problem).

If you know that it's doing the AERROR stuff, the locking up must occur after that.

Yes, you could search for the ID before sending the INSERT, and refrain from searching if it is found. But you can't rely on that. There's always the chance that another user might insert the same ID between your search and your insert (very remote possibility, I admit).

If you want to do that, the easiest way would be to call REQUERY() to refresh the remote view, and then seach the view for the ID.

But I think it would be better if you found the cause of the locking up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[PatMcLaughlin]

Again you are a fantastic asset! Yes I can get the AError stuff but once this error has been triggered, the program will lock up.
What I did was add a WAIT WINDOW (laErr(5)) after your line AERROR(laErr)

Your code is the entire script so as you can see, nothing happens after this process except it is like the connection will not process any data but it will not release or close the connection.

As for other users accessing this while my process is in use, My process is the only one which will have full access. All other users are read-only so I feel comfortable at searching first. In fact since the downloaded data (even though it may match data on the back side) will all be unique for each session this runs so if I am understanding the help files correctly I could use the "AGAIN" clause in the query. (I'm a newby and have not tried this yet).

This was my first try at doing this...

numsearch = "bbc40109"
use jtview
INDEX on jtid TO jtview
LOCATE FOR jtid = numsearch 
IF FOUND()
   WAIT WINDOW "Found"
ELSE
   WAIT WINDOW "Not Found"
ENDIF

There will be hundreds of entries each time this process runs so will need to check each one if this is how I go.

 

[JRB-Bldr]

"is there a way to filter the data or ignore the error
message within the code"

If you wanted to handle this situation by skipping over the new INSERT value, then what I would do would be to run a query on the recipient table BEFORE the INSERT to see if the value already existed.

If not then execute the INSERT
If so, then skip over it and not attempt an INSERT.

If you wanted to handle the situation in a different manner, then we would need more info on what you wanted to do.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Another approach would be to run an initial query to create a VFP cursor 'image' of the recipient table.

Then build an Index on it around the suspect field(s).

The run a comparison of your new values with the existing values by either establishing a RELATION or running a SCAN/ENDSCAN.

If duplicate values were found, eliminate them from the new INSERT data records and run the INSERT on what remains.

That would be quicker since it should only involve a single pre-query to remove ALL duplicates, followed by your INSERTs instead of a pre-query before each and every INSERT.

Good Luck,
JRB-Bldr

 

[PatMcLaughlin]

Your second approach is most likely the one I should take, but I have no idea how to create a cursor from the remote data or view. Being a newby at Fox programming I understood part of what you said. i.e. I have no idea what a RELATION is or how to use it. I have used SCAN/ENDSCAN and can see where I could search it for each record using a loop. You guys are fantastic for your trying to help!!

 

[Dan Freeman]

The remote view *is* a cursor.

(Cursor, in this context, is an acronym for CURrent Set Of Records.)

If you index the cursor (view), you do not need LOCATE. SEEK will likely be faster.

HOWEVER, the rule of thumb when working with remote data is to limit the data retrieved (in a view or otherwise) to the minimum possible. It's kinder on both your back end and your network in general.

You should probably have a "duplicate checking view" which is parameterized on the key which is causing your conflict. Before attempting an insert from your main view, set the "duplicate checking view" parameter to your "new" key and requery() it. If it does not return a record, your insert will likely succeed.

See the help file for parameterized views for more information.

The lockups you're seeing are unusual, and probably caused by the ODBC driver you're using. They're often as flaky as printer or video drivers. :-(

 

[Mike Lewis]

Pat,

I don't usually disagree with JRB-Bldr, but in this case, I wouldn't go the route he is suggesting.

When I advised you to requery the existing remote view, I was taking into account that you have not yet had much experience in this area, and that you were looking for a solution that you could achieve easily.

Basically, I am suggesting that you stay with remote views, because you already know how to use them.

Your aim is to find if a given key field already exists on the back end. All you need is a view that contains records containing the key in question. You might be able to use your existing view for that, or you can create another one very easily. It just needs one field (the ID) and it needs a filter condition that fetches records containing the key.

Once you have the view, you would USE it (or REQUERY it if it is already open). That way, you will get the most up-to-date data. You would then do a simple LOCATE FOR to find if the ID in question is present.

Although most of us here would probably do this differently, I feel that this is the simplest solution, which is why I am suggesting it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Mike - No problem.

Pat - if you are going to stay with using the Remote View then if you are doing a number of sequential INSERTs ("hundreds of entries...") you might try something like:

* --- Make Sure Your Remote View jtview Is Set To Be Updateable ---
USE jtview IN 0
SELECT jtview
INDEX ON jtid TAG jtid  && assuming that jtid is the duplicated field

SELECT NewData  && table/cursor with ALL new entry data
SCAN
   * --- Get New Data Into Memory Variables (m.whatever) ---
   SCATTER MEMVAR

   SELECT jtview
   =REQUERY()  && Update The View Records
   SET ORDER To jtid
   IF !SEEK(m.jtid)
      * --- Not Already There, Insert New Value(s) ---
      INSERT INTO jtview FROM MEMVAR
      =TABLEUPDATE()  && 'Push' New Data To Backend
   ELSE
      * --- Already Exists ---
      <do whatever you need to do>
   ENDIF

   SELECT NewData
ENDSCAN

NOTE - if your Remote View does not have the appropriate Updateable settings, no new data will be retained.

NOTE 2 - the only reason that I have shied away from using the Remote View approach is that, depending on the size of the remote View Cursor returned, you sometimes have to add extra code after a REQUERY() to check if the Remote View Cursor is still 'busy' collecting records from the backend. If the Remote View Cursor has only a few records, then this is not generally a problem.

NOTE 3 - the above is merely one of many approaches to checking for Duplicates prior to executing your INSERT.

Good Luck,
JRB-Bldr

 

[PatMcLaughlin]

You guys are fantastic! It is running the script as we speak and all seems to be working... (Taken nearly an hour so far to load the initial records) There are 910000 initial records. Before I started it, I added a line after the SCAN and before the SCATTER MEMVAR that said

m.jtid = jtdataload.jtid

While waiting for it to run I have been trying to use the Help files to better understand what you did. I now know a new command, SCATTER. (I did not need my addition.) Where I am the only one with access to the files right now, what does the REQUERY() do for me?

 

[Mike Lewis]

Pat,

Delighted to hear it's working (but better wait until it's finished before you celebrate).

The REQUERY() function simply refreshes a remote view. It goes back out to the back end, and re-fetches all the data. It's similar to what would happen if you closed the view and opened it again.

Don't confuse it with the REQUERY method in the listbox and combo box control. It just happens to have the same name.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[PatMcLaughlin]

It is finished and has transferred the data... It took over 23 hours to complete. Unfortunately the server had to reboot on the Iseries before it was totally finished leaving 435 records not transferred. I revised my code to not refresh the database before each search, created a new download database with only the missing records and transferred the last records in less than 15 seconds. I then created a cursor of the download data and did a scan and seek on each record to assure that all compared. (I would bet there is a better way to do that) This completed successfully in about 4 minutes and my program is now running happily along. (900+ new records added or updated last night without error). Thank you both for all of your kind patience with a novice! I am humbled by your knowledge and skill!

 

[JRB-Bldr]

If I had known that this was to be a one-time effort, I would have suggested another approach using APPEND FROM DBF()

Regardless, I am glad that things worked out for you.