For now, I’m going to use https://www.elephantsql.com as an alternative to Bit.io. I’ve been able to connect to their PostgreSQL instances without any issues, other than being noticeably slower than Bit.io.
If this issue is addressed by SV I’ll switch back, I think.
Thank you all for your help with this. Much appreciated.
From Sean in another thread - it might help to add the following to your connection string:
usedeclarefetch=1
Thank you. I’ll try that.
It seems to take a few seconds to establish the connection but then the speeds are adequate. I’ll look at opening the table in the background when the app opens to get the delay out of the way in the background.
You might also take a look at TURBOSQL in the docs.
Will do. Someone else suggested that to me too.
I think your issue is in at “FROM scendmedia/demo_repo.atl_parcel_attr”. The DSN should already ask what database you are looking in so “scendmedia/demo_repo” will fail because PG doesnt support cross-database references. I would try setting the FROM to “FROM demo_repo.atl_parcel.attr”.
Make sure your data source database = scendmedia( assuming “scendmedia” is the database name).
for some insight here is my connection string.
CONNECT STRING( ‘Driver={{PostgreSQL ANSI};Server=;Port=5432;Database=<cap-sens_dbname>;Uid=;Pwd=;UseDeclareFetch=1;CommLog=1’ )
So, a query would look like ‘SELECT col1, col2 FROM demo_repo.atl_parcel_attr WHERE id=1’
Thanks, but Bit.io requires the database in DSN to be blank in order to work. Or in this case, not work. 
Just adding to the thread. I received an email from Bit.io’s CTO today:
In a few weeks, we’ll be moving away from requiring quoted schemas. Instead, the database name will have a slash in it. Most tools we’ve used support slashes in database names. Since the schema will no longer require a slash, the SQL will be easier to write, too.
So I’m hopeful that one way or another (either changes Bit.io and/or SoftVelocity make) this can be made to work in the future.
I received another update to this. Bit.io said they’ll be switching to a dot notation for their schemas instead of the forward slash. The timing is undetermined.
In the meantime, ElephantSQL has been working very well.
For what it is worth;
In the dct I only put the table name in the full path. (eg.atl_parcel_attr)
I create a global veriable called glo:pg_owner cstring(129)
The owner name is then a variable - !glo:pg_owner
Then in the exe at open files, this code comes in; (this is the connection string)
! glo:pg_owner = 'DSN=' & clip(loc:pg_connect) & ';' ! -- this is when using odbc
glo:pg_owner = 'DRIVER=PostgreSQL Unicode'
glo:pg_owner = glo:pg_owner & '; DATABASE=' & clip(loc:pg_database)
glo:pg_owner = glo:pg_owner & '; SERVER=' & clip(loc:servername)
glo:pg_owner = glo:pg_owner & '; PORT=' & clip(loc:pg_port)
glo:pg_owner = glo:pg_owner & '; UID=' & clip(loc:username)
glo:pg_owner = glo:pg_owner & '; PWD=' & loc:password
!
dummy_table{prop:sql} = 'set search_path = ' & clip(loc:pg_schema)
This last set search_path selects the schema in Postgres, and allows you to list multiple schemas if you wish.
These variables (loc:…) can all be stored in an ini file.
This should isolate the problem for you.
Hope this can help.
The S1000 bit of the error message is a common Windows ODBC error code.
Appendix A: ODBC Error Codes - ODBC API Reference | Microsoft Docs
ODBC Errors - Open Database Connectivity (ODBC) | Microsoft Docs
From the driver trace
Error Occurred: S1000 Error while preparing parameters
Is a “General warning”
Error Occurred: S1010 [Microsoft][Administrador de controladores ODBC] Error en la secuencia de función
Is a function sequence error
and this link might shed some light
Function Sequence Error from MS Query application (ibm.com)
Problem
Execution of queries with selection criteria in Microsoft Query is failing with error “Function Sequence error”
Symptom
Error message during execution of queries with parameters (selection criteria)
Cause
Asynchronous execution of the statements leads to execution of API SQLBindParameter() prior the execution of API SQLDescribeParam() is completed.
Resolving The Problem
Disable Async execution of the statements by using connection attribute ASYNCENABLE=0 in the connection string.
So looking at this
Asynchronous vs synchronous execution, what is the main difference? - Stack Overflow
and bearing in mind its an internet based db, I’m wondering if there is some sort of fancy firewalling going on, like only allowing one tcp connection per ip address regardless of NAT.
Edit. I should add, there shouldnt be, but you never know! /Edit
So maybe this driver switch would be one to look at to control the synchronicity?
/Busyhandling=1|2|3|4
Have you also tried to use GetInfo to get more info from the backend db?
Result = filelabel{PROP:GETINFO, property}
Thank you both, but I’ve switched to ElephantSQL and everything is working well.
Maybe Federico might be able to do some testing?
Hi Richard,
I’ve just tested and it doesn’t work.
The ASYNCENABLE=0 and combinations 1 2 3 4 of /BUSYHANDLING driver option didn’t solve the problem that issuing 2 simple PROP:SQL fails.
Relating to the slash and quotes needed on the schema, Leroy commented on feb.5th Bit.io communicated him they would be changing that, moving in a few weeks the slash to the database name, and on a second communication on feb.20th, dropping the slash and using dot notation,with timing undetermined. He doesn’t commented more about that, and on the web ui of bit.io it shows it still uses slash on schema.
The trick commented by Ian also didn’t worked. On the web ui you have a console like the query analizer where you can run ad hoc queries. This works there:
SELECT count(*) FROM “fnavarro/testrepo”.“cust”;
This doesn’t:
set search_path = “fnavarro/testrepo”;
SELECT count(*) FROM “cust”;
Federico
I’m not certain if /BusyHandling=1|2|3|4 is even the equivalent of the ASYNCENABLE=0 mentioned in the IBM webpage, because threading might also be a factor here.
SQLite and Multithreading
SQLite is designed to be used on a single thread but he Clarion file driver overcomes this limitation as much as possible. To make this work the driver has one limitation on usage. Only one thread can be accessing the SQLite database while a transaction is active. If another thread attempts to access an SQLite database and another thread is in the middle of a transaction, the non-transacting thread will wait until the transaction is completed…
The SQLite Driver supports the following standard SQL driver strings:
ALLOWDETAILS
AUTOINC1
NOPRIMARY
USEPRIMARY
WHERE
I’m beginning to wonder if the SQLlite driver could swapped into use in the dct to connect to bit.io at this stage as a quick check. Dont expect it to work, but you never know.
Has anyone tried to connect to the bit.io db using a MS office odbc connection or MS Power Query, because I’ve got connection string info out of it in the past but MS do this new tool called Power Query.
Microsoft Power Query
Richard,
The two PROP:SQL were issued on the main thread just after OPEN of the first table (a turbosql one).
I had connected to bit.io with databasebrowser portable from Etl-tools using the ODBC, dsn and dsnless . Received some errors first but managed to get it work. Even after having it working, sometimes queries failed and have to re run them.
Federico
Yes, I did see that. I switched to ElephantSQL because of the issues I ran into with bit.io and it’s been working well.
Thanks for the heads up.