How to access to file saved as BLOB (Excel xls file) ? - Urgent help needed

Applications Forms
, , ,
#1

Hello Guys,

I configured Filed’s DataType as Document(Database) - please see attached screenshots.
I have external library which should read saved xls file (in MySQL as BLOB)

In external application I just added path to this file, e.g. /somwhere/on/server/docs/file.xls and I could read it.
But how to read it when file was stored with multiupload in SC?

Of course I know how to store file, how to create grid and access to this field, but I don’t know how to provide path to it for my Excel class.

Please advise. Any example needed.

Thank you very much

daretzki (Darek)

0.png

1.png

#2

I see you saved your blob as document database, hence the data is stored in your blob field.

Since scriptcase uses PDO this tutorial might help you:
http://www.mysqltutorial.org/php-mysql-blob/
Yet a blob stored in via pod is not just a binary dump of some bytes.
Microsoft excel can use odbc so you should be able to make an odbc connection to your mysql database and access the stored blob. Be aware tho that the blob in mysql is not a binary representation of the contents of the file. It has extra bytes as far as I remember. So you need to do some searching on the internet. ON the other hand, if you use PDO to store the blob as a temporary file then it should be fairly simple.

#3

Thanks rr.

So, the best way is store the excel file in field DataType Document(File) (as xls on disk) and access to it by path, right? Do you have simple example how to access to stored file in SC in this way? I tried to do it but I had problems how to define path to saved file. I saw few examples on forum with global session var, but I am not sure how to define it:



$_SESSION['scriptcase']['application_name']['glo_nm_path_doc'].{which_parameter}.'/'.{where_file} etc...

daretzki.

#4

Document filename is the easier way indeed.
You may want to use specific dirs for different people that login.
My example with an editable grid (1.jpg) and (2.jpg for the settings).
All I set then is the OnScriptInit where I set the proper path:


	[sc_upload_folder]='scriptcase/'.[sc_upload_subfolder];
	if (substr([sc_upload_folder],-1)!='/') [sc_upload_folder]=[sc_upload_folder].'/';

and the OnAfterDelete (since I want to delete the file when needed).


if (file_exists($_SESSION['scriptcase']['grid_filestorage']['glo_nm_path_doc'].[sc_upload_folder].$tmpFilename)){
	unlink($_SESSION['scriptcase']['grid_filestorage']['glo_nm_path_doc'].[sc_upload_folder].$tmpFilename);
};

This should get you going I hope…

1.JPG

2.JPG

#5

Thank you rr.

You are always ready to help.
That’s the solution what I was looking for. I will try to implement it in my project and will share information how I solved the problem.
btw, do you know how use multiupload as single upload? e.g. when I would like to prevent to add more than one file…?
Thanks a lot again!

daretzki

#6

The current upload in SC8 is buggy at the moment so what you see is my SC7.1 version. I dont use multiupload myself since that has not worked properly as I wanted in a long time. But using an editable grid you can always disallow an insert on the database as far as I see, just use an OnBeforeInsert and add an error box message in there. That should stop the rest of the insert.

#7

Hi again,

I don’t understand what is:



[sc_upload_folder] and
[sc_upload_subfolder]

in code:



[sc_upload_folder]='scriptcase/'.[sc_upload_subfolder];
if (substr([sc_upload_folder],-1)!='/') [sc_upload_folder]=[sc_upload_folder].'/';

variable are a global variables which are pointing to place in SC installation ?

how it is related to:



$_SESSION['scriptcase']['grid_filestorage']['glo_nm_path_doc'].[sc_upload_folder].$tmpFilename

is it mean, that with configuration what you have, files always will be stored in:



$_SESSION['scriptcase']['grid_filestorage']['glo_nm_path_doc'].[sc_upload_folder]

I have Excell class where i should provide path to uploaded file. And than this class will work on file (will import sheet to MySQL).
What will be path to file in example which you provided?

Thanks for help rr!

daretzki

#8

I used [sc_upload_folder] for myself. This contains the main path where I store my files in subdirectories (in my case in the c:/storage directory).
The [sc_upload_subfolder] is the subfolder under the main path where I store the files per record. So in my case /scriptcase/myapplication/123/ (where 123 is the record number).
So I store with each record in my app the files that belong there.
So I have a dir on my drive like: c:/storage/mutform/12/main.docx and c:/storage/mutform/12/report.xslx etc… I like to store files that belong together in one dir…

So in order to use it I create a global variable [sc_upload_folder] which I set in one application and then I jump to the editble grid…

Of course you can make it the way you want it… This is just my working example (tho for convenience the simplified version).