sql error using odbc driver

[cyberfred95]

Hi
I am using power automate and  a step "open sql connection" to access to an odbc content on my hard drive

the connection is ok

the next step is to execute SQL instruction where I use "select devise from devises"; ( the " character is not in the filled) and when I run the step I have an error below :

Microsoft.Flow.RPA.Desktop.Modules.SDK.ActionException: Erreur dans l’instruction SQL L'opération arithmétique a provoqué un dépassement de capacité. ---> System.OverflowException: L'opération arithmétique a provoqué un dépassement de capacité.
à System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)
à System.Data.Odbc.OdbcDataReader.GetFieldType(Int32 i)
à System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
à System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
à System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
à System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
à System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
à System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
à System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
à System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
à System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
à Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.DatabaseActions.ExecuteSQLStatement(Variant connectionString, Variant sqlConnectionVariable, Variant sqlCommand, Variant& result, Int32 timeout, Int32 getConnection)
--- Fin de la trace de la pile d'exception interne ---
à Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.ExecuteSqlStatement.Execute(ActionContext context)
à Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

When I try to access this odbc content with Excel using the same sql statement there is no problem the data is returned without issue.

Do you have an idea?

thanks for your help

Regards

[lbendlin]

Your SQL query produces an arithmetic overflow.  Likely a division by zero somewhere. 

[cyberfred95]

Hi

There can not be any arithmetic issue as I only query a text column as you can see in the select with no computation at all.

Is there a way to troubleshoot odbc connection ?

Regards

[lbendlin]

how many rows in the Devices table?

[cyberfred95]

HI

I have done some additional tests.

The connexion is ok 

if I query a numeric value : select notypereglement from typereglement; there is no problem no error.

If I query a text value : select typereglement from typereglement; the error upper appears.

I try with several different tables this is exactly the same result if I query a text or a numeric column.

Any thought?

Regards  

[cyberfred95]

[cyberfred95]

Hi 6 rows , see below this is for test purposes. The content is not the problem because this is the same issue with all tables I try to query on small on big ones.

Regards

[lbendlin]

Does it have to be ODBC? Can you use a native driver instead?  What's the actual database?

[cyberfred95]

The target database is HFSQL https://pcsoft.fr/hfsql/

There is no native driver of course odbc is the last chance for such intégration

Is there any way to troubleshoot the issue? how do PA developers dos in such case ?

Regards.

[cyberfred95 · Accepted solution]

For other users that may have the same issue I was able to access HFSQL with the OLEDB driver more powerful than odbc driver 

So install the oledb driver donwloaded from PC SOFT Download Center: Common modules 27 - 270104h (windev.com)

Mention the connexion strings  in PA : "Provider=PCSOFT.HFSQL;Initial Catalog=<your directory where HFSQL files are installed>"