- This topic has 6 replies, 3 voices, and was last updated 1 week, 3 days ago by
.
-
Topic
-
-
Replies
-
-
Are you doing SQL or Stored Procedure?
I am currently trying to get advanced lookup via Stored Procedure with a postgresql DB (Cloverleaf 19.1).
If you are doing Stored Procedure, perhaps what I discover could be of use to you.
I have been fighting my way through a lot of unknowns but making some progress (no join in my SP)
If you are using SQL, I am afraid I cannot be any help at this point.
email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.
-
-
Hi Rick,
Here is a simple join for a MSSQL Database:
select d.name_doc, lower(d.feld9) as file_ext, m.name as medium from object65 d, medien m where d.feld32 = <docid> and d.flags in (1,2,16) and m.id = d.medium_doc
The SQL will differ depending on what database you are trying to access (ie: Oracle, MSSQL, SQLite, PostgreSQL, etc.). I always develop the query in the native Database browser first. If it works there, then in should work from Cloverleaf.
Best Regards, Jim
-
-
I am glad you got that working.
As for SP postgresql, not good.
First, I am attempting this on CL 19.1 which does not natively support postgresql. We got a driver and installed that, but then needed to install postgresql libraries. Finally got connectivity. A simple query works fine.
But calling a SP fails. After trying everything I could I came to the conclusion the issue is Cloverleaf only supports IN and OUT parameters to a called procedure (at least in 19.1). That is what is documented. Postgresql does NOT support OUT parameters it only supports IN and INOUT parameters.
CL 2209 documentation still indicates only supporting IN and OUT parameters for SP but CL 2209 claims support for postgresql. I am unsure if a postgresql Stored Procedure with INOUT parameters can be successfully called from CL 2209.
Unfortunately, at this point, I cannot reference the DB I am using in CL 19.1 from CL 2209. Otherwise, I could determine if CL 2209 has the same issue.
If someone out there has successfully called a postgresql SP with INOUT parameters in post 19.1 Cloverleaf, I would be interested in conversing with them.
If I finally determine 2209 capability vis-a-vis postgresql and my memory allows, I will post here.
email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.
-
Quick update — I got the invocation of a Stored Procedure for Advanced Table Lookup working in CL 2209. I will produce a White Paper on how I did it and what I found out and post it as soon as it is done.
But, in the meantime, if you want to do that (or any DB Lookup that supports Stored Procedures) I am willing to assist.
email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.
-
- You must be logged in to reply to this topic.
