PostsreSQL / SQL newbie question (solved)

Bastiaan Olij bastiaan at basenlily.me
Sun Apr 14 18:01:34 EDT 2013


Yeah that makes sense,

Mark, it's actually the other way around. If you do NOT use double
quotes postgres is case insensitive.

So:
select * from mytable
select * from MyTable
select * from MYTABLE

will all select from mytable whatever case is used.

select * from "myTable"
will only work if the table is called myTable.

But the beauty is that you can use characters that otherwise would fail
like "This is a REALLY dumb Tablename"

Also on the subject of case, there is one thing that gets me every time.

If you define a list from old fashion file classes (as many still do
when they are migrating from datafiles to SQL) Omnis column names are
case insensative so:
calculate ivMyValue as ivRow.MyColumn
calculate ivMyValue as ivRow.MYCOLUMN
calculate ivMyValue as ivRow.mycolumn

all work

BUT define your list from a schema/table class and all of a sudden
column names are case sensitive. This also applies when you select data
through $execdirect and then select through $fetch on a list that has
not columns defined. The columns will be defined in the case as postgres
returns (which by default is lower case) the result set and are case
sensative from that point onwards.

So if you do
Do lvList.$define()
Do ivSession.$execdirect('select MYCOLUMN from MYTABLE')
Do ivSession.$fetch(lvList, kFetchAll)

Our column name will now be "mycolumn" and thus:
calculate lvValue as lvList.1.MYCOLUMN
will fail.

But:
Do lvList.$define()
Do ivSession.$execdirect('select MYCOLUMN as "MyColumn" from MYTABLE')
Do ivSession.$fetch(lvList, kFetchAll)

Will return a list with the column name defined as MyColumn and thus:
calculate lvValue as lvList.1.MyColumn

will work.

Cheers,

Bas

On 15/04/13 4:03 AM, Bob Fiering wrote:
> Hi Mark,
>
> Your email crossed the mail i was typing.
> Problem solved.
>
> Because the tables and fields i manually created worked in combination with Omnis and the tables Omnis created did not, i compared the syntax Omnis used and the one i used. 
> There were quotes in the syntax for the tablenames and fields. I made an export file from the 'wrong' database and removed the quotes. After that i build the database with the right syntax and everything worked.
>
> Thank you Mark, Bas and Andy for all your input.
> If there are tips for working with Omnis in combination with Postgres please let me know.
> Ofcourse i will be at euromnis again this year to learn some more.
>
>
> regards,
>
>
> Bob
>
>
> Op 14 apr. 2013, om 09:29 heeft Bastiaan Olij <bastiaan at basenlily.me> het volgende geschreven:
>
> Hi Bob,
>
> Try changing the table name of the schema simply to 'FBV', omit the
> public bit.
>
> Also did you connect to the right database? With the postgres DAM you
> select the database first, then logon, not the other way around, so you
> may simply be connecting to the postgres database instead of your own
> database.
>
> I need to find time to get my website back up and running but I'll send
> you my old getting started demo library, you may find it of some use :)
>
> Cheers,
>
> Bas
>
> On 14/04/13 4:28 AM, Bob Fiering wrote:
>> Hi Mark,
>>
>> 1. Yes, i can connect with the Omnis SQL Browser to the postgres database. I can use show data and it shows the content of that table.
>> I can also connect with the object in combination with my login window that i created. 
>>
>> 2. With the pgAdmin tool i can see all tables and its corresponding data. When moving all tables under Omnis the tables were automatic placed under Schemas / public / Tables. Under public also the sequences are placed.
>>
>> 3/4  I think here lies the problem. The monitor tool you mentioned (and i missed) gives the message
>>
>> 2013-04-13 19:51:40 CEST	ERROR	relation "public.fbv" does not exist at character 1269	
>> 2013-04-13 19:51:40 CEST	STATEMENT	SELECT public.FBV.BBVCODE,   ......all fields from the table.....  public..FBV.BWEBSITE FROM public.FBV	
>>
>> when i choose show data in the SQL Browser of Omnis the statement is   SELECT * FROM "public"."FBV"
>> So "public"."FBV" is the right syntax. When i look at the properties of the schema class the table name is public.FBV.
>> When i change this to "public"."FBV" the error changes to
>>
>> 2013-04-13 20:08:16 CEST	ERROR	column FBV.bbvcode does not exist at character 8	
>> 2013-04-13 20:08:16 CEST	STATEMENT	SELECT "public"."FBV".BBVCODE,"public"."FBV".BBVNAAM,  ..... all the fields from the table........  "public"."FBV".BWEBSITE FROM "public"."FBV"	
>>
>> So somehow the schema class i created by dragging the table to Omnis has not the right relation to the table in postgres.
>> Not sure how to solve it, but i am a step further in the process of solving the problem.
>>
>> regards,
>>
>> Bob
>>
>> Op 13 apr. 2013, om 17:57 heeft Mark Phillips <mark.phillips at mophilly.com> het volgende geschreven:
>>
>> On Apr 13, 2013, at 8:22 AM, Bob Fiering wrote:
>>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
>


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij




More information about the omnisdev-en mailing list