Re: [SQL] ODBC SQL question
От | José Soares |
---|---|
Тема | Re: [SQL] ODBC SQL question |
Дата | |
Msg-id | 376F2F41.B94E2ED8@sferacarta.com обсуждение исходный текст |
Ответ на | ODBC SQL question ("Hostmaster - Internet au Virtuel Inc." <hostmaster@virtuel.qc.ca>) |
Список | pgsql-sql |
The <tt>M$Access boolean value is an integer (0 or -1);</tt><br /><tt> FALSE=0</tt><br /><tt> TRUE=-1</tt><br /><tt>whilethe PostgreSQL boolean is a string;</tt><br /><tt> TRUE= 'true','t','1','y','yes'</tt><br /><tt> FALSE='false','f','0','n','no'</tt><tt></tt><p><tt>Youhave to create an = operator for bool and int4 for compatibility withM$Access.</tt><br /><tt> </tt><br /><tt>This PL/pgsql script creates all what you need:</tt><tt></tt><p><tt>--this functionreturns every zero value as FALSE otherwise as TRUE--</tt><br /><tt>create function AccessBool(bool,int4) returnsbool as '</tt><br /><tt>begin</tt><br /><tt> if $1 is NULL then</tt><br /><tt> return NULL;</tt><br /><tt> end if;</tt><br /><tt> if $1 is TRUE then</tt><br /><tt> if $2 <> 0 then</tt><br /><tt> return TRUE;</tt><br /><tt> end if;</tt><br /><tt> else</tt><br /><tt> if $2 = 0 then</tt><br/><tt> return TRUE;</tt><br /><tt> end if;</tt><br /><tt> end if;</tt><br /><tt> return FALSE;</tt><br /><tt>end;</tt><br /><tt>' language 'plpgsql';</tt><tt></tt><p><tt>create operator = (</tt><br/><tt> leftarg=bool,</tt><br /><tt> rightarg=int4,</tt><br /><tt> procedure=AccessBool,</tt><br/><tt> commutator='=',</tt><br /><tt> negator='!=',</tt><br /><tt> restrict=eqsel,</tt><br/><tt> join=eqjoinsel</tt><br /><tt> );</tt><br /><tt></tt> <br /> <br /> <p>"Hostmaster- Internet au Virtuel Inc." ha scritto: <blockquote type="CITE">Hi <p>I'm running PostgreSQL v6.4.2 on RedHat Linux 6.0. I use it on a Windows NT <br />server 4.0 with service pack 5 through ODBC calls; the ODBC driver is the<br />one from Insight Distributions System, v6.40.00.06. <p>Usually everything is fine. My datasource works, as I canlink tables from <br />MS Access 97 without problems. <p>Here is what I don't understand: <br />Query1: SELECT * FROMsites WHERE free = FALSE; <br />Query2: SELECT * FROM sites WHERE free IS FALSE; <p>When I run them within psql, bothqueries output the requested rows. <p>Through the ODBC driver, query1 complains that "ERROR: There is more one <br />possibleoperator '=' for types 'bool' and 'int4' You will have to retype <br />this query using an explicit cast (#1) <p>query2complains for "Invalid use of Is operator in query expression 'free <br />IS FALSE'" <p>I tried fiddling with theODBC driver parameters, to no avail. What do I do <br />wrong? I ran the queries with MS Access 97 and also with pgAdmin6.4.3 beta, <br />they both return the same errors. Anybody could help me? <p>Here is the table definition: <br />+---------------------+--------------+-------+<br />| Field | Type | Length| <br />+---------------------+--------------+-------+<br />| site_id | int4 | 4 | <br />| name | varchar() | 50 | <br />| ip | varchar() | 15 | <br />| parent | int4 | 4 | <br />| type | int4 | 4 | <br />| owner | int4 | 4 | <br />| admin | int4 | 4 | <br />| creation_date | datetime | 8 | <br />| non_profit | bool | 1 | <br />| free | bool | 1 | <br />| reg_fee | bool | 1 | <br />| bill_period | int2 | 2 | <br />| bill_date | datetime | 8 | <br />| bill_paid | bool | 1 | <br />| paid_until | datetime | 8 | <br />| size | int4 | 4 | <br />| peek | int4 | 4 | <br />| list_personal | bool | 1 | <br />| list_organisation | bool | 1 | <br />| list_business | bool | 1 | <br />| title_fr | varchar() | 75 | <br />| title_en | varchar() | 50 | <br />| description_fr | varchar() | 254 | <br />| description_en | varchar() | 254 | <br />| free2 | bool | 1 | <br />+---------------------+--------------+-------+<p>Accept my apologies if I'm not in the right place to ask this. If thisis <br />the case, please tell me where I should direct my question. <p>Thanks, <p>Nicolas Cadou</blockquote><blockquotetype="CITE">______________________________________________________________</blockquote> PostgreSQL6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 <br />^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br />Jose' <br />
В списке pgsql-sql по дате отправления:
Предыдущее
От: Zalman SternДата:
Сообщение: Create table doesn't always respect atomicity of transactions.