How to do this with Clarion and PostgreSQL?
Like this:
In a browse it should āJust Workā.
I assume you mean putting it all in a SELECT with appropriate Joins?
You might need a little extra on you dct definition.
This is my standard for Postgres tables:
/BUSYHANDLING=2 /ISOLATIONLEVEL=2 /JOINTYPE='ODBC 3.0' /NESTING=TRUE
Note there is also a Windows ODBC Join limitation of 2 or 3 or something. So more complex stuff itās better to put into a server side view
Carl Barnes: Edit to add links to help for above parameters.
/JoinType='ODBC 3.0' is not in help, but it does say
When using ODBC, the ODBC 3.0 standard does support multiple joins, so ODBC 3.0 compliant drivers should not require this switch.
2 Likes
Works in Postgres the same as any other database. If itās not working for you itās probably because you have set relationships up wrong in the clarion dictionary. Given what you are showing I would expect that your relacionamento table is a ālinkā table, and that it has a many-1 relationship with the other two tables, so that you get one row for every row in relacionamento, and that there are no null values allowed in the relacionamento columns that link to the other two tables.
1 Like
What is the error you are getting?
Show us the view that is being generated.
1 Like
And @seanh , excuse the question, but, how do I use the PostgreSQL View in Clarion?
Iām kind of new to ODBC and Iām not very used to it, so Iām learning everything, and you guys from the Forum have been a savior.
You just import the view into the clarion dictionary exactly the same as you would a table. The differences are that: since you are less likely to have an index on a view in the database you have to create them on the clarion side if you need them, plus you will run into far fewer problems if you are able to create a primary key on a columns or set of columns in the view āfileā in the clarion dictionary.
Modifying rows in a table based on a view is a bit different. Some views are updateable (i.e. the server will allow you to update them), some are not. In the ānotā case, the most likely scenario is that you want to update the ābaseā table of the view. In your case, you might define the update procedure for your view as UpdateRelacionamento, and when calling it from the browse for your view you do:
rel:tipo = v:tipo
rel:cliente = v:cliente
access:relacionamente.fetch(rel:pk_rel)
so that your update form is editing the matching relacionamente record.
1 Like
Thanks JonW, This information is really good!
And how would you judge the use of ALIAS with ODBC and PostgreSQL? Normal?
You might need an alias occasionally, but I think it should be very rare. For example, if you have a table called People which has columns Mother and Father, which link back to other people in the People table, my preference would be to have a view on the server like:
select p,name,f.name father,m.name mother
from people p
left join people f on f.id = p.father
left join people m on m.id = p.mother
to show a person with their mother and father. If you had to do it in Clarion you would require two alias files.
But if you have a form with two browses based on the same file, like if you were a matchmaker and you had a list of guys and a list of girls from the people file and you were trying to match them up, then you might still choose to do that with an alias on the the people file.
1 Like