I just noticed that from a C or C++ program using
libpq or libpq++, I can send *one* command that
contains several SQL statements separated by
semicolon. Something like:
PgDatabase db (" .... ");
const char * const sql =
"insert into blah (...); insert into blah (...)";
if (db.Exec (sql) == PGRES_COMMAND_OK)
{
cout << "Fine!" << endl;
}
And I verify the table, and all the inserts took place
(and of course, the program outputs "Fine!").
But I'm wondering -- is this a PostgreSQL extension,
or is it "legal SQL"? In particular, I'm wondering
if it is a feature that in the future you might
decide to eliminate for not being ANSI-SQL compliant.
What happens if the first command is ok but the second
one fails? I guess PgDatabase::Exec would return an
error code, and PgDatabase::ErrorMessage would return
the error message corresponding to the second statement
(the one that failed). Am I correct in thinking this?
Any reason why this should be avoided? (on the plus
side, I think this might increase efficiency for
transactions where one executes several insert or
update statements).
Thanks for any comments,
Carlos
--
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 5 1283
Carlos Moreno wrote: I just noticed that from a C or C++ program using libpq or libpq++, I can send *one* command that contains several SQL statements separated by semicolon.
But I'm wondering -- is this a PostgreSQL extension, or is it "legal SQL"?
The whole libpq API is made up out of thin air, so it's not conforming
to any public standard.
In particular, I'm wondering if it is a feature that in the future you might decide to eliminate for not being ANSI-SQL compliant.
Because of the above, that cannot be a reason for eliminating any
interfaces.
What happens if the first command is ok but the second one fails?
All the commands are run in one transaction, so if one fails, the whole
sequence is rolled back.
--
Peter Eisentraut http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Peter Eisentraut wrote: I just noticed that from a C or C++ program using libpq or libpq++, I can send *one* command that contains several SQL statements separated by semicolon.
But I'm wondering -- is this a PostgreSQL extension, or is it "legal SQL"?
The whole libpq API is made up out of thin air, so it's not conforming to any public standard.
Oh, wait. Though you didn't say it explicitly, I guess
you're implying that it is libpq the one that splits the
thing into the individual SQL statements and then send
each of those to the backend? (wrapped around a
transaction?) In particular, I'm wondering if it is a feature that in the future you might decide to eliminate for not being ANSI-SQL compliant.
Because of the above, that cannot be a reason for eliminating any interfaces.
Well, I was under the impression that the backend would
receive *one* command with *one* string that contains
all the SQL's separated by semicolons. This is what I
thought might be removed in future releases, if it is
considered that it's an unnecessary extension, etc.
Thanks,
Carlos
--
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Carlos Moreno wrote: But I'm wondering -- is this a PostgreSQL extension, or is it "legal SQL"?
This is a reasonably standard result from an "exec" or "execute" call,
but I am not an SQL expert. How else could the psql command
line utility process command files?
What happens if the first command is ok but the second one fails?
I've not used this interface extensively because I do
mostly transactional work. The easy way to test would be to
make the second statement fail on a syntax error.
Any reason why this should be avoided?
Yes. You've raised the core issue in your question about what happens
when a command fails. For efficiency and convenience, use prepared
statements.
For greater efficiency, execute stored procedures to do your work
In general, my web applications use prepared statements for moderate
efficiency.
For batch applications, I would use stored procedures or embedded SQL (both
provide the advantage of stored, optimized query plans).
-bill
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Bill Harper wrote: But I'm wondering -- is this a PostgreSQL extension, or is it "legal SQL"?
This is a reasonably standard result from an "exec" or "execute" call, but I am not an SQL expert. How else could the psql command line utility process command files?
Well, notice that the keyword in here would be
"utility" -- psql is a program that interacts with
the user, and as such, it can do any processing and
add any logic it wants.
I always thought the semicolon was a psql thing,
which it would use to determine when the user is
done entering the SQL statement. For libpq or other
client libraries, the way I saw it, it is the end
of the received string what would allow the backend
to figure out where the SQL statement ends.
Thanks,
Carlos
--
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Carlos Moreno <mo****@mochima.com> writes: I always thought the semicolon was a psql thing, which it would use to determine when the user is done entering the SQL statement.
Exactly. psql parses what you type sufficiently well to locate
command-ending semicolons, and then sends one command at a time to the
backend. libpq is much dumber; it just takes the string you give it and
ships that to the backend.
The backend's current behavior with multi-command input strings has been
that way since the beginning (or at least since before I got here).
People have occasionally proposed removing the feature, but it's pretty
well entrenched on backwards-compatibility grounds.
Note that the new "v3" fe/be protocol does *not* allow multiple commands
in an extended-query-mode input string, so if you are of the opinion
that allowing multiple commands is a bad idea you can just use extended
query mode all the time. (In libpq that translates to not using PQexec,
but one of the newer functions.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: matty |
last post by:
Go away for a few days and you miss it all... A few opinions...
Programming is a craft more than an art (software engineering, not
black magic) and as such, is about writing code that works,...
|
by: pakkocool |
last post by:
¿Como ganar dinero en internet?
Lee atentamente el siguiente texto, es super interesante y te hara
ganar muchos dolares si sigues las instrucciones y le pones empeño:
Hace unos días que...
|
by: Chris Riesbeck |
last post by:
I need to remove colors put on links with Javascript so that CSS hover
works on uncolored links. Both background color and transparent turn
off the hover effect, and null doesn't remove the color....
|
by: Timothy Madden |
last post by:
Hello
Is my program legal and well formed:
class Data;
class SMax
{
class Data &Buffer;
public:
SMax(class Data &buffer)
|
by: bluekite2000 |
last post by:
If it is what is this technique called? A good example for this would
be when I need to extract a column out of a matrix, then create a new
diagonal matrix out of this column, ie:
A.cols(3).diag()...
|
by: Marco Jez |
last post by:
Is it legal, in a class template's member function, to instantiate the same
class template but with a different type as argument? The following code is
very simple and I'd expect it to compile, but...
|
by: Frank Rizzo |
last post by:
I realize that this maybe the wrong forum, but maybe someone has run
into this situation before. I sell an app and I want to offer my
customers a migration path from a competing product. However,...
|
by: Erik de Castro Lopo |
last post by:
Hi all,
The GNU C compiler allows a void pointer to be incremented and
the behaviour is equivalent to incrementing a char pointer.
Is this legal C99 or is this a GNU C extention?
Thanks in...
|
by: jl_post |
last post by:
Hi,
I've heard that if you've declared a variable (such as a double or
an int) and not initialize it, then the result of printing out its
value is undefined.
I've also heard that "undefined...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |