File Choosing Manipulation (Conceptual Design) 1

[Scott24x7]

Hi All,
So I've reached an important stage in the build of my application, one that I've been putting off for a while, because I knew it would be a bit complex, potentially messy, but I thought I would seek the guidance of the Tek-Tips community on this subject, as I am sure others have done this kind of thing before.

Let me start off by mentioning that, years ago, after a great deal of experience in OLE embedding (especially graphical items, though these days I have a lot bigger issues), found that Fox tables were exceedingly subject to corruption. So that issue still sits with me. I spent months working with tables that had embedded image data into General fields and they just corrupted all the time. Particularly if you were moving the files around.

I eventually went to a linked model using Memo fields to contain the path and file name to a file saved in the working directory. That meant copying the file into the directory first and then "Picking" the file (image only) data which it would just embed the path to the file (which were all saved under the \<application Name>\Graphics\<source of image>\<Filename.ext>

As I am now planning to associate "lose documents" with various sources within the database (and I mean that) I want a new strategy.

The idea is to "pick" the filename from where ever it sits (assuming regular directory navigations, or networked drives, etc) and when the file name is "Picked" it's actually copied into a single directory which will be: \<Application>\DOCUMENTS.

Since I just need a "container" for documents that will get referenced by Memo field as a linked object, I don't really care that they are "cleanly" grouped into different directories. I understand there could be conflict between matching file names, but for now I don't want to manage those in the application, I will force users to make the name unique at the time they select it for including if it already exists.

The question is... how to best go about this process? I'm open to someone's "library" if they have such a thing, or 90% solution where the codes is still open to modification so I could tailor it. I'm also fully willing to write the full thing, but some of that manipulation (especially the "copy a file from one place to another, and then link the directory/filename to Memo file" seems a bit sketchy. (Lot's of FOPEN() calls...)

Is there an elegant solution to this?
Suggestions?
Is ok to tell me to go figure it out myself. Just hoping to cut my development time and learn curve as much as possible.
Thanks to you all in advance.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Gagnon]

found that Fox tables were exceedingly subject to corruption.

Move to SQL backend to resolve that.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Scott24x7]

C'mon... that's really not an answer.
Not an option for me either.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Indeeed MSSQL has a good thing with filestreams and it's not impossible to incorporate an express version for storing files in it.

But it's fairly easy to COPY FILE, isn't it?

If you want OS support for coppying with a bit of safety against file corruption or copying in a restartable way, there is MoveFileEx for that. It also has a flag/option to fail in case the file already exists.
What you can easily do is rename files with something like SYS(2015) or a autoinc number and storing the original name, too. That of course makes it less easy to detect already existing files, but in the end it's just a lookup of the filename part of the full filename, which is JUSTFNAME(), so that's fairly easy too, even if you rename files inside the database directory.

What are further mental blocks and questions and concerns you have with it?
In short you could do:

#Define ccDocumentBaseDir "D:\Yourapp\Documents\"
lcUserpickedFile = GETFILE()
If !Empty(lcUserpickedFile) AND ADIR(laDummy,lcUserpickedFile)=1
   * The user picked an existing file and no CANCEL or ESC was used out of the GetFile dialog
   lcDestinationFile = Addbs(ccDocumentBaseDir)+JustFName(lcUserpickedFile)
   If ADIR(laDummy,lcDestinationFile)=0
      * destination file name is not yet used, so we can copy
      llStore = .F.
      Try
         COPY FILE (lcUserpickedFile) TO (lcDestinationFile)
         llStore = .T.
      Catch
         * something went wrong in copying the file. llStore is still .F.
      Endtry
      If llStore
         Insert Into yourDocuments (filename) VALUES (lcDestinationFile)
         Messagebox("The file you picked was added to the database.")
      Else
         Messagebox("Something went wrong, try adding that file again later.")
      Endif
   Else
      Messagebox("That file already was put into the database documents section and therefore is rejected. Try to add it renamed.")
   Endif
Endif

Bye, Olaf.

