Auto-complete table and field names uppercase

No, the things (checkboxes) you're talking about are on DB Options -> DB Queries. The dropbox SysOp is referring to is on DB Options -> SQL Assistance -> Oracle -> DB Queries (you've got to unfold it first to see the queries). There are several queries, each one selected in an a dropbox. Clicking on the line selects its contents, double-clicking it cycles the selected query. You've got to click on the dropdown arrow if you want to see them all.

That's much clearer, thanks Gem.

Unfortunately, it still doesn't resolve the uppercase problem.

Although this is defined in the Columns (Oracle) + Keys text:

select
'WHAT THE',
UPPER(column_name),
data_type...

The picklist displays 'what the' and the field list, all in lowercase. And picking any of them returns a lowercase "what the".

I'll end this thread now. Hopefully Support will load up Oracle 11g, and try to replicate this, and issue a fix if it turns out not to be a user configuration problem. There's no point in a user insisting that they're experiencing these symptoms, unless Support can empirically confirm it either way.

Thanks Gemisigo, you did clear up some confusion.

Don't give up so quickly :)

Besides, having Columns (Oracle) + Keys defined the way you did does not help if Query "Columns" is not set to Columns (Oracle) + Keys but to Columns (Oracle). I see in the thread that you changed that as well. Modify it so that they both yield completely different results but both in uppercase to see which one is actually in command and if there's some glitch reverting the field names previously forced uppercase with upper(). Also try to change the dropbox back and forth and apply the settings. I had experienced in the past that the setting shown in the options/dropboxes was not the "dominant" one.

gemisigo wrote:

Don't give up so quickly :)

Besides, having Columns (Oracle) + Keys defined the way you did does not help if Query "Columns" is not set to Columns (Oracle) + Keys but to Columns (Oracle). I see in the thread that you changed that as well. Modify it so that they both yield completely different results but both in uppercase to see which one is actually in command and if there's some glitch reverting the field names previously forced uppercase with upper(). Also try to change the dropbox back and forth and apply the settings. I had experienced in the past that the setting shown in the options/dropboxes was not the "dominant" one.

Already done all of the above. Tried 'WHAT THE' in the keys version, and 'WHO DAT' in the version without the keys. Right now, with the Columns + keys being selected, and with this query text:

Columns(Oracle)
select
UPPER('WHO DAT?'),
upper(column_name),
data_type...

and

Columns (Oracle) + Keys
select
UPPER('WHAT THE'),
UPPER(column_name),
data_type

