ODBC Join Test Example

[DonnEdwards]

In the Clarion documentation it refers to the “ODBC Join Test” as one of the examples. I have installed the examples for C9, C10 and C11 and not found the example.

See http://clarion.help/doku.php?id=odbc_join_test.htm which is a copy of the relevant help entry. It refers to a folder called "\ (CLARION ROOT) \EXAMPLES\ODBCJOIN " but it is nowhere to be found. Did it change its name?

I have created a dictionary that works as desired with TPS files. Now I want to migrate these files to a PostgreSQL database so I can manipulate the data and create a front end in Clarion. I just can’t get it to work. My app compiles but doesn’t seem to run.

I was hoping the example would shed some light on the correct way to set up a connection to the PG database, because the notes I have found in the C11 help file are completely inadequate for a newbie like me. I will keep looking, but for now I’m stumped.

Any advice or suggestions would be greatly appreciated. I have FM3 but until I can specify the connection to PG correctly it isn’t going to be able to assist.

[Mike_Duglas]

https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/

[DonnEdwards]

Thanks Mike, I have the correct connection string, but can’t figure out which field to put it in, in the dictionary’s table definition:
Driver Options: ?
Owner Name: ?
Full Path Name: ?
None of the examples I could find help me figure this out. The closest I could find is in FM3 documentation, but it’s not for the ODBC driver.

If I use the System ODBC DSN the string would be
'ODBC;DSN=nielsen; SERVER=127.0.0.1; PORT=5432; DATABASE=nielsen; UID=postgres; PWD=donn;'

Alternatively DSNless connection string is
'DRIVER=PostgreSQL Unicode; SERVER=127.0.0.1; PORT=5432; DATABASE=nielsen; UID=postgres; PWD=donn; LFConversion=0; UseDeclareFetch=1;'

[totalhip]

Donn,

Set driver to ODBC.
Put your driver string in a configuration ini file.
Load your global variable with the driver string from the ini.
Make it easy on yourself and use lowercase for PG - ie customers.

[DonnEdwards]

@DonnEdwards

Driver Options: ?
Owner Name: ?
Full Path Name: ?

So I leave all of it blank? Don’t I need to put the global variable somewhere?
I created glo:pgconn as a global variable and set its initial value to

'DRIVER=PostgreSQL Unicode; SERVER=127.0.0.1; PORT=5432; DATABASE=nielsen; UID=postgres; PWD=donn; LFConversion=0; UseDeclareFetch=1;'

but that doesn’t seem to do anything. I have tried using !glo:pgconn in the driver options or the owner name field without much success.

I have made all my table and field names lower case. I was warned about that

[totalhip]

Put global variable in the owner name.
Full path name is the PG schema.tablename.
I encourage the use of schemas once you get going.

[totalhip]

…and I assume you have indeed installed the PG ODBC driver.

BTW - a final caution: Once working, you will never want to think about tps again.

[CarlBarnes]

@DonnEdwards

It refers to a folder called "\ (CLARION ROOT) \EXAMPLES\ODBCJOIN " but it is nowhere to be found. Did it change its name?

I attached that example from CW20. On these old APPs you must set a Project Target (EXE) name or you’ll get error "Either the Sources list must contain only one File and that File must be a Project File or TargetName must be specified ".

Not sure how well it will work. I got these errors and did not try to fix. Those functions are from the OBDC.INC that I did not try to build that .PR

Unresolved External SQLAllocEnv in JOINTEST006.obj
Unresolved External SQLDataSources in JOINTEST006.obj
Unresolved External SQLFreeEnv in JOINTEST006.obj

ODBCJOIN_Cw20.zip (191.8 KB)

[DonnEdwards]

@totalhip

I assume you have indeed installed the PG ODBC driver.

Yes indeed, and I have tested it to ensure it works.

@totalhip

Once working, you will never want to think about tps again.

I certainly hope so

[DonnEdwards]

This is weird. I have removed all references to FM3 Version numbers on my table definitions in the DCT.

Here is the Global definition for glo:pgconn

Here are my two tables, as seen by pgAdmin III:

Here are the table properties for mediapad (mp)

and for ariana3 (a3)

I built a new application that just refers to these two tables. They are not related.
The “Start Without Debugger (Builds Solution)” successfully created the exe file, and then nothing appears to happen. No dialog boxes, no opening screen.

So I added in only the UltimateDebug (Global Extension) and changed “Build” -> “Set Configuration” to “debug” and ran “Start Without Debugger (Builds Solution)” again.

At least this time I get an error message.

So I’m making slow progress. I have looked at the generated CLW file for the global declarations, which includes the following:

glo:pgconn           STRING('Driver={{PostgreSQL ANSI};Server=127.0.0.1;Port=5432;Database=nielsen;Uid=postgres;Pwd=donn; {92}')
SilentRunning        BYTE(0)                               ! Set true when application is running in 'silent mode'

