Reading/Writing Files to the Database

Ok, so i have this working, just wondering if there is an easier way to do this. In my database, i have a table called files, with 5 columns:
[ul]ID , which is of type INTEGER and is auto-incrementing
Tag, which is of type VARCHAR(200)
name, which is of type VARCHAR(200)
ext, which is of type VARCHAR(5)
data, which is a LONGBLOB.
[/ul]

On the windows, i have a List object, and 2 buttons, save and insert.

Save code

[code]file = event.source.parent.getComponent(‘List’).selectedIndex
if file != -1:
table = fpmi.db.toPyDataSet(event.source.parent.getComponent(‘List’).data)
id = table[file][‘id’]
ext = table[file][‘ext’]
name = table[file][‘name’] + “.” + ext

filename = fpmi.file.saveFile(name,ext,ext)

if filename != None:
	resultset = fpmi.db.runQuery("select data from files where id = %d"%(id))
	if len(resultset) > 0:
		data = resultset[0][0]
		writeToFile = 1
		if fpmi.file.fileExists(filename):
			writeToFile = fpmi.gui.confirm("File '%s' already exists. Overwrite?"%filename)
		if writeToFile:
			fpmi.file.writeFile(filename, data)

[/code]

Insert Code

#import re library import re #get tag of window tag = event.source.parent.Tag #prompt user to select file path = fpmi.file.openFile() #if user selects a file, and does not click cancel, do this if path != None: #find the extension split1 = re.split(re.compile("\."),path) for row in split1: ext = row #find the file name split2 = re.split(re.compile("\\\\"),path) for row in split2: name = row #remove the extension from the file name name = re.sub("."+ext,"",name) #read in the file as bytes bytes = fpmi.file.readFileAsBytes(path) #write the data to the database, and return the id of the new row key = fpmi.db.runPrepStmtGetKey("INSERT INTO Files (tag,name,ext,data) VALUES (?,?,?,?)", (tag,name,ext,bytes)) #refresh the data in the list, as it is set to polling off fpmi.db.refresh(event.source.parent.getComponent('List'),"data") #set the selected value equal to the new key, doesnt work, the previous #line of code should be a fpmi.db.runQuery with the data query event.source.parent.getComponent('List').selectedValue = key

Does anyone know if there isw an easier, or neater way to do this, or there is any other file type actions we can add to this screen?

ps Is there a way to add attachements to posts so others can just paste this into their projects, and save them the hassle?
File Management.fwin (10.3 KB)

Kyle,

Those scripts look nice! My suggestions below will neaten them up a tad, but not much…

The first script looks good. The second script could be cleaned up a bit by not using the regular expression library

#get tag of window tag = event.source.parent.Tag #prompt user to select file path = fpmi.file.openFile() #if user selects a file, and does not click cancel, do this if path != None: ext = path[path.rfind(".")+1:] name = path[path.rfind("\")+1:len(path)-len(ext)-1] #read in the file as bytes bytes = fpmi.file.readFileAsBytes(path) #write the data to the database, and return the id of the new row key = fpmi.db.runPrepStmtGetKey("INSERT INTO Files (tag,name,ext,data) VALUES (?,?,?,?)", (tag,name,ext,bytes)) #refresh the data in the list, as it is set to polling off fpmi.db.refresh(event.source.parent.getComponent('List'),"data") #set the selected value equal to the new key, doesnt work, the previous #line of code should be a fpmi.db.runQuery with the data query event.source.parent.getComponent('List').selectedValue = key

Not that I know of…this would be nice for screenshots especially, I’ll ask.

Hope this helps,

Aww man are you kidding, I didnt know about the rfind function, took me all morning to figure out the re library. Are these “other” functions in the help files?

I will post my finalied scripts in a bit, just finishing them up

Nope, thats pure Python functionality. I highly recommend that you go pick up a book on Python (I personally cracked open Jython Essentials published by O’Reilly to find “rfind”, but Learning Python or Python in a Nutshell are probably even better.)