, it shows a picklist of lowercase 'what the' (from the keys version, so we know it's active) and lowercase column names.

And the previous version of the dropbox - which was pointing to the non-keys version - returned what was expected, except in lowercase.

Sql Assistant has a problem with the Upper() function. There might be some other possible conclusions, but I don't see them.

I see. I'm pretty sure SA does not have any problems with the Upper() function since that's executed in SQL context. My opinion is (based on your results) that SA reverts the results into lower case but that's to be confirmed yet. What do you get when you run the query (both for Columns (Oracle) and Columns (Oracle) + Keys) in the editor (replacing the :SCHEMA_NAME and :OBJECT_NAME and other : SA variables with something meaningful)?

gemisigo wrote:

I see. I'm pretty sure SA does not have any problems with the Upper() function since that's executed in SQL context. My opinion is (based on your results) that SA reverts the results into lower case but that's to be confirmed yet. What do you get when you run the query (both for Columns (Oracle) and Columns (Oracle) + Keys) in the editor (replacing the :SCHEMA_NAME and :OBJECT_NAME and other : SA variables with something meaningful)?

Gem,

Understood, thanks for the suggestions. Will have to continue tomorrow or Monday. Deadline looming.

Regards,

SE

gemisigo wrote:

I see. I'm pretty sure SA does not have any problems with the Upper() function since that's executed in SQL context. My opinion is (based on your results) that SA reverts the results into lower case but that's to be confirmed yet. What do you get when you run the query (both for Columns (Oracle) and Columns (Oracle) + Keys) in the editor (replacing the :SCHEMA_NAME and :OBJECT_NAME and other : SA variables with something meaningful)?

In both the bare Columns and the Columns + key query, all uppercase fields are returned. In contrast to the picklist and returned columns in Sql Assistant-enabled UltraEdit.

HOWEVER - here's a strange symptom - 7 minutes ago, I saw the expected uppercase picklist, ONE TIME, and selected one of the items. But afterwards, doing exactly the same thing (typing SELECT and a space), it defaulted back to the lowercase versions.

So right now, I'm receiving only lowercase results.

Gamisigo, have you tried sql assistant with Oracle 11g, using either Notepad++ or UltraEdit?

It appears that in Oracle in DB2 all object and column identifiers by default are stirred in system catalog tables in upper case, not in the original case they were coded by users (unless users specifically used name delimiters to save the correct casing. To remedy for the issue SQL Assistant automatically converts full upper case names to lower case. Without that conversion the resulting SQL queries look monstrous- - basically everything appears in uppers case.

To see it for yourself, imagine the following in Oracle

	Code:
	CREATE TABLE test_table_name (column_name_1 INT, column_name_B INT); CREATE TABLE TestTableName (ColumnName1 INT, ColumnName2 INT);

If the auto-correction to lower case wasn't used, SQL Assistant would generate SELECT statement for test_table_name and TestTableName as the following code. This code is hard to read absolutely all text appearing in upper case including keywords, and identifiers making them indistinguishable.

	Code:
	SELECT COLUMN_NAME_1, COLUMN_NAME_2 FROM TEST_TABLE_NAME; SELECT COLUMNNAME1, COLUMNNAME2 FROM TESTTABLENAME;

With the auto correction applied, you get the following, which is better, but still not perfect

	Code:
	SELECT column_name_1, column_name_2 FROM test_table_name; SELECT columnname1, columnname2 FROM testtablename;

However, if you create TestTableNameDelimited using quoted identifiers, everything would look quite different, your original casing will be preserved and the auto-correction won't be applied.

	Code:
	CREATE TABLE "TestTableNameDelimited" ("ColumnName1Delimited" INT, "ColumnName2Delimited" INT);

Now please undo your changes in the queries (don't use conversion to upper case or it wont' work) . Using SQL Assistant generate SELECT query for TestTableNameDelimited table You will get the following code which will have original name casing entered by the user.

	Code:
	SELECT ColumnName1Delimited, ColumnName2Delimited FROM TestTableNameDelimited.

The examples above are simplified to demonstrate the working of the case auto-correction feature. Hope it explains the internal mechanics.

SysOp wrote:

It appears that in Oracle in DB2 all object and column identifiers by default are stirred in system catalog tables in upper case, not in the original case they were coded by users (unless users specifically used name delimiters to save the correct casing. To remedy for the issue SQL Assistant automatically converts full upper case names to lower case. Without that conversion the resulting SQL queries look monstrous- - basically everything appears in uppers case.
.
.
.
The examples above are simplified to demonstrate the working of the case auto-correction feature. Hope it explains the internal mechanics.

Hmm, just as I thought. That's sort of reasonable but why is it hardwired into SA? You could achieve the same effect by using the modified DB Query and at least it would be the users' choice to use it lowercase or leave it "monstrous" or whatever their wish is.

	gemisigo wrote:
	Hmm, just as I thought. That's sort of reasonable but why is it hardwired into SA? You could achieve the same effect by using the modified DB Query and at least it would be the users' choice to use it lowercase or leave it "monstrous" or whatever their wish is.

Not quite so. It's not the conversion from upper case to lower case that is hardwired. It's the logic that if everything appears in upper case (every column of a table, or its name using upper case characters only), then it must be Oracle or DB2 not preserving the original user specified casing. and an attempt on SA part to produce a readable script. That's not a simple case of converting all across the board or not converting all that can be achieved using simple upper() or lower() function.

On the other hand, the control for applying automatic case correction could have been exposed as an option. To the best of my knowledge I don't know of any requests so far to change that auto-correction logic. If SqlExplorer or anyone else is interested in changing that, please submit an enhancement request. I hope it should be relatively easy to add a new user configurable option for controlling that feature.

I see. The ball is in SqlExplorer's court then :)

Well, I guess that's it. I prefer fields and table names in uppercase, but Sql Assistant won't provide that.

The tables and fields at my school are all in uppercase, and I'd like to retain that in Sql Assistant. In Pl Sql Developer, this isn't a problem.

I don't really care if uppercase entities are harder to read, for most developers. My style of coding isn't based on a popularity contest. Generally, I depend on the indenting scheme, to enhance readability.

At least we know now the reason, why SA won't do what we want. I'll decide whether it's worth continuing to use Sql Assistant, over the next couple of weeks.

Thanks,

I submitted an enhancement request. Ticket #SA0023686

	SqlExplorer wrote:
	At least we know now the reason, why SA won't do what we want. I'll decide whether it's worth continuing to use Sql Assistant, over the next couple of weeks.

vs.

	SysOp wrote:
	If SqlExplorer or anyone else is interested in changing that, please submit an enhancement request. I hope it should be relatively easy to add a new user configurable option for controlling that feature.

You really should pay more attention to what you read and what you perceive ;)

gemisigo wrote:

	SqlExplorer wrote:
	At least we know now the reason, why SA won't do what we want. I'll decide whether it's worth continuing to use Sql Assistant, over the next couple of weeks.

vs.

	SysOp wrote:
	If SqlExplorer or anyone else is interested in changing that, please submit an enhancement request. I hope it should be relatively easy to add a new user configurable option for controlling that feature.

You really should pay more attention to what you read and what you perceive ;)

No. I will not submit an enhancement request, for something that should already be a part of Sql Assistant. I wasted enough time on this. My Oracle database fields were defined as uppercase, and Sql Assistant is not returning that; it's being reinterpreted as lower case. This should be considered a bug, not a missing feature.

Anyway, I see that Sysop has already submitted it.