Queries via ODBC plugin getting an (apparently) random empy response

Plug-ins
1

Hi guys,

Sometimes I work with external databases via ODBC. I began with MySQL but I experimented with one problem and, misguidedly, I changed to PostgreSQL hoping not to have to face it again. Nevertheless, that has not been the case and I guess the database system wasn't the problem.

I do queries to my databases through odbcGetArraySQL() and it works exactly as I want. My problem is that queries usually work well but sometimes AB gets "Empty" instead of the correct array, (being the query ok and having correct data in the database). It seems like a random behavior when connecting to my databases.

I show an AFL example below.

string = "ODBC;Database=database_name;DSN=PostgreSQL35W;Host=localhost;Port=5432;User ID=postgres;Password=password";
odbcOpenDatabase(string);	

formula = "SELECT Custom_data, Date FROM aapl ORDER BY Date ASC";
array = odbcGetArraySQL(formula);

Filter = 1;

AddColumn(array,"array",1.5);

I have checked the data source and it seems to be ok.

Captura
Captura971×432 28.3 KB

I would like to clarify I am not requesting technical support at all. I know that using the ODBC plugin is for advanced users and obviously I am not. So, wishing not disturbing, I don't ask for any concrete solution, but I would appreciate any suggestion of what I should investigate and learn more. I don't mind having to spend time learning more about it because being able to connect to my own databases is important to me.

Thanks a lot.

2

@BernieTGN, does it happens after some "long" inactivity or it seems to be totally random?
And/or immediately after connection?

2 Likes
4

Hi @beppe. It seems to be totally random and immediately after connection. If I click ten times at "Explore" in a row (in AA), I usually get one or two empty results.

Thanks,

5

Again, I am not PostgressSQL expert, but I would enable logging from Postgress side and I would look at the logs to see if I get any error messages.

1 Like
6

Thanks @Tomasz,

I'm a beginner with Postgresql and was not aware of the log files until your post. After reviewing them, I was able to identify the issue I was having and can now reproduce it. It must be said I have not experienced any problems with queries when using the plugin.

However, I've noticed that when executing the AFL formula in AA that contains the query to the database, it works perfectly fine as long as I keep the AFL Formula Editor closed. If I have Formula Editor opened and I don't have the AFL formula used in AA, it seems to work well, too.

My problem occurs when the Formula Editor and the AFL formula in AA are open simultaneously. Sometimes, when I execute "Explore" in AA, the formula that runs is different from the one contained in the code. To test this, I am using a "static" string query formula that always executes the same query.

At first glance, it would look like they sometimes run old versions of the AFL formula (querying fields that are no longer in my database). But if I've learned anything since I started programming as a hobby, it's not to rely too much on assumptions about how a code is executed.

I'll continue to investigate and search the forum to see what I'm doing wrong, but I think this is not related to connecting to the database via the ODBC plugin at all.

Thank's a lot.

1 Like
7

Generally when Analysis window RUNS the exploration it keeps a formula IN RAM, so
it won't be affected by edits, as long as you don't stop or finish exploration and restart it again. Then it modified formula will be read and exploration will be run with new formula.

It is also important not to change current working directory of the program if you are using RELATIVE paths. If you change CWD, relative paths will no longer resolve correctly. Make sure you are not changing CWD or that ProgressSQL does not change CWD somehow.

1 Like