Remember, we didn’t invent Python, so us documenting the language would be redundant - google for python docs and you’ll find much more complete stuff than we could ever write. This page:
docs.python.org/lib/string-methods.html
would have been the one to look at in this case.

Good to know. Here are my update scripts
Save File

file = event.source.parent.getComponent('List').selectedIndex
if file != -1:
	table = fpmi.db.toPyDataSet(event.source.parent.getComponent('List').data)
	id = table[file]['id']
	ext = table[file]['ext']
	name = table[file]['name'] + "." + ext

	filename = fpmi.file.saveFile(name,ext,ext)

	if filename != None:
		resultset = fpmi.db.runQuery("select data from files where id = %d"%(id))
		if len(resultset) > 0:
			data = resultset[0][0]
			writeToFile = 1
			if fpmi.file.fileExists(filename):
				writeToFile = fpmi.gui.confirm("File '%s' already exists. Overwrite?"%filename)
			if writeToFile:
				fpmi.file.writeFile(filename, data)
else:
	fpmi.gui.errorBox("No file selected!")[/code]

Insert File
[code]#get tag of window 
tag = event.source.parent.Tag 
#prompt user to select file 
path = fpmi.file.openFile() 
#if user selects a file, and does not click cancel, do this 
if path != None: 
	ext = path[path.rfind(".")+1:]
	print ext
	name = path[path.rfind("\\")+1:len(path)-len(ext)-1] 
	print name
	#read in the file as bytes 
	bytes = fpmi.file.readFileAsBytes(path) 
	#write the data to the database, and return the id of the new row 
	fpmi.db.runPrepStmt("INSERT INTO Files (tag,name,ext,data) VALUES (?,?,?,?)", (tag,name,ext,bytes)) 
	#refresh the data in the list, as it is set to polling off 
	fpmi.db.refresh(event.source.parent.getComponent('List'),"data") 

Delete File

file = event.source.parent.getComponent('List').selectedIndex
if file != -1:
	table = fpmi.db.toPyDataSet(event.source.parent.getComponent('List').data)
	ext = table[file]['ext']
	name = table[file]['name'] + "." + ext
	if fpmi.gui.confirm("Are you sure you want to delete %s from the database"%(name),"Delete"):
		id = table[file]['id']
		fpmi.db.runUpdateQuery("delete from files where id = %d"%(id))
		fpmi.db.refresh(event.source.parent.getComponent('List'),"data")
else:
	fpmi.gui.errorBox("No file selected!")

Update File

file = event.source.parent.getComponent('List').selectedIndex
if file != -1:
	table = fpmi.db.toPyDataSet(event.source.parent.getComponent('List').data)
	id = table[file]['id']
	#prompt user to select file 
	path = fpmi.file.openFile() 
	#if user selects a file, and does not click cancel, do this 
	if path != None:
		#read in the file as bytes 
		bytes = fpmi.file.readFileAsBytes(path) 
		#write the data to the database, and return the id of the new row 
		fpmi.db.runPrepStmt("UPDATE Files SET data = ? where id = ?", (bytes,id))
	else:
		fpmi.gui.errorBox("No new file selected. File not updated")
else:
	fpmi.gui.errorBox("No file selected!")

Im having an issue with inserting large files though. It is a MySQL setting though. Seems that the MYSQL server is being limited to files of roughly 1 Mb. Can you recommend a safe setting for this?

The exact error is

Gateway Error 301 SQL ERROR: Packet for query is too large(...). You can change this value on the server by setting the max_allowed_packet variable

Ah yes, the packet size problem. You can safely increase this to as big as you need to, meaning as big as the biggest file you want to support. 50mb, 100mb, something like that should usually suffice. There are probably some hard limits on the size of a single LONGBLOB entry, but its probably massively huge. Check the MySQL Docs if you need to store really big files.

Hope this helps,

