File name contains spaces and underlines after download

Ignition
1

Hi there,

I currently have the problem that as soon as I want to download a file from the SQL Server, the file is displayed with spaces and the format is underlined (see pic below).

image

The file name is made up of the seperately strored columns [Filename] and [Filetype] .

image
image894×38 2.29 KB

To download the file I use the following code:

for i in range(fileReceived.getRowCount()):
		fileName = fileReceived[i]["Filename"]
		system.perspective.print(fileName)
		fileType = fileReceived[i]["FileType"]
		system.perspective.print(fileType)
		uploadedFile = fileReceived[i]["UploadedFile"]
		#system.perspective.print(uploadedFile)
		fileString = str(fileName + "." + fileType)
		system.perspective.print(fileString)		    
			    

	       if uploadedFile:
		      system.perspective.download(fileString, uploadedFile)
				
           else:	
	          system.perspective.openPopup()

This is what DevTools is dispaying in regard of the system.perspective.print() functions:

image

Any idea on how to solve this problem?

Any kind of help is much appreciated!

2

It looks like maybe your database entries aren't sanitized and contain whitespace and/or newlines?

Try:

fileName = fileReceived[i]["Filename"].strip()
fileType = fileReceived[i]["FileType"].strip()
2 Likes
3

Kevin, you're a legend! You made my day!

It worked using .strip()

Thank you tons!

4

There's something else to take from Kevin's answer: Maybe you should look at how things are inserted into your db, and clean things up before hand.

That doesn't mean you shouldn't also make sure you're getting proper data out and sanitize it, but having clean data in your db is never a bad thing.

1 Like
5

Good point - wasn't really paying attention to what I'm actually writing into the SQL-DB...

I've just checked and the number of blanks correlate to the selected Datatype (see pic below).
image

For instance - "Test1" equals 5 characters though 50 in total are reserved due to the datatype [nvarchaer(50)]. Hence when retrieved from the SQL-DB IGNITION is taking the whole bunch of 50 Characters, including the blanks...

Not sure if what I'm doing is bad practice so I'm open for opinions...

Thanks in advance!

6

If you need variable length strings, use nvarchar instead.
You can still have a maximum length, using nvarchar(50) for example, but the stored value will not be padded with whitespaces.

A few notes about what I'm seeing:

  • "Type" is a string. This is not ideal. I'd suggest either a int that represents an enumeration in your code, or a foreign key to a separate table that holds the actual types descriptions.
  • "FileVersion" should probably be an numeric value as well
  • "Date" is a datetime, which is not so bad, but it seems to me you're using sqlserver. If that's the case, use datetime2 instead.

Disclaimer: I am in no way a db specialist, I'm only talking about a few things I picked up over my small experience with them.
I know adding constraints and reference tables and such may seem like it will add a lot of work, but in the long run these things protect your data integrity and will save you a lot of headache down the road. A properly setup database is a VERY powerful tool to have in your application.

I'll also suggest throwing an eyeball at database normalization:

I haven't read this wikipedia article, but I'm guessing it will be insightful.
You can also search google to find better articles.

3 Likes
7

Thank you, Pascal, for the valuable information - it is greatly appreciated!

I will make the necessary changes to my table regarding the "nvarchar(50)" field type. It makes much more sense.

I just wanted to make a few additional remarks. The [ID] field in the "Files" table is already functioning as a foreign key. The "Type" field refers to internal document types that are uploaded by users of my application, such as "Drawing", "STEP File", and similar types. Therefore, I am afraid that I will have to stick with using a string for this field. The same goes for the "FileVersion" field, as the CAD department is using characters instead of numbers.

Still - Thanks for the support!!!

8

The ID column is a primary key, not a foreign key.
A foreign key references the primary key of another table. It's what makes a relational database relational. While you can have only one primary key (which could use more than one column, we'd say it's composite), you can have as many foreign keys as you like.

1 Like
9

Yes, it is a primary key, but this primary key is already being used as a foreign key in another table.

I simply wanted to point out that we're already - well, at least trying - to adhere to the "normalization" standard :slight_smile: