Error with None/NULL Parameters While Converting the Document Management Application to PostgreSQL

Ignition
, ,
1

Hello,

I’m trying to adapt a “Document Management” solution from Ignition Exchange to PostgreSQL. In the current setup, some queries can pass a “NULL” value to the parentId column of type INT. When folder.id in Python is None, I send it to the Named Query, but the PostgreSQL driver throws this error:

"java.lang.Exception: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1"

def createFolder(self, name):
	import re
	folder = self.session.custom.filters.folder
	system.perspective.print(folder.id)
	if name == "":
		message("Please provide a folder name")
	elif not re.match("^[\s\w\d_-]*$", name):
		message("Folder contains invalid characters, only letters, numbers, spaces, underscores, and dashes allowed")
	elif system.db.runNamedQuery(path="Document Management/Folders/Exists", parameters={"parentId":folder.id, "name":name}):
		message("Folder '%s' already exists. Please try another." % name)
	else:
		system.db.runNamedQuery(path="Document Management/Folders/Add", parameters={"parentId":folder.id, "name":name})
		system.perspective.closePopup(id="folder-create")
		refreshFolders()
		refreshDocuments()

image
image2296×588 26.3 KB

Am I using it incorrectly, or is there no way to send a null value to an integer field in PostgreSQL? Should I consider alternative solutions?

2

I've never used PostgreSQL, but I'd check that the table's parentId column is nullable (can accept NULL values).

The tutorial on PostgreSQL DESCRIBE TABLE says that you can query the information_schema:

SELECT
  column_name,
  data_type,
  character_maximum_length,
  is_nullable,
  column_default
FROM
  information_schema.columns
WHERE
  table_name = 'myTable';

According to the StackOverflow question, sql - How to change a PG column to NULLABLE TRUE? - Stack Overflow, you can modify the table as follows:

ALTER TABLE mytable 
    ALTER COLUMN mycolumn 
    DROP NOT NULL;

I would expect that both of those queries could be run from Designer's Named Query editor if you have permissions.

3 
CREATE TABLE document_folders (
	id serial4 NOT NULL,
	"name" varchar(200) NOT NULL,
	parentid int4 NULL,
	CONSTRAINT document_folders_pkey PRIMARY KEY (id),
	CONSTRAINT fk_document_folders_1 FOREIGN KEY (parentid) REFERENCES mes.document_folders(id)
);
CREATE INDEX fk_document_folders_1_idx ON mes.document_folders USING btree (parentid);
CREATE INDEX idx_document_folders_1 ON mes.document_folders USING btree (name);

I checked table and parentid column is nullable.

image
image1064×344 37.2 KB
d

And also entered same value on that table. In designer it is not accept null value or None value on integer field same as MSSQL. But MSSQL accept None value in the script side.

4

That all looks OK then.
Can you show us your named query SQL?

5

Of course I'm adding it.

SELECT 
	COUNT(*)
FROM 
	document_folders f
WHERE 
	((:parentId IS NULL AND f.parentid IS NULL) OR (:parentId IS NOT NULL AND f.parentid = :parentId)) AND 
	f.name = :name

image
image2204×746 34.7 KB

6

Based on postgresql - Postgres Sql `could not determine data type of parameter` by Hibernate - Stack Overflow, try changing the first OR branch of your query from

WHERE
  (
    (:parentId IS NULL AND f.parentid IS NULL) 
    OR 
    (:parentId IS NOT NULL AND f.parentid = :parentId)) 
    AND 
    f.name = :name
  )

to

WHERE
  (
    (cast(:parentId AS INT) IS NULL AND f.parentid IS NULL) 
    OR 
    (:parentId IS NOT NULL AND f.parentid = :parentId)) 
    AND 
    f.name = :name
  )

(Again, I don't know PostgreSQL!)

1 Like
7

Thank you for your help. I don't understand what the exatctly issue is but this work.

SELECT COUNT(*)
FROM document_folders f
WHERE 
    f.parentid IS NOT DISTINCT FROM :parentId::INTEGER
    AND f.name = :name