Le 07/07/2014 18:28, Madhurima Das a écrit :
> Hi Pujol,
>
> Thanks a ton for your help!!
>
> I was missing the semicolon and it works fine now..
>
> Thanks,
> Madhurima
>
>
> On Mon, Jul 7, 2014 at 11:09 AM, Madhurima Das
> <madhurima(dot)das(at)gmail(dot)com <mailto:madhurima(dot)das(at)gmail(dot)com>> wrote:
>
> I just checked that anything after the line
>
> ALTER TABLE protein_sequence ADD comment VARCHAR(500) \n\
>
> does not work and gives the same syntax error as above.
>
>
>
>
> On Mon, Jul 7, 2014 at 10:56 AM, Madhurima Das
> <madhurima(dot)das(at)gmail(dot)com <mailto:madhurima(dot)das(at)gmail(dot)com>> wrote:
>
> Thanks once again.. However, I get a error after running the
> program as:
>
> Adding col to table (ALTER) Failed: ERROR: syntax error at or
> near "ELSE"
> LINE 5: ELSE
> ^
>
> Can you provide some suggestions.
>
>
>
> On Mon, Jul 7, 2014 at 10:46 AM, Pujol Mathieu
> <mathieu(dot)pujol(at)realfusio(dot)com
> <mailto:mathieu(dot)pujol(at)realfusio(dot)com>> wrote:
>
>
> Le 07/07/2014 13:44, Pujol Mathieu a écrit :
>>
>>
>> Le 07/07/2014 12:48, Albe Laurenz a écrit :
>>> Madhurima Das wrote:
>>>> I am writing a C program to access a PostgreSQL
>>>> database, where
>>>> I add a column if it doesn't exists in the table
>>>> or, update the column, if the column already exits.
>>>> Please suggest how to work with the conditional
>>>> statements.
>>>> N.B. I wrote the following:
>>>>
>>>> res = PQexec(conn, "IF
>>>> COL_LENGTH('protein_sequence','comment') IS NULL");
>>>> PQclear(res);
>>>> if(res)
>>>> {
>>>> res = PQexec(conn, "ALTER TABLE protein_sequence
>>>> ADD comment VARCHAR(500)");
>>>> PQclear(res);
>>>> }
>>>> else
>>>> {
>>>> res = PQexec(conn, "UPDATE TABLE protein_sequence
>>>> ADD comment VARCHAR(500)");
>>>> PQclear(res);
>>>> }
>>>>
>>>> Is the code logically correct??
>>> No, that doesn't make any sense.
>>> The statement sent with PQexec must be a legal SQL
>>> statement.
>>>
>>> You could do it like this:
>>>
>>> /* try the update */
>>> res = PQexec(conn, "UPDATE protein_sequence SET comment
>>> = ... WHERE ...");
>>> if (!res) {
>>> /* out of memory, error out */
>>> }
>>> r = PQresultStatus(res);
>>> PQclear(res);
>>> if (r == PGRES_COMMAND_OK) {
>>> return; /* UPDATE ok */
>>> } else if (r != PGRES_NONFATAL_ERROR) {
>>> /* unexpected result, error out */
>>> }
>>>
>>> /* add the column */
>>> res = PQexec(conn, "ALTER TABLE protein_sequence ADD
>>> comment VARCHAR(500)");
>>> if (!res) {
>>> /* out of memory, error out */
>>> }
>>> r = PQresultStatus(res);
>>> PQclear(res);
>>> if (r == PGRES_COMMAND_OK) {
>>> return; /* ALTER TABLE ok */
>>> } else {
>>> /* unexpected result, error out */
>>> }
>>>
>>> This code is untested.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>> Snippet Hi,
>> You can do that in a single statement
>> std::string lStatement;
>> lStatement += "DO $$\n"; lStatement += "BEGIN\n";
>> lStatement += "IF
>> COL_LENGTH('protein_sequence','comment') IS NULL THEN\n";
>> lStatement += "ALTER TABLE protein_sequence ADD comment
>> VARCHAR(500) \n";
>> lStatement += "ELSE\n";
>> lStatement += "UPDATE TABLE protein_sequence ADD comment
>> VARCHAR(500)\n";
>> lStatement += "END IF;\n";
>> lStatement += "END;\n";
>> lStatement += "$$;\n";
>> res = PQexec(conn, lStatement .c_str());
>> Regards,
>> Mathieu
>>
>>
>
> Hi,
> My answer is a C++ sample.
> This looks like you don't have include string header, or
> use this code if if you are writing pure C program.
>
> const char * lStatement ="\
> DO $$\n\
> BEGIN\n\
> IF COL_LENGTH('protein_sequence','comment') IS NULL THEN\n\
> ALTER TABLE protein_sequence ADD comment VARCHAR(500) \n\
> ELSE\n\
> UPDATE TABLE protein_sequence ADD comment VARCHAR(500)\n\
> END IF;\n\
> END;\n\
> $$;\n";
>
>
> Regards,
> Mathieu
>
>
>
>
Hi,
I'm glad that helps you. Just feew tips for using the mailing list. You
should reply to the mailing list pgsql-general(at)postgresql(dot)org( and not
only the person who answers you), so that other users could see that
your problem is solve. You should also write your answer at the bottom
of the mail to facilitate reading.
Regards,
Mathieu