!region File Declaration
mediapad             FILE,DRIVER('ODBC'),OWNER(glo:pgconn),NAME('public.mediapad'),PRE(mp),CREATE,BINDABLE,THREAD ! Media Pad Product   
product_pk               KEY(mp:product_local_id),NOCASE,PRIMARY ! Primary Key for Products
Record                   RECORD,PRE()
product_local_id            LONG                           ! Product Local ID    
product_name                STRING(200)                    ! Product Name        
section_guid                STRING(40)                     ! Section GUID        
section_local_id            LONG                           ! Section Local ID    
section_local_id_original   LONG                           ! Section Local ID    
section_name                STRING(200)                    ! Section Name        
category_guid               STRING(40)                     ! Category GUID       
category_local_id           LONG                           ! Category Local ID   
category_local_id_original  LONG                           ! Category Local ID   
category_name               STRING(200)                    ! Category Name       
sub_category_guid           STRING(40)                     ! Sub Category GUID   
sub_category_local_id       LONG                           ! Sub Category Local ID
sub_category_local_id_original LONG                        ! Sub Category Local ID
sub_category_name           STRING(200)                    ! Sub Category Name   
advertiser_guid             STRING(40)                     ! Advertiser GUID     
advertiser_local_id         LONG                           ! Advertiser Local ID 
advertiser_local_id_original LONG                          ! Advertiser Local ID 
advertiser_name             STRING(200)                    ! Advertiser Name     
brand_guid                  STRING(40)                     ! Brand GUID          
brand_local_id              LONG                           ! Brand Local ID      
brand_local_id_original     LONG                           ! Brand Local ID      
brand_name                  STRING(200)                    ! Brand Name          
product_guid                STRING(40)                     ! Product GUID        
product_local_id_original   LONG                           ! Product Local ID    
selected_session            STRING(32)                     ! Selecttion Session Id
finished_task               STRING(4)                      ! Finished Task       
                         END
                     END                       

ariana3              FILE,DRIVER('ODBC'),OWNER(glo:pgconn),NAME('public.ariana3'),PRE(a3),CREATE,BINDABLE,THREAD ! Ariana Product      
product_pk               KEY(a3:product4_id),NOCASE,PRIMARY ! Primary Key on original Brand ID
Record                   RECORD,PRE()
product4_id                 LONG                           ! Product ID          
product4_name               STRING(200)                    ! Procuct Name        
section_code                LONG                           ! Section Code        
section_code_original       LONG                           ! Section Code Original
section_name                STRING(200)                    ! Section Name        
category_code               LONG                           ! Category Code       
category_code_original      LONG                           ! Category Code Original
category_name               STRING(200)                    ! Category Name       
category3_code              LONG                           ! Category 3 Code     
category3_code_original     LONG                           ! Category 3 Code     
eng_category3_name          STRING(200)                    ! Eng Category 3 Name 
advertiser_code             LONG                           ! Advertiser Code     
advertiser_code_original    LONG                           ! Advertiser Code Original
advertiser_name             STRING(200)                    ! Advertiser Name     
brand3_id_original          LONG                           ! Product ID Original 
brand4_id                   LONG                           ! Brand Local ID      
brand4_name                 STRING(200)                    ! Brand Name          
selected_session            STRING(32)                     ! Selecttion Session Id
finished_task               STRING(4)                      ! Finished Task       
                         END
                     END                       

Just to be thorough I tried changing ‘public.mediapad’ to just ‘mediapad’ in the dictionary but the error message still remains after rebuilding the EXE.

What should I try now?

[DonnEdwards]

@CarlBarnes

I attached that example from CW20

Many thanks. From the example I noticed that they define the tables slightly differently

So what part goes into the glo:DriverString and what part goes into the DataSource ?

[totalhip]

Global variable for connection string goes in the owner.
Forget about driver string for now (at least).
Use CSTRINGs rather than Clarion strings.
Suggest unchecking create. Do check bindable.

[DonnEdwards]

@totalhip

Use CSTRINGs rather than Clarion strings.
Suggest unchecking create. Do check bindable.

I have make these changes in the dictionary.

If I add in the driver options as shown, from the DCT editor I can right-click on the table, and choose “Browse Table”. I then get a pop-up window:

When I click OK it opens the table. It even created the table for me through this method. I deleted the previous table in order to create one using CSTRING fields instead of STRING.

But even after all this, the error message in my app remains the same:

So my guess is that I am “solving” the wrong problem.

Any ideas anyone?

[totalhip]

What happens if you do not include public as the schema?

[DonnEdwards]

Same error message, but without the word “public.”
Thank you to everyone who contributed. I have learnt a lot in the process.

I have given up trying to do this without FM3. I have succeeded with FM3.

I changed glo:pgconn to STRING(1024) and removed the initial value. It was too long.

In the global “After PROGRAM CODE Statement” embed I added in:

glo:pgconn = 'DRIVER=PostgreSQL Unicode;SERVER=127.0.0.1;PORT=5432;DATABASE=nielsen;UID=postgres;' | 
    & 'PWD=donn;LFConversion=0;'

In FM3 I clicked on the “Import SQL Connect Procedure” , and in “AutoUp” enabled the “ODBC driver” checkbox…

In the “SQL_Connect (Window)” I went to extensions:

That allowed the app to run and I filled in the required connection information. Bingo!

[totalhip]

Glad you got something working. Having never used FM3, I have no idea what extra value it is adding. A good practice to develop when having query problems:

Use windows odbcadmin to turn on tracing and direct output to a file.
Start your program.
Get the error.
Exit the program.
Look at the sql sent to the back end.

[DonnEdwards]

@totalhip

Use windows odbcadmin to turn on tracing and direct output to a file.

Next time I modify the DCT I will log the ODBC traffic and report back

[Alla]

I use postgres I have no connection problem ex DATA_ASSURESQL FILE, DRIVER (‘ODBC’), OWNER (datasource), NAME (‘Data_Assure’), PRE (SCLI), CREATE! Assured

datasource cstring (512)
datesource = ‘MyDataPostgres’ I created in odbc window after installing ODBC 32 and 64 from postgres .
I am trying to automatically create this source on all client workstations.
Is there an API or other to do it automatically

Nb: my observation is that POSTGRESQL is very efficient Thanks

Thanks

[DonnEdwards]

It is possible to link to PG without using an ODBC connection. See
https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/

I haven’t done it yet but www.watchmanager.net uses a DSN-less connection. Not sure how it is done.

[Alla]

The problem ?
or assign the connection string?
DRIVER or OWNER context (as in MSSQL)

By assigning the connection string in DRIVER
The system always asks for the data source and it works

Thanks