• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • '' random_name computed columns in SQL translate differently depending on database

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (6)

Resolved '' random_name computed columns in SQL translate differently depending on database

  1. How-to
0
Votes
Undo
  1. Kurt Triebe
    Kurt Triebe
  2. PowerBuilder
  3. Monday, 17 July 2023 15:35 PM UTC

Hi all,

I'm a dev on a very large cross-DB-platform PB application. I'm trying to solve an issue that has plagued us as long as I can remember. The problem is with computed columns, specifically ones that are defined as two single ticks, with nothing in between them, like test_string_col in the following query:

SELECT acct.acct_id,
acct.acct_full_nm,
acct_loc.term_type,
'' test_string_col,
cast(0.0 as decimal(17,4)) test_dec_col,
'AB' test_two_char_str_col,
' ' test_one_space_str_col,
acct_loc.tax_exempt
FROM acct,
acct_loc
WHERE ( acct.acct_id = acct_loc.loc_acct_id )

When the SQL is edited while connected to an Oracle database, this gets translated to a char(0) column in the DW syntax. However, if the SQL is edited while connected to any other kind of DB/driver, from what I can tell (ODBC/SQL Server is really the only one I care about, though) it always ends up being char(1).

The SQL Server result is a disaster for 2 reasons.

First, it results in a field that is hard-capped at a single character, regardless of what edit mask is applied- whereas char(0) results in a column that can have any amount of text entered. We use this technique frequently as a dummy text entry field for something connected to a decimal ID value, and only being able to enter a single character here is a Very Bad Thing.

Second, let's say we've got 2 datawindows that we do RowsCopy back and forth on. If one gets edited while connected to SQL Server, and the other Oracle- the RowsCopy will fail, as there's a conflict between the column types of char(1) and char(0). Very often, a change just needs to be made to one of the DWs- and if the dev doesn't remember to connect to an Oracle DB first, they unknowingly create DWs that will eventually cause a RowsCopy problem.

This ends up being a problem that easily slips through the cracks on testing, and is also very difficult to identify via a search. If I could get all of our devs to ONLY connect to Oracle DBs while edited DWs, we'd be fine- but that has proven to be quite difficult, as NONE of our clients use Oracle- they ALL use SQL Server- and thus, our devs are frequently connected to SQL Server DBs, and I can't get them to remember to pop over to an Oracle DB for DW edits.

So, for the time being- we HAVE to keep an Oracle server around JUST for this reason- we HAVE to get any DWs that use this syntax to come out as char(0), not char(1). And, for any of you that are familiar with Oracle DBs... well... I'm sure you know just how big of a PITA those are. Everything about Oracle DBs is a thousand times harder than necessary, and to have to put up with this hassle JUST to keep this one DB around for DW edit purposes... gigantic waste of time & effort.

Ideally speaking, I would love to find some way to get SQL Server to create char(0) for computed string columns like this. 

The second best option would be finding an alternative DB platform besides Oracle, that's not as much of a PITA to deal with as Oracle, that creates char(0) instead of char(1). It's not a true solution, but it's at least a less painful situation from an administrative standpoint- getting to dump the Oracle instance would be a happy day.

Any ideas?

Thanks,

Kurt

Attachments (1)
char0vschar1.png char0vschar1.png
file search
Is this Ghostscript security flaw something Appeon...
Responses (6)
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 18 July 2023 06:45 AM UTC
  2. PowerBuilder
  3. # 1

Unless otherwise one can find a common denominator that works in all [ or both ] database(s), there can't be "ONE SQL WORK ANYWHERE" to be successful. 

Other option would be to use SETSQLSELECT.   While connecting to SQL Server, create a SQL statement that works in SQL and use SETSQLSELECT to assign it to the datawindow.  For oracle, construct a different SQL statement.  You need to create the dw which can work with SETSQLSELECT.  

Happiness Always
BKR Sivaprakash

 

Comment
There are no comments made yet.
Kurt Triebe
Kurt Triebe Accepted Answer Pending Moderation
  1. Monday, 17 July 2023 22:54 PM UTC
  2. PowerBuilder
  3. # 2

Yeah, I don't need truly unlimited; practically speaking, this is usually a fairly low number (probably less than 100), it's just not always known at the time the dev is creating the DW, or it varies and a limit gets set through code. The issue is just that when it gets hard-coded to char(1) in the DW syntax, regardless of whatever I do in the code or with an edit mask, PB will not let more than a single character into the field. The inconsistency with which PB creates this DW syntax- varying it depending on what type of DB you were connected to while visiting the SQL painter- is wildly frustrating to me. I can't get all my devs to always develop DWs while connected to Oracle. If I could enforce that somehow, this wouldn't matter so much, but I can't- we're ALWAYS connected to SQL Server because that's the only platform anybody actually uses. Stupid Oracle is good for literally 1 thing, and 1 thing only... making DWs work this way. Garbage otherwise.

Comment
John Fauss
  1. John Fauss
  2. Tuesday, 18 July 2023 05:08 AM UTC
If that's the case, omit the length specification from the varchar Cast:

SELECT Cast('' AS varchar) test_string_col

char(0) is not a valid datatype spec in SQL Server.
  1. Helpful
mike S
  1. mike S
  2. Tuesday, 18 July 2023 13:01 PM UTC
if you don't want to set it to some max size (100 or 200 or whatever), then you can always change the datawindow syntax at runtime to make it exactly how large you want it to be.



  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 17 July 2023 19:05 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Kurt -

Char(0) and Varchar(0) are not valid datatype specifications in SQL Server:

You can simply use "varchar" and you will be able to assign up to 8000 characters, as the documentation snippet I've pasted indicates, or use "varchar(max)" to go larger.

Best regards, John

Comment
There are no comments made yet.
Kurt Triebe
Kurt Triebe Accepted Answer Pending Moderation
  1. Monday, 17 July 2023 18:08 PM UTC
  2. PowerBuilder
  3. # 4

use cast to force it to the size you want:

  SELECT cast( '' as varchar(20)) '' test_string_col

FROM acct

 

It doesn't let me do varchar(0), though- it wants to force a non-zero number in there.

cast('' as varchar(0)) test_string_col


Gives me this:

SQLSTATE = 37000
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Length or precision specification 0 is invalid. Do you want to correct errors?

I need it to be char(0), not a finite integer.

Comment
mike S
  1. mike S
  2. Monday, 17 July 2023 21:15 PM UTC
yes, just set it to whatever the max size you need it to be.
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 17 July 2023 17:48 PM UTC
  2. PowerBuilder
  3. # 5

use cast to force it to the size you want:

  SELECT cast( '' as varchar(20)) '' test_string_col

FROM acct

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 17 July 2023 17:34 PM UTC
  2. PowerBuilder
  3. # 6

In Oracle I solve the problem by using the rfill() function of spaces up to the amount of char() I want the field to have.

Comment
Kurt Triebe
  1. Kurt Triebe
  2. Monday, 17 July 2023 18:05 PM UTC
I don't have a set number of spaces, though- I need an unspecified length. Getting the DW Syntax to say char(0) works in Oracle- that's what I want. The problem is every other DB platform does something different.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 17 July 2023 22:14 PM UTC
Ok, I get it now. I thought you did NOT want char(0). So you want this to get an unlimited amount of characters to be inserted? That's not really going to be the case anyway, if I'm not wrong. There's a limit on how many characters can be inserted in a single field. if I'm not mistaken something around 32,765
  1. Helpful
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.