PostsreSQL / SQL newbie question (solved)
Dan Ridinger
dlr at futurechalk.com
Sun Apr 14 23:24:36 EDT 2013
Hello Bob,
One of the new properties in the session object is $quotedidentifier which automatically put quotes around tables and fields for you. I've never tried this myself since I did it through my framework before this property came about.
The property being set should help not having to worry about the quoting.
Dan
On 2013-04-14, at 3:01 PM, Bastiaan Olij <bastiaan at basenlily.me> wrote:
> 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
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
Dan Ridinger
Managing Director
FutureChalk Software Inc.
20521 92A Avenue
Langley, BC V1M 1B7
Phone No: 604.723.6837
EMail: dlr at futurechalk.com
www: www.futurechalk.com
More information about the omnisdev-en
mailing list