max_allowed_packet is described in the MySQL documentation here. You can set that in your my.ini or pass it in as a runtime variable. It’d look something like this:

max_allowed_packet=16M

Thanks guys. To change the setting, i just went to advanced network in the administrator gui, and set it to 100Mb. As for size restrictions based off of data type, i found this page.

Looks like the restrictions are, if im not mistaken
[ul] TINYBLOB = 2^8, or roughly 256 bytes
BLOB = 2^16, or roughly 65 kb
MEDIUMBLOB = 2^24, or roughly 16 Mb
LONGBLOB = 2^32, or roughly 4.3 Gb
[/ul]

I have LONGBLOBs, as there might be a few manual which are greater than 16 Mb. If we can find a way to post attachments, ill post my windows for all.

Ask and you shall receive! You can now add attachments to posts.

Test message
boring_test.txt (11 Bytes)

Alrighty, time for some updated code. I converted part of the code to a function, and called the functions with fpmi.system.invokeAsynchronous() so the screens dont lock up while UL/DL files.

Save File to Computer

[code]file = event.source.parent.getComponent(‘List’).selectedIndex
if file != -1:
table = fpmi.db.toPyDataSet(event.source.parent.getComponent(‘List’).data)
id = table[file][‘id’]
ext = table[file][‘ext’]
name = table[file][‘name’] + “.” + ext

filename = fpmi.file.saveFile(name,ext,ext)

if filename != None:
	def saveFile(id = id, filename = filename):
		import fpmi
		writeToFile = 1
		if fpmi.file.fileExists(filename):
			writeToFile = fpmi.gui.confirm("File '%s' already exists. Overwrite?"%filename)
		if writeToFile:
			resultset = fpmi.db.runQuery("select data from files where id = %d"%(id))
			if len(resultset) > 0:
				data = resultset[0][0]
				fpmi.file.writeFile(filename, data)
	fpmi.system.invokeAsynchronous(saveFile)

else:
fpmi.gui.errorBox(“No file selected!”)[/code]

Upload File from Desktop

#get tag of window tag = event.source.parent.Tag #prompt user to select file path = fpmi.file.openFile() #if user selects a file, and does not click cancel, do this if path != None: def insertFile(path = path, tag = tag): import fpmi ext = path[path.rfind(".")+1:] name = path[path.rfind("\\")+1:len(path)-len(ext)-1] #read in the file as bytes bytes = fpmi.file.readFileAsBytes(path) #write the data to the database, and return the id of the new row fpmi.db.runPrepStmt("INSERT INTO Files (tag,name,ext,data) VALUES (?,?,?,?)", (tag,name,ext,bytes)) #refresh the data in the list, as it is set to polling off # fpmi.db.refresh(event.source.parent.getComponent('List'),"data") fpmi.system.invokeAsynchronous(insertFile)

and Update File

file = event.source.parent.getComponent('List').selectedIndex if file != -1: table = fpmi.db.toPyDataSet(event.source.parent.getComponent('List').data) id = table[file]['id'] #prompt user to select file path = fpmi.file.openFile() #if user selects a file, and does not click cancel, do this if path != None: #read in the file as bytes def updateFile(id = id,path = path): import fpmi bytes = fpmi.file.readFileAsBytes(path) #write the data to the database, and return the id of the new row fpmi.db.runPrepStmt("UPDATE Files SET data = ? where id = ?", (bytes,id)) fpmi.system.invokeAsynchronous(updateFile) else: fpmi.gui.errorBox("No new file selected. File not updated") else: fpmi.gui.errorBox("No file selected!")

Delete file has not changed. I have also included the SQL script to create the table, and my window, nothing fancy, but it works.

One problem with the window though. If you exit the window while it is uploading a file, it will give you an error when it is finished because it will try update the list of files, which it now cannot find because the window is closed. Can we add a function, fpmi.nav.currentWindow, to get around this? If we had this function, we could change the code to only update the list if the window is open.
SQL Query.txt (267 Bytes)