Edit: Noticed I could add the INSERT-SQL and final messageboxes in the corresponding Try or Catch Block, but I tend to just set a logical flag variable for separate evaluation, as extending the TRY block could lead to landing in the catch block for various other reasons than you care for and you can expand on the catching of very specific cases better, when separating the finalisation. What I could have done is using the FINALLY section for this. Anyway, this is not full blown code.

 

[Dan Freeman]

First of all, you're not Linking (the L in OLE) at all so you'll do yourself a favor by not calling it that. It only confuses the discussion because someone coming into the middle may think you actually mean Linking.

You're storing a file path/name. And that's a perfectly valid approach. It is, in fact, the approach generally recommended.

The problems with General fields are many. It's based on the OLE 1.0 standard which was only barely useful when it was released with Windows 3.x. Once the data goes in you can't get it out easily. The list goes on.

I've always found DBF files otherwise to be as stable as the environment you put them in. I have personally never experienced corruption. I know it happens! I've had clients who could seemingly corrupt them at will. But I have personally never experienced it, and when clients' environments are stabilized they tend to have better luck.

 

[Olaf Doschke]

Having given that sample, there's more to it SQL Server can handle quite easily, eg versioning of same document, so take a look at MS SQL Server.
See here for further information about making that decision:

https://msdn.microsoft.com/en-us/library/ff929144.aspx?f=255&MSPPError=-2147217396

Bye, Olaf.

 

[Mike Gagnon]

C'mon... that's really not an answer.
Not an option for me either.

Funny, Olaf seems to think it is.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Scott24x7]

Yeah, I can't go doing a full port to a DB system I have no experience in. I really prefer to keep this in VFP... there's a reason I've chosen it, so can we just drop any discussion about SQL Server, as it's really not something I'm going to pursue. Appreciate that you find it an elegant solution, but it's just not a path I'm willing to go down at this stage.

