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