In BigQuery documentation guide it specifies that each DML statement is automatically committed, so when you set SQLCA.AutoCommit = True when connecting to the database, the IDE runtime and database painter behave the same. You can refer to: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#limitations

Hi All,

Got good news.

If you set SQLCA.AutoCommit = True it behaves the same as the db painter.

I can connect and get data back.

Yeah!

Regards

MLB

Hi Marie,

Maybe the difference between connecting from the IDE and running the executable is in the PBODB.INI file. Do you deploy that file along your executable?

https://docs.appeon.com/pb2022/connecting_to_your_database/XREF_15872_About_the_PBODB125.html

I've been in a similar situation with another (not supported) database, where using ODBC, simple selects, inserts, updates and deletes they all work, but lots of things with stored procedures don't. 

Our solution to that was to use a JDBC connection, but now in PB2022 JDBC is no longer supported. I've been told by the people of my previous job, that JDBC is coming back though, somewhere next year (when R2) comes out.

regards.

MiguelL

HI,

I am using Powerbuilder 2019 R3 2670.

Regards

MLB

HI,

SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "ConnectString='DSN=PAXI_BIGQ'" (We setup a Simba ODBC driver that BigQuery recommends)
CONNECT USING SQLCA;

I am using simba driver.

Is there some place that we can set the location in the connectstring.

The data is hold in the EU and we are from south africa.

They can't change the location in BigQuery.That is where the data reside.

How is it possible for it to work in the Database Painter and not runtime.

They setup a test column for me to call. Not even a difficult sql.

String ls_pep_branch_no

select string_column1
INTO :ls_pep_branch_no
from odbc_test
USING SQLCA;

Regards

MLB 

I don't think BigQuery is supported by Appeon.

Hi Sorry.

Yes I did check sqlca

That is where I got the Error description.

SQLSTATE = S1000
[Simba][BigQuery] (31750) Dataset is not found. Not found: Dataset grp-servts was not found in location US

What can I do?

Regards

MLB

Hi, Marie -

You have given us extremely little information to help you diagnose the problem. I suspect your application is not setting the Transaction object properties correctly.

Make sure your application is setting the Transaction object (SQLCA) properties to the same values you are using in the IDE. Edit the DB Profile that you know is working correctly. The "Preview" tab in the Database Profile Setup window will show you the PowerScript code you can place in your application to replicate those same settings in your application. There is also a "Test Connection" button in the Preview tab where you can verify that the settings defined in that DB Profile connect successfully.

Once you have assigned the connection properties to SQLCA, your app needs to connect the Transaction object to the database. This is typically done via the Connect command:

CONNECT Using SQLCA;

Your should always check the SQLCA.SQLCode property to see if the connection has been successfully established before proceeding. After a successful connection, the SQLCA.DBHandle() function will return a positive handle number.

In the future, keep in mind we are not mind readers (at least, I'm not), so please provide us with the PB version, release and build #'s of the version you are using. For database-related questions, please also include the database vendor, version and the SQLCA properties (mask out any sensitive information, such as server name and password). If you are receiving any errors/messages, it is a good idea to include error #'s and error text.

Best regards, John