Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
No trigger Just triggers for foreign key in spectacle and none in spectacle_v How is it possible to verify triggers for foreign keys ? Stephan Szabo a écrit: On Fri, 8 Aug 2003, BenLaKnet wrote: When I duplicate this code in an other table named spectacle_v without Foreygn key ... all is running. But when I try to delete a spectacle_membre, linked value in spectacle are correctly deleted, but I have an error for spectacle_v which is not linked : (ERROR: referential integrity violation - key in membre_adherent still referenced from spectacle_v ) What triggers are defined on membre_adherent? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] JDBC encoding problem
I have met the same problem when I deal with chinese.
I guess tomcat and jdbc produced this bug.
But you may solute the charset display problem.
You change the database's charset is useless when you connected DB with JDBC. You
should convert Input Text with UTF-8 before saved in database. When you fetch data
from DATABASE ,you should convert output data with some charset (F.E:GBK) before you
display the data in page.
There is some java code I used.Hope they will help you.
//Output:
OutputStream fileOut = new FileOutputStream(outputFileName);
fileOut.write(data.getBytes("GBK")); //GBK、GB2312、BIG5、UTF8
//Input:
InputStream fileIn = new FileInputStream(inputFileName);
int i = fileIn.read(buff);
String data = new String(buff,"UTF8");
Best regards.
leon
- Original Message -
From: "Kurt Overberg" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 06, 2003 1:11 AM
Subject: [SQL] JDBC encoding problem
>
> I'm having a rather strange problem that I'm hoping someone can help me
> with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
> the blackdown JVM . I'm attempting to convert my current SQL_ASCII
> database to UNICODE. I'm new to this, so am most likely making a few
> mistakes. Here's what I've done so far:
>
> o Converted database encoding to be UNICODE. I'm pretty sure this part
> worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
> created new db with encoding UNICODE and reloaded- no errors upon reload)
>
> sparky:~$ psql -l
> List of databases
> Name| Owner | Encoding
> ---+--+---
> unitest | kurt | UNICODE
> template1 | postgres | SQL_ASCII
> (2 rows)
>
>
> o set client_encoding to 'UTF8';
>
> o In my JSP files, I set the following at the top of each:
>
> <%@ page lanuage="java" pageEncoding="UTF-8" %>
>
>
> Now, to test this, I go to a japanese page, copy some text, then paste
> it into a form, that gets submitted to the server and saved into the DB.
> Then I try to display what I got back from the database. It comes out
> garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
> file it still comes out garbled, UNTIL I set UTF-8 manually in my
> browsers Character Encoding settings (both mozilla and IE). Then the
> japanese characters render fine (just like I entered them).
>
> Very strange. What's confusing is that when I set the pageEncoding to
> 'UTF-8', the characters don't render properly, and as far as I can tell,
> thats the same as manually setting the browser manually. I must be
> doing something wrong because I get the same results in IE and mozilla
> (recent build).
>
> What may be the problem- I don't do anything differently when getting
> the data out of the database, just standard
> resultset.getString("column"); Do I need to change that call, to handle
> the potentially UTF-8 encoded strings? I can't find anything on that at
> all with google/usenet.
>
> Any and all help, suggestions or pointers would be greatly appreciated.
>
> Thanks!
>
> /kurt
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] length of recordset read through a cursor
> > >> After declaring a cursor, one way of obtaining the length of the > >resultset > >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a > >"MOVE nn" > >> where nn is the length of the resultset. (A negative MOVE can then be > >used > >> to allow starting to fetch records from the beginning of the > >resultset.) > >> > >> Is there another, possibly faster way? > >> > >Looks like you're using libpq (because you mention PQcmdStatus), > >then after declaring a cursor and FETCH ALL, try > > > >1.3.4. Retrieving SELECT Result Information > > > >PQntuples Returns the number of tuples (rows) in the query result. > > > >int PQntuples(const PGresult *res); > > > >I'm not exactly sure what you're trying to achieve or going to do, > >so if I misunderstood you, ask again. > > > >Regards, Christoph > > Thanks for your reply. > What I'm trying to do is the following: I want to browse through a view > containing more than 1 records. To avoid slowing things down too much, > I would like my client program to receive (through the network) only the > records that are to be displayed on the screen. I believe I could do this > by declaring a cursor and then fetching the parts of the resultset I need. > It would be useful to know the size of the resultset immediately after the > cursor has been declared. How do I get this information? I could of course > fetch all of the resultset, but that is what I am trying to avoid. > Shouldn't it be quicker to perform a move through the set than fetching it? > I found that moving zero records results in a move to the end of the > resultset, with a command status returning the number of records moved. > Although I expected this method to take less time than a fetch (does it?), > I was wondering if there might be another way to get the size of the > resultset that can be fetched through the declared cursor. > I do not know about the internals of FETCH or MOVE. To me your first approach looks reasonable. I doubt there is way to learn about the size of a resultset simply by declaring a cursor (because there is no query taking place yet). You may like to send your request to the performance list. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sub-sel/group problem
On Monday 11 August 2003 11:24 am, Gary Stainburn wrote: > Hi folks, > > I don;t know if it's cos it's Monday or what, but I can't see what's wrong > here. > > I've got two tables, turns which holds a turn number, a task, and where > appropriate a link to a loco. The select below works but only shows those > tasks where a loco is involved.: > [snip] Having re-read my email and had another go, I've opted for the sub-select approach, and come up with: select rtid, concat(task) from (select rtid, case when r.rlid > 0 then r.rcid::text || ' on ' || l.lnumber::text else r.rcid::text end as task from rides r left outer join loco_dets l on r.rlid = l.lid) r group by rtid order by rtid ; Can anyone see any problems with this, or come up with a better approach? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Retrieving tuple data on insert
you can retrieve it using the currentVal function on the
sequence used to generate that id.
"sebmil" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
> I have a table with two columns, created with : CREATE TABLE test ( id
> serial primary key, name text ) ;
>
> To populate the table i use :
> INSERT INTO test(name) values('test1'); so the "id" is automatically set
> by PostgreSQL.
>
> Now the problem, i would like to retrieve the value of "id" that
> PostgreSQL assigns to this tuple, but i can't make a SELECT on the name i
> just inserted because it's not UNIQUE, so SELECT may return multiple
> results.
>
> Is there a way to retrieve the value of "id" from the insertion ?
>
> I was thinking of something with Oids, like getting the Oid of the INSERT
> statement then use it to SELECT the tuple, but i didn't find what to do
> with an Oid in PostgreSQL documentation.
>
> Also, it would be better if it was possible in only one operation (not
> INSERT then SELECT).
>
> Thanks in advance.
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] possible?
Hi! I'd like to make a bulletin board, and came upon a strange question: can the forum listing be done only with one single SELECT? My data structure is like this. Table "forum" holds the main categories, each with an "id" field. Table "thread" holds the threads, each has it's own "id" too, and a "forumid" field that specifies it's parent caregory. Table "post" holds the posts, with "id", and with a "threadid" field, that specifies which thread this post belongs to, and also each post has a timestamp. First the user should see a listing of the "forum" table. Then he selects a forum, and he should see the a listing of the "thread" table with the appropriate "forumid". It's not too serious so far. Here are my problems: - order the listings by the time the last post was made - display how many posts that "forum" or "thread" contains - for forums, display how many threads are in it I checked out some forum implementations so far, and found nothing helpful. They use too many queries, or don't order by time (like PHPBB). On the main page, I can list the forums, and then make a query for each of them to tell how many threads and posts are in it, but I just don't like it. By creating views, I was even able to order by time, but couldn't tell the number of threads. Also, after creating a new thread, it's empty, so "WHERE forum.id = thread.forumid AND thread.id = post.threadid" doesn't return empty threads, so noone can post to it. What I'd really love to see is a single SELECT statement for each of the two listings that display everything I want. With data views, embedded selects, or anything. So, is it possible? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Comparing arrays
On Sat, Aug 09, 2003 at 09:01:13AM -0700, Joe Conway wrote:
>
> This should work on 7.4 beta.
And now it works too in 7.3. I've implemented the <, <=, =,
<>, =>, and > operators in PL/pgSQL for the VARCHAR[] type and
assembled them into an operator class. This is just a bit slow but
that's bearable considering that the queries using them are just
exceptional administrativia tasks.
> In any case, 7.3 and before have many issues with effective use of
> arrays. For instance, even if you got all this working, you'd find
> working with arrays in PL/pgSQL painful if not impossible.
The only annoyance trouble I had with array manipulation is
the array_dims() function. I had to wrap it inside
split_part(split_part(array_dims($1), ']', 1), ':', 2)::INT
> If you really depend on arrays, I'd strongly suggest testing on 7.4 beta
> and moving to 7.4 as soon as it is released.
Right now I'll try to stay away from betas or .1 releases: I'm
not yet confident enough with my skills and may not be able to decide
if a failure is mine or postgresql's.
Thanks for your kind answer.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] optimisation of a code
Hi all
I wrote a script in PHP with mysql (hum ... to be honnest, I wrote
this script under PostGreSQL but we recently migrated to mysql, so I
had to adapt my code to mysql ... sorry about that ... anyway, it is
the same kind of query, with subqueries !) but this is a very very
long script ...
I was wondering if there is a way to optimize this one, by doing some
JOIN (because the subqueries don't seem to be very powerful ...)
Here is the script :
To resume, it is a script that :
- list all the leads available (1st query)
- For each lead, find 3 members that can buy this lead (2nd query)
- For each member, buy the lead
";
$today_midnight = strtotime(date('Y-m-d 00:00:00'));
if ($AFF_FIXED_AMOUNTS)
$amount_fixed = $AFF_SHD_AMOUNT;
else
$amount_fixed = $AFF_PERCENTAGE * .01 *
$PRICE_POINT_IN_DOLLARS;
while ($lead=mysql_fetch_assoc($r_avail))
{
$n = $lead[period];
if ($lead[loan_type] == "Refinance") $type="refi";
else if ($lead[loan_type] == "Purchase") $type="pur";
else $type = "homeq";
$field = $type."_t$n";
$price = $lead[price];
$id = $lead[id];
$aff_id = $lead[affiliate_id];
// SECOND QUERY
// find the members that fit all the required criterias
$q_members = "select member.id, automated.delivery, member.email
from (automated INNER JOIN member ON member.id = automated.member_id)
";
$q_members .= " where activated=1 ";
$q_members .= " and website='$SITE_NAME'";
$q_members .= " and (select count(*) from trans_member where
(unix_timestamp(now())-unix_timestamp(date)) <
(unix_timestamp(now())-'$today_midnight') and type='purchase' a\
nd comment LIKE '%automated%' ";
$q_members .= " and member_id=member.id and comment LIKE
'%$type%') < max_$field ";
$q_members .= " and balance_in_points > $price ";
$q_members .= " and credit_ratings_t$n LIKE
'%$lead[borrower_credit_rating]%' ";
$q_members .= " and states LIKE '%$lead[prop_state]%' ";
$q_members .= " and ltv_t$n/100 >= (cast($lead[loan_amount] as
unsigned) / cast($lead[current_value] as unsigned)) ";
$q_members .= " and amount_t$n < $lead[loan_amount] ";
$q_members .= " and $id NOT IN (select lead_id from purchase where
member_id=member.id) ";
$q_members .= " AND $aff_id NOT IN (select affiliate_locked_id
from affiliate_lockout where member_id=member.id) ";
$q_members .= " AND $id NOT IN (select lead_id from purchase where
member_id IN (select member_id_to_exclude from member_exclusion where
member_id=member.id))";
$q_members .= " ORDER BY balance_in_points DESC";
$r_members = mysql_query($q_members);
$nbdispo = $NBPERSONS_SHARED - $lead[nbsold];
while (($member=mysql_fetch_assoc($r_members)) && $nbdispo>0)
{
BUY THE LEAD FOR THIS MEMBER
$nbdispo--;
}
//}
} // END OF while ($lead=mysql_fetch_assoc($r_avail))
?>
Has anybody an idea ?
Thanks very much for your help
Krysto
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] string function -- replace needs a clear doc
urldb=# select replace('whateveritis','a','A');
replace
--
whAteveritis
(1 row)
urldb=# select replace('whateveritis','e','E');
replace
--
whatEvEritis
(1 row)
urldb=# select replace('whatever%20itis','e','E');
replace
-
whatEvEr 1tis
(1 row)
urldb=# select replace('whatever%%20itis','e','E');
replace
-
whatEvEr%20itis
(1 row)
urldb=# select replace('whatever%%20sitis','e','E');
replace
--
whatEvEr%20sitis
(1 row)
urldb=# select replace('whatever%20sitis','e','E');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
> psql urldb
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
urldb=# select replace('whatever%20sitis','si','SI');
replace
---
whateverSItis
(1 row)
1. No doc said % should escape by %%, and
select replace('whatever%20sites','%20','%%20') won't work also.
2. v7.3.2 haven't resolved this problem yet, I am not sure the later version.
Jie Liang
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
