Обсуждение: Ghost insert
Hi.I am trying to do a interface from a web over my database postgresqlwhit libpq.I can't do a simple 'insert' from
libpq.I'mdoing this:sprintf(temp_string,"insert into clientesvalues('fff','prueba','123','123','pepe
gotera','jjjjjj','jjj')");res1=PQexec(conn,temp_string);tuplas=PQresultStatus(res1);imprime_cab_html(0);printf("Resultado:
%s<br><p>\n",PQresStatus(tuplas));printf("Temp_string:%s\n",temp_string);*****And the result on my web it's
OK:Resultado:PGRES_COMMAND_OKTemp_string: insert into clientesvalues('fff','prueba','123','123','pepe
gotera','jjjjjj','jjj')Butthe insert hasn't effect on my database. If from my database i do'select * from clientes;'
theresult it's empty.Where it's the problem?.Thanks in advanced.************
Thus spake Enrique Rodriguez Lazaro > Hi. > > I am trying to do a interface from a web over my database postgresql > whit libpq. > > I can't do a simple 'insert' from libpq. Just guessing here but is it possible that clientes is a view and you are seeing the underlying table when you select after the insert? In PostgreSQL, views are real tables. They just have an implied rule on select that gets data from a SELECT statement instead of the named table. See the following URL for more details. http://www.postgresql.org/docs/programmer/rules890.htm > I'm doing this: > > sprintf(temp_string,"insert into clientes > values('fff','prueba','123','123','pepe gotera','jjjjjj','jjj')"); > > res1=PQexec(conn,temp_string); > tuplas=PQresultStatus(res1); If the above is not your problem then you might also try PQoidStatus(res1) to see what the resulting OID is. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Enrique Rodriguez Lazaro <enrique@xpress.es> writes:
> I can't do a simple 'insert' from libpq.
Perhaps you did a "BEGIN" and haven't yet done a "COMMIT"?
The result of your insert (or any other data update command)
won't be visible to other clients until and unless you commit it.
However, if you haven't issued a BEGIN then the default behavior
is to auto-commit after every successful command, so I'm not sure
that's the right answer...
regards, tom lane
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> Thus spake Enrique Rodriguez Lazaro
>> I can't do a simple 'insert' from libpq.
> Just guessing here but is it possible that clientes is a view and you are
> seeing the underlying table when you select after the insert?
Oooh, good thought --- but you are explaining it backwards. If clientes
was made with CREATE VIEW, then an INSERT into clientes would in fact
insert data into clientes --- but you'd never see it again, because any
SELECT from clientes would be redirected to whatever the view is of.
(I've been burnt by that myself ;-).)
If you want to insert/update on a view, you need to provide ON INSERT,
ON UPDATE, ON DELETE rules that tell how to modify the underlying tables
appropriately. The system will *not* try to intuit these for you.
regards, tom lane
"D'Arcy J.M. Cain" wrote: > > Thus spake Enrique Rodriguez Lazaro > > Hi. > > > > I am trying to do a interface from a web over my database postgresql > > whit libpq. > > > > I can't do a simple 'insert' from libpq. > > Just guessing here but is it possible that clientes is a view and you are > seeing the underlying table when you select after the insert? In PostgreSQL, > views are real tables. They just have an implied rule on select that gets > data from a SELECT statement instead of the named table. See the following > URL for more details. > > http://www.postgresql.org/docs/programmer/rules890.htm > > > I'm doing this: > > > > sprintf(temp_string,"insert into clientes > > values('fff','prueba','123','123','pepe gotera','jjjjjj','jjj')"); > > > > res1=PQexec(conn,temp_string); > > tuplas=PQresultStatus(res1); > > If the above is not your problem then you might also try PQoidStatus(res1) > to see what the resulting OID is. > I can't resolve the problem. clientes is a table, isn't a view. All it's correct with respect the distints solutions that i have recived. The last time i have put this on my C program: sprintf(temp_string,"insert into clientes values('f','p','1','23','pepe gotera','j,'j')"); /* NOTE: the ';' it's not necesary*/ res1=PQexec(conn,temp_string); tuplas=PQresultStatus(res1); imprime_cab_html(0); printf("Status: %s<br><p>\n",PQoidStatus(res1)); printf("Resultado: %s<br><p>\n",PQresStatus(tuplas)); printf("Temp_string: %s\n",temp_string); printf("</body></html>"); And the result it's this: Status: 19648 Resultado: PGRES_COMMAND_OK Temp_string: insert into clientes values('f','p','1','23','pepe gotera','j','j') That's the clientes table: \d clientes Table = clientes +----------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------+----------------------------------+-------+ | id_cli | text not null | var | | empresa | text | var | | nif | text | var | | ubicacion | text | var | | telefono | text | var | | contacto | text | var | | observaciones | text | var | +--------------- ------+----------------------------------+-------+ Index: clientes_pkey The permissions over this database are: > \z Database = xpress+---------------+--------------------------+| Relation | Grant/Revoke Permissions |+---------------+--------------------------+|clientes | {"=","nobody=arwR"} || comerciales | {"=","nobody=arwR"} || prueba | {"=","nobody=arwR"} || prueba_id_seq | || seq1 | || tecnicos | {"=","nobody=arwR"} | 'nobody' is the apache user. If nobody run the query insert from the prompt of psql, the insert have effect. It's ok. Where it's the problem? Thanks in advanced.
Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> > Thus spake Enrique Rodriguez Lazaro
> >> I can't do a simple 'insert' from libpq.
>
> > Just guessing here but is it possible that clientes is a view and you are
> > seeing the underlying table when you select after the insert?
>
> Oooh, good thought --- but you are explaining it backwards. If clientes
> was made with CREATE VIEW, then an INSERT into clientes would in fact
> insert data into clientes --- but you'd never see it again, because any
> SELECT from clientes would be redirected to whatever the view is of.
> (I've been burnt by that myself ;-).)
No, I think I was right but I probably used the wrong word. By "underlying"
I meant the table returned instead of clientes. I guess "redirected" would
have been a better word.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Tom Lane wrote: > > Enrique Rodriguez Lazaro <enrique@xpress.es> writes: > >>>> I can't do a simple 'insert' from libpq. > > Come to think of it, the uncommitted-transaction problem could also > exist on the query side (the apache client). If that client is > running in SERIALIZABLE transaction mode, it won't see the effect > of an insert transaction started after its own current transaction. > So you might need to do a commit/begin cycle on that side too. > > regards, tom lane > > ************ Thank you very much. COMMIT was the solution.