If I wanted to use SQL Server, I'd have built a .Net interface over it, and not taken the advantage of the seamless VFP advantage.
I really don't have a need to save the files within the database tables. Living as they do as a reference in a directory is really fine. I'm not even that worried about file name clashes, because the data that is expected will be highly unlikely (even if we had a million files, which we won't) to have the same file name. And yeah, I thought about checking filename, if existing adding some random value to it as an option, and then saving it. The intention isn't to actually reduce or prevent identical data being saved. (There is a long list of reasons for this which I won't go into here). Portability and backup are also simplified in some cases, and even access to the reference files without the application actually running are also useful.

I guess I should have been more careful with my initial statement about Fox tables being susceptible to corruption, and made that "Fox Tables using General Fields and OLE embedding is exceedingly susceptible to corruption, and therefore, I've already ruled that possibility out". Not that I actually NEEDED to store files there.

Dan: Never experienced a corruption... wow, you are LUCKY. I (as well as many clients) have experienced them over the years. Most of the clients that had issues were actually applications that were written by someone else, and I agree, once we get the code bases stable, they were fine. The only real corruption issues I faced (while building and testing) were the result of General fields, or loss of power in midst of a table open (especially if it's being written to). Those are the worst. Lot of times unrecoverable, without rolling back to backup, and that could be a big mess for operations, but I've seen it happen.

So, thanks to all for the suggestions to date, I will try to incorporate them into an interface that works in the application I've been constructing, and may return for other insight if I hit a road block.
Many thanks.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Who asked you to convert all your data? You could use the SQL DB to use its FileTable instead of a directory, but additional to all your other data.

Bye, Olaf.

 

[Scott24x7]

Doesn't that get messy having 2 DBMS?
I really don't know SQL Server. I use SQL calls in Fox, but I was of the impression SQL installations require deeper administration knowledge (at minimum) and a full-time administrator for larger implementations.
Though that said, I don't really need them to be stored in the table. File reference are fine.
I've set up another "meta-data" form for the files, which will give me a lot of information about them, and capture their extension which I then will use SHELLEXECUTE to launch them later. I've already got something similar for my topline menu that lets me put "PDF references" in a directory, and it dynamically reads them, lets you launch a "reference" easily that way.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

The fine thing about the SQL Server FileTable is, that the files are in a normal filesystem folder, but managed by monitoring processes, they fall under tools of the SQL Server, like backup and restore and you get meta file informations as SQL Table data for free, even if you just copy files into that folder. It's less work.

Two RDBMS are no problem you can have as many connections to remote databases open and as many DBC open by OPEN DATABASE or DBFs by USE of them, as you like.
Queries joining tables are coming with a price, of course, but you gain more advantages in my eye.

You can also install an Express version silent and need no further administration of the one FileTable or more data, the administration of it can be as low as defininig rights, that has to be done for the folder of your DBFs, too, if the customer don't want everybody having access, besides that SQL Server offers lots of adminitrative tools, which go beyond what DBF files can do.

If you never dip your toes into new water you never can see how wonderful it's to swim in there. Maybe you're really too far off of todays IT needs to want renovation.

Bye, Olaf.

 

[Scott24x7]

Hi Olaf,
The concept is intriguing, but I'm in the unusual position that I am the client...
This is part of my core business system, and will separate my company from my competitors.
The big issue is, speed to market. And I'd rather have (for now) an application that I can manage, and build fast, vs the longer term benefit of the discovery and manipulation of SQL Server. So it's less a matter of will and more a matter of skill, which I am being realistic with myself about. For me right now, managing and manipulating the information is more important than a specifically elegant solution. I hate that I've made that decision, but I am sticking to it, because I have people I'm responsible for, and I have to consider that.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Just pointing out the beauty of the FileTable, as it's working two-ways, even if users put in files directly.
It's your decision to take it or leave it due to the learning curve. I understand your priorities, I do have about the same when it goes about legal software or not, so of course I understand that reasoning.

Bye, Olaf.

 

[Scott24x7]

This wound up being very tricky... I had to move away from INSERT INTO and use instead UPDATE. It was very weird. I thought INSERT INTO would update an existing record, but it kept creating a new record every time. I also discovered that UPDATE strangely advances the record pointer to the next record in the DBF (or at least with the relationships I have set on, but that in itself was surprisingly odd.)

I've got 2 versions of this, though I may consider parameterizing it and making it one PROCEDURE where the number of parameters drives how it behaves.

(There are some instances where I don't care, or the images are in a fixed directory only, so picking them doesn't require a file copy, just updating the Memo field (like business card image that I have in one form). It does all the same thing except the copy of the file to a fixed directory, because in this case, the files will already be in the correct directory.

I suppose the easy thing to do would be to just create a "llCopyFile" parameter that is .T. if you want the file to be copied/checked for duplicate, and .F. if no copy is to take place... then everything else is the same... might give that a whirl.

I really appreciate this. This is so much more elegant than what I had done in the past, which was to create a very elaborate screen where you would sort through images, then drag and drop them once you found the one you wanted. It was kind of clunky. Looked cool, but messy for the user.

Still working on this, but it's now coming together. The "interface" in general as a rule where images are involved in a record, you right click the image on the screen (defaults to white box) and then select the file (GETFILE()) manipulate the DIR string a bit (I always take out drive letters and just work from "Root" forward, that way if drive letter changes, or the application gets moved, the references still work, so long as the DIR structure doesn't change, but that should be followed as well anyway...)

Thanks all.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

What? INSERT INTO is the SQL command to INSERT a new record intoa a table. The SQL command to update a table is called UPDATE.

I did use it, as sample code, assuming you have no record for the file copied every case you do copy. That's why the INSERT only is done, once the non existance of the file in the destination folder is checked and no error occurd while copying (e due to file being in exclusive use, permissins missing, etc).

No problem, but any SQL is working on the table as a set, not via the record pointer. An Update surely causes a relation to react, what do you expect?

In regard of a file picked from the directory itself, this would lead to the branch of rejecting the file, as it's picked from the destination folder, so it's found in there. So there is no problem with that, too, unless you rewrote the code to do something else.

Bye, Olaf.

 

[Scott24x7]

Ah, I see, like I mentioned, I'm still rusty, and I remembered there being something odd about it. The Memo fields were giving me a problem as well.

I was confused, thinking that INSERT INTO was to update a field in a table and INSERT was for creating a new record. But the Update thing worked, they just have very different syntax.

Basically for my use the file will always get added after the record has been created, because the file is either a) an image that goes with a type of record (like a business card image, or a floorplan image), or b) will be a document that has a lot of metadata associated with it, which will be added after the record is created and the document is then selected. So as a matter of process flow, I won't need to insert the record at the time of picking the file.

The bigger thing that surprised me though is that the UPDATE advances the record pointer. Very strange, or is that common? (I thought it might be related to the relationships I have set on the table, but not sure).


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Well, if you want to make a change to the current reccord you could also use REPLACE.

You don't post your update statement, but in general it will put the record pointer anywhere, SQL commands are not record pointer oriented at all.
Let me give it a try...

Clear
Cd (GetEnv("TEMP"))
Create Database Test
Create Table UpdateTest (id i, cdata c(10))
Insert into UpdateTest Values (1,Sys(2015))
Insert into UpdateTest Values (2,Sys(2015))
Insert into UpdateTest Values (3,Sys(2015))
Goto 1 && goto the record to update (though it's the job of the UPDATE)
? "recno before update:",Recno()
Update UpdateTest Set cData="Updated" where id=1
? "recno after update:",Recno()
? Eof()

Alter Table UpdateTest Add primary key id tag xid
Goto 1 && goto the record to update (though it's the job of the UPDATE)
? "recno before update:",Recno()
Update UpdateTest Set cData="Updated" where id=1
? "recno after update:",Recno()
? Eof()
Use
Drop Table UpdateTest
Close Database
Delete Database Test

In the first case the record pointer is moved, yes. In the UPDATE of the table without the primary index the UPDATE has to test all records and ends at record 3, in the UPDATE with the index on id, it can optimize to only update id=1 and that's where the record pointer will be after the update.

So if your table has a primary key field you missed indexing it, or you even have no primary key. If you did, and your UPDATE would have the appropriate where clause selecting that ID, you'd end up at the updated record and would have no problem. I couldn't foresee that, I even didn't foresee your need to update an existing record.

I would only create a record, when the copy operation succeeds, as only then the record makes sense. You'd be stuck with incomplete data without the file, if the copy fails due to any reason, another reason I used INSERT-SQL in my example. When the outset rather is the filename stored being an optional extra attribute of a completely different entity, then the record will obviously exist before the file is picked, but you only very generally talked about associating files with the database. My idea therrefore was just a table for files, maintaining the files only. To relate any concrete data with a file you could have the id of this new table as foreign key of other tables. Anyway, that's what sample code means, it's not fitted exactly to your unknown needs.

You say you had problems with memo field and don't say what exact problems you had. There is no special syntax in neither Insert nor Update nor Replace for setting a memo fields value, like any field it's fieldname=value, so I don't know what problem you have.

Bye, Olaf.

 

[Olaf Doschke]

You could show code additional to talking about problems, otherwise there's nothing to fix and it's just a useless complaint.

What was your exact memo field problem? All memos having the last picked file in them, perhaps? Did you do an update without a where clause? That updates all records.

Bye, Olaf.

 

[Scott24x7]

Hi Olaf,
Thanks for the run down.
I haven't shared the code because I'm still working on it... there are some things I need to work out on my own... I don't understand your jumping to conclusion about it being a "complaint". It was not. Just relaying results of my findings.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

For simple image replacement/addition (right click on the image in the form is where this takes place) it looks like this:

lpGetImage = GETFILE("JPG,PNG,BMP")
lnCurRec = RECNO()
*
IF NOT EMPTY(lpGetImage)
	lContactID = CONTACT.CONTACTID
	This.Picture = lpGetImage
	UPDATE CONTACT SET BUSINESSCARDBACK = lpGetImage WHERE CONTACT.CONTACTID = lContactID
*
	GO lnCurRec
*
	SELECT (ThisForm.ActiveDBF)
*
	IF NOT TABLEUPDATE()
		WAIT WINDOW "Unable to Save"
		TABLEREVERT()
	ENDIF
ENDIF

If I understand correctly, I could simplify it using REPLACE instead, but looking in Fox Help I only see REPLACE FROM ARRAY which I think from experience years ago is where the difficulty with updating a memo field is. I don't recall the exact issue, but I think you either have to use a SCATTER to an ARRAY before you can use REPLACE, and I didn't really like that approach, as we moved away from SCATTER MEMVAR after Fox 2.6.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."