Hi Leroy,
For the test I did, both DSN and DSNLess worked but it was with a TURBOSQL table. I could send a query through PROP:SQL, like
SELECT COUNT(*) FROM “scendmedia/demo_repo”.“atl_parcel_attr”;
and the records returned correctly.
But with a non TURBOSQL i see the same errors as you, sorry
Also on the dummy table, a second PROP:SQL after the first fails too with “Error Occurred: S1000 Error while preparing parameters” and error in function sequence number or something like that
I tried that and every variation that I can think of.
Find way more up to date versions here:
PostgreSQL: File Browser
Latest they have is 13.02
I just installed and tried 13.02. The same errors remain.
But with a non TURBOSQL i see the same errors as you, sorry
Ah, that’s too bad. I wonder if there’s a problem with Clarion’s driver, given that it works with other environments.
Also on the dummy table, a second PROP:SQL after the first fails too with “Error Occurred: S1000 Error while preparing parameters” and error in function sequence number or something like that
Well, it might be time to give up, unless you or anyone else has any other ideas.
Clarion ODBC has long worked well with PG. Gotta believe something still not right with the setup.
Clarion ODBC has long worked well with PG. Gotta believe something still not right with the setup.
That was my hope, but the setup is working from non-Clarion environments and not from Clarion. Maybe it’s not flexible enough to work with the quotes and forward slash that Bit.io requires.
Just a thought what about doubling the quotes up?
As in ““scendmedia/demo_repo.atl_parcel_attr”” ?
Turn on trace.
Start your Clarion app.
Open the browse.
After error, exit,
Turn off trace.
Open trace file.
Do a find for the SELECT generated for the browse.
Copy the select that was passed
Paste into your PG mgr.
Do you get an error?
Usually, an error reported there will be very specific.
Created a test here using leroy/test as the schema.
In PG SQLManager, either of the following selects were without error:
a) select * from “leroy/test”.“mytable”
b) select * from “leroy/test”.mytable
Double quotes are definitely required on the schema.
With Clarion ODBC (sans DNS):
c) using double quotes on the schema results in an error with table open before select
d) using no quotes allows the table to be opened but gives an error on the select itself
An explanation of this behaviour can be found in a trace file.
Trace file shows:
e) on Clarion open, all schema & table names are enclosed in double quotes
f) using any double quotes in the DCT will cause an error here
g) when non-quoted in the DCT, schema & table names on open appear in double quotes w/o error.
h) Clarion generated select does NOT add double quotes and so now gives a schema error.
I do not know at this point whether some driver string will correct this Catch-22 situation.
FYI, there is a direct access to PostgreSQL, without Clarion ODBC.
I’m aware of it, and I’ve heard that it works very well.
Does that work with template-generated code?
Yep, tried that too. No go.
Created a test here using leroy/test as the schema.
In PG SQLManager, either of the following selects were without error:
a) select * from “leroy/test”.“mytable”
b) select * from “leroy/test”.mytable
Double quotes are definitely required on the schema.
Using Clarion ODBC (sans DNS):
c) using double quotes on the schema results in an error with table open before select
d) using no quotes allows the table to be opened but gives an error on the select itself
An explanation of this behaviour can be found in a trace file.
Trace file shows:
e) on Clarion open, all schema & table names are enclosed in double quotes
f) using any double quotes in the DCT will cause an error here
g) when non-quoted in the DCT, schema & table names on open appear in double quotes w/o error.
h) Clarion generated select does NOT add double quotes and so now gives a schema error.
I do not know at this point whether some driver string will correct this Catch-22 situation.
Thanks for making such a thorough attempt at figuring this out.
I think I’m out of ideas.
Unless there is an appropriate driver string, it is a Clarion ODBC driver bug and will come into play whenever double quotes are used with a schema in the DCT. Why a schema name should include ‘/’ is another good question.
I got it to work with hand code prop:sql.
I got it to work with hand code via Mike Duglas’ psql DLL.
The double quotes around the schema name were required in both cases.
Seems logical to me. PROP:Sql selects are passed straight through by the driver.
Name attribute in a DCT (if used) would then be without quotes.
There was no DCT. I was using a hand coded file spec that did not match Leroy’s table - using turbosql.
Seems Like a bug. If you could report with your examples to the PTSS then we ‘might’ get a fix in the next update 
So are we concluding that there’s an issue with Clarion’s driver that Bit.io’s schema spec is flaring and that this possibly won’t work?
But there is something wrong additionally connecting with Bit.io
A simple code like this fails:
! Open Files
Relate:dummy.Open ! File dummy used by this procedure, so make sure it''s RelationManager is open
SELF.FilesOpened = True
! [Priority 7800]
dummy{PROP:SQL} = 'SELECT now();'
NEXT(dummy)
MESSAGE(ERRORCODE() & ERROR() & ' - Ret:' & dum:col1) !works
dummy{PROP:SQL} = 'SELECT now();'
NEXT(dummy)
MESSAGE(ERRORCODE() & ERROR() & ' - Ret:' & dum:col1) !fails
and produces this trace:
Version: 11.0.13244
03FF4H(1) 19:25:34.793 Allocating Environment Environment 0CF7D00H Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 dummy FILE,DRIVER('ODBC','/CLIPSTRINGS=FALSE /TURBOSQL=TRUE'),OWNER(******),NAME('dummy'),THREAD
03FF4H(1) 19:25:34.793 RECORD
03FF4H(1) 19:25:34.793 DUM:COL1 CSTRING(255)
03FF4H(1) 19:25:34.793 DUM:COL2 CSTRING(255)
03FF4H(1) 19:25:34.793 END
03FF4H(1) 19:25:34.793 END
03FF4H(1) 19:25:34.793
03FF4H(1) 19:25:34.793 GET_PROPERTY(dummy:0627200H) Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 GET_PROPERTY(dummy:0627200H) Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 GET_PROPERTY(dummy:0627200H) Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 GETNULLS(dummy:0627200H) Error: File Not Open Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 AllocatingConnection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:34.793 Connecting to Driver={PostgreSQL Unicode};Server=db.bit.io;Port=5432;Database=***;Uid=***;Pwd=***; Time Taken:1.25 secs
03FF4H(1) 19:25:36.050 Getting Functions for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Transaction capacity for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Transaction Isolation level Connection 0CF7D80HIsolation level is 2 Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Setting Auto Commit ON for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Correlation name support for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Quote character for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Qualifier Seperator for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Outer Join support for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting number of statements per connection for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting ODBC Version for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Driver Name for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Allocating Statement 0CF9C00H on Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Setting Asynchronous calling on for Statement 0CF9C00H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Setting Asynchronous calling off for Statement 0CF9C00H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Unknown Backend
03FF4H(1) 19:25:36.050 Maximum Statements per connection is 255
03FF4H(1) 19:25:36.050 Getting Connection Information for Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting list of key words Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Getting list of key words Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 OPEN(dummy:0627200H) Time Taken:1.25 secs
03FF4H(1) 19:25:36.050 GET_PROPERTY(dummy:0627200H) Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 SETNULLS(dummy:0627200H) Error: Invalid Field Type Descriptor Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Allocating Statement 0CF8C68H on Connection 0CF7D80H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Setting Concurrency to Read Only for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Setting Cursor Type to Forward Only for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Resetting Parameters Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Preparing Statement 0CF8C68H : SELECT now(); Time Taken:0.00 secs
03FF4H(1) 19:25:36.050 Executing prepared Statement 0CF8C68H Time Taken:0.64 secs
03FF4H(1) 19:25:36.691 Getting Number of columns for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 Setting number of rows to fetch to 1 for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 Describing Column 1 for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 Getting Auto-increment state for Column 1 for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 Getting Searchable state for Column 1 for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 SET_PROPERTY(dummy:0627200H) Time Taken:0.64 secs
03FF4H(1) 19:25:36.691 Binding Column 1 to C type CHAR(255) for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 Fetching Row from Statement 0CF8C68H Return Code: 0 Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 NEXT(dummy:0627200H) Time Taken:0.00 secs
03FF4H(1) 19:25:36.691 DUM:COL1 : '2022-01-31 22:25:37.000988'
03FF4H(1) 19:25:36.691 DUM:COL2 : ''
03FF4H(1) 19:25:36.691
03FF4H(1) 19:25:37.670 Closing Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:37.670 Closing Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:37.670 Resetting Parameters Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:37.671 Preparing Statement 0CF8C68H : SELECT now(); Time Taken:0.24 secs
03FF4H(1) 19:25:37.901 Executing prepared Statement 0CF8C68H
03FF4H(1) 19:25:38.103 Error Occurred: S1000 Error while preparing parameters
03FF4H(1) 19:25:38.103 Time Taken:0.20 secs
03FF4H(1) 19:25:38.103 Getting Number of columns for Statement 0CF8C68H Time Taken:0.00 secs
03FF4H(1) 19:25:38.103 SET_PROPERTY(dummy:0627200H) Error while preparing parameters Time Taken:0.44 secs
03FF4H(1) 19:25:38.103 Fetching Row from Statement 0CF8C68H Return Code: -1 Time Taken:0.00 secs
03FF4H(1) 19:25:38.103
03FF4H(1) 19:25:38.103 Error Occurred: S1010 [Microsoft][Administrador de controladores ODBC] Error en la secuencia de función
03FF4H(1) 19:25:38.103 NEXT(dummy:0627200H) Error en la secuencia de función Time Taken:0.00 secs
03FF4H(1) 19:25:38.103 DUM:COL1 : '2022-01-31 22:25:37.000988'
03FF4H(1) 19:25:38.103 DUM:COL2 : ''
03FF4H(1) 19:25:38.103PTSS #43275 now entered. Curious if this is still Scott’s domain.