1. Marie Bisschoff
  2. PowerBuilder
  3. Thursday, 29 September 2022 11:12 AM UTC
HI,

BigQuery in Database Painter in Powerbuilder:
* I connect fine
* When I run sql in Database Painter in Powerbuilder (works fine)

It is only when I run through IDE(runtime) when I get problems.

Can somebody help please.

Regards
MLB
Marie Bisschoff Accepted Answer Pending Moderation
  1. Tuesday, 11 October 2022 06:21 AM UTC
  2. PowerBuilder
  3. # 1

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

Comment
There are no comments made yet.
Marie Bisschoff Accepted Answer Pending Moderation
  1. Tuesday, 11 October 2022 05:58 AM UTC
  2. PowerBuilder
  3. # 2

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

 

Comment
  1. John Fauss
  2. Tuesday, 11 October 2022 13:23 PM UTC
Congratulations, Marie, that is very good to hear! Would you please take a moment and mark this issue as resolved? Thank you.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 30 September 2022 06:56 AM UTC
  2. PowerBuilder
  3. # 3

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

 

Comment
There are no comments made yet.
Marie Bisschoff Accepted Answer Pending Moderation
  1. Thursday, 29 September 2022 18:58 PM UTC
  2. PowerBuilder
  3. # 4

HI,

I am using Powerbuilder 2019 R3 2670.

Regards

MLB

 

 

Comment
There are no comments made yet.
Marie Bisschoff Accepted Answer Pending Moderation
  1. Thursday, 29 September 2022 18:55 PM UTC
  2. PowerBuilder
  3. # 5

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 

 

Comment
  1. John Fauss
  2. Thursday, 29 September 2022 20:14 PM UTC
There will not be any BigQuery-specific options you can specify from PB. Since the Simba ODBC driver appears to be designed to connect with BigQuery, I suspect this is where you will have to look to find configuration options to specify the Dataset and/or Location.

http://simba.wpengine.com/products/BigQuery/doc/ODBC_InstallGuide/win/content/odbc/bq/options/intro-online.htm

  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 29 September 2022 15:22 PM UTC
  2. PowerBuilder
  3. # 6

I don't think BigQuery is supported by Appeon.

Comment
  1. Chris Pollach @Appeon
  2. Thursday, 29 September 2022 19:09 PM UTC
Correct
  1. Helpful
There are no comments made yet.
Marie Bisschoff Accepted Answer Pending Moderation
  1. Thursday, 29 September 2022 14:08 PM UTC
  2. PowerBuilder
  3. # 7

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

Comment
  1. John Fauss
  2. Thursday, 29 September 2022 14:50 PM UTC
Can you find any BigQuery help online that can help you understand the error? I did a search and got several hits, but because I'm not familiar with it, the discussions have little meaning. Hopefully there is someone else in the Appeon Community that can help you. I suspect there are not a lot of PB people doing this, but I could be wrong about that.

Are you connecting in PB via ODBC or are you using a REST API call? What version of PB?
  1. Helpful
  1. Roland Smith
  2. Thursday, 29 September 2022 14:57 PM UTC
Location US refers to all data centers in the United States. Is the database painter connection using location US or something else?

Valid locations are here: https://cloud.google.com/bigquery/docs/locations

  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 29 September 2022 13:34 PM UTC
  2. PowerBuilder
  3. # 8

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

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.