Good stuff - Thanks Kyle! You can use fpmi.gui.getOpenedWindows(). I bet Carl will suggest a better way of accomplishing that.

Yeah, i tryed the following code to see if it would work

try: window = fpmi.gui.getWindow('Files') if window == 'Files': .... except ValueError: ....

However, this wouldnt do it, for some reason, if i did a print window, it would print Files, so i dont know why it isnt working. I will try the getOpenedWindows(), and see how that works

ok, so this worked:

winopen = 0 for window in fpmi.gui.getOpenedWindows(): if window.name == "Files": winopen = 1 if winopen: fpmi.nav.openWindow("Files") fpmi.db.refresh(event.source.parent.getComponent('List'),"data") else: fpmi.gui.warningBox("<HTML>File <b>%s.%s</b> was uploaded successfully"%(name,ext),"Upload Completed")

I added the fpmi.nav.openWindow part so if the window is open, but in the background, it will give it focus

[quote=“Kyle Chase”]Yeah, i tryed the following code to see if it would work

try: window = fpmi.gui.getWindow('Files') if window == 'Files': .... except ValueError: ....

However, this wouldnt do it, for some reason, if i did a print window, it would print Files, so i dont know why it isnt working. I will try the getOpenedWindows(), and see how that works[/quote]

This didn’t work because fpmi.gui.getWindow(name) returns the actual window object, not its name. Sure, if you print it, it’ll print its name, but the object returned is not a string. Also, if that window isn’t open, you’ll get a ValueError anyways, so the if just isn’t necessary.

You could have done this:

try: fpmi.gui.getWindow('Files') # ok, do my update here - I didn't error out except ValueError: # oh darn window wasn't open. #Use 'pass' in this block to do nothing

ok, so this is what i finished with

try: fpmi.gui.getWindow('Files') fpmi.nav.openWindow("Files") fpmi.db.refresh(event.source.parent.getComponent('List'),"data") except ValueError: fpmi.gui.warningBox("<HTML>File <b>%s.%s</b> was uploaded successfully"%(name,ext),"Upload Completed")

I will upload the windows and sql query to the first post now

By the way - thanks for posting these! This forum has become quite the scripting repository. Nice touch on the asynch calls. Freezing buttons is never a good thing.

Hey man, anything i can do to help. You guys have helped me countless times, which i am thankful for. Just trying to give back to the community.

I’ve been playing around with this code, and ran into a problem.

I imported the window, and the only diifference in my setup is that I am using MSSQL, so instead of a BLOB, I am using the IMAGE data type. When I "“Upload” a file, I get an error on the last line:

Traceback (innermost last):
File “”, line 16, in insertFile
NameError: event

at org.python.core.Py.NameError(Py.java)
at org.python.core.PyFrame.getglobal(PyFrame.java)
at org.python.pycode._pyx1.insertFile$1(<event>:16)
at org.python.pycode._pyx1.call_function(<event>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyFunction.__call__(PyFunction.java)
at com.calmetrics.factoryhmi.application.script.ScriptManager.runFunction(ScriptManager.java:237)
at com.calmetrics.factoryhmi.application.script.builtin.SystemUtilities$3.run(SystemUtilities.java:127)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)

The file is actually uploaded, but the list doesn’t refresh. If I strip out the function def and the invokeAsynchonous call, and un-indent by one tab the code in between, it works fine.

Any ideas?

[quote=“Step7”]Traceback (innermost last):
File “”, line 16, in insertFile
NameError: event
[/quote]

This is just a simple scripting error - you are using a variable named “event” on line 16 that it doesn’t know anything about.

[quote=“Step7”]
The file is actually uploaded, but the list doesn’t refresh. If I strip out the function def and the invokeAsynchonous call, and un-indent by one tab the code in between, it works fine.

Any ideas?[/quote]

Sorry, there were too many scripts posted here for me to re-create exactly what script you’re using. Please post the script.

Thanks,