Thread: How to get the data from the query
Hi, ALL,
[code]
std::wstring query2 = L"SELECT DISTINCT column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullable, column_default,
CASE WHEN column_name IN (SELECT ccu.column_name FROM
information_schema.constraint_column_usage ccu,
information_schema.table_constraints tc WHERE ccu.constraint_name =
tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND
ccu.table_name = 'leagues') THEN 'YES' ELSE 'NO' END AS is_pk,
ordinal_position FROM information_schema.columns col,
information_schema.table_constraints tc WHERE tc.table_schema =
col.table_schema AND tc.table_name = col.table_name AND
col.table_schema = $1 AND col.table_name = $2 ORDER BY
ordinal_position;";
res2 = PQprepare( m_db, "get_columns",
m_pimpl->m_myconv.to_bytes( query2.c_str() ).c_str(), 2, NULL );
if( PQresultStatus( res2 ) != PGRES_COMMAND_OK )
{
std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
errorMsg.push_back( L"Error executing query: " + err );
PQclear( res2 );
result = 1;
}
else
{
PQclear( res2 );
......
res2 = PQexecPrepared( m_db, "get_columns", 2, values1,
length1, formats1, 1 );
status = PQresultStatus( res2 );
if( status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK )
{
std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
errorMsg.push_back( L"Error executing query: " + err );
PQclear( res2 );
fields.erase( fields.begin(), fields.end() );
foreign_keys.erase( foreign_keys.begin(), foreign_keys.end() );
result = 1;
}
else if( status == PGRES_TUPLES_OK )
{
int numFields = 0;
for( int j = 0; j < PQntuples( res2 ); j++ )
{
int size, precision;
bool autoinc = false;
const char *field_name = PQgetvalue( res2, j, 0 );
fieldName = m_pimpl->m_myconv.from_bytes( field_name );
fieldType = m_pimpl->m_myconv.from_bytes( PQgetvalue(
res2, j, 1 ) );
char *char_length = PQgetvalue( res2, j, 2 );
char *numeric_length = PQgetvalue( res2, j, 4 );
char *numeric_scale = PQgetvalue( res2, j, 6 );
fieldDefaultValue = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res2, j, 8 ) );
fieldIsNull = !strcmp( PQgetvalue( res2, j, 7 ), "YES"
) ? 1 : 0;
fieldPK = !strcmp( PQgetvalue( res2, j, 9 ), "YES" ) ? 1 : 0;
if( *char_length == '0' )
{
size = atoi( numeric_length );
precision = atoi( numeric_scale );
}
else
{
size = atoi( char_length );
precision = 0;
}
if( fieldType == L"serial" || fieldType == L"bigserial" )
autoinc = true;
[/code]
I'm able to get field name and field type, but the size comes out "" (empty)
.
WHat am I doing wrong?
Thank you.
On Saturday, April 26, 2025, Igor Korot <ikorot01@gmail.com> wrote:
but the size comes out "" (empty)
What is the value you are expecting? What is the minimal table definition (i.e., a one or few column table) that would produce this expected value? What does psql show if you use it to output the query against that table?
David J.
Hi, David,
On Sat, Apr 26, 2025 at 8:06 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, April 26, 2025, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> but the size comes out "" (empty)
>>
>
> What is the value you are expecting?
Expecting to have "100".
What is the minimal table definition (i.e., a one or few column
table) that would produce this expected value?
CREATE TABLE IF NOT EXISTS leagues(id integer primary key, name
varchar(100), drafttype integer, scoringtype integer, roundvalues
integer, leaguetype char(5), salary integer, benchplayers integer);
Field "name" should be 100.
What does psql show if you use it to output the query against that table?
draft=# SELECT column_name, data_type, character_maximum_length,
numeric_precision, table_name FROM information_schema.columns WHERE
column_name = 'name' ORDER BY tabl
e_name;
column_name | data_type | character_maximum_length |
numeric_precision | table_name
-------------+-------------------+--------------------------+-------------------+---------------------------------
name | text | |
| abcß
name | character varying | 100 |
| leagues
name | name | |
| pg_available_extension_versions
name | name | |
| pg_available_extensions
name | text | |
| pg_config
name | text | |
| pg_cursors
name | text | |
| pg_file_settings
name | text | |
| pg_prepared_statements
name | text | |
| pg_settings
name | text | |
| pg_shmem_allocations
name | text | |
| pg_stat_slru
name | text | |
| pg_timezone_names
name | character varying | 70 |
| players
name | character varying | 50 |
| teams
(14 rows)
draft=#
Query is shorter, but it gives the result of 100.
Let me try the full one from my code...
Thank you.
>
> David J.
>
Now this query still works:
[code]
draft=# SELECT DISTINCT column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullabl
e, column_default, ordinal_position FROM information_schema.columns
WHERE table_name='leagues' ORDER BY ordinal_position;
column_name | data_type | character_maximum_length |
character_octet_length | numeric_precision | numeric_precision_radix |
numeric_scale | is_nullable | col
umn_default | ordinal_position
--------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+-------------+----
------------+------------------
id | integer | |
| 32 | 2 |
0 | NO |
| 1
name | character varying | 100 |
400 | | |
| YES |
| 2
drafttype | integer | |
| 32 | 2 |
0 | YES |
| 3
scoringtype | integer | |
| 32 | 2 |
0 | YES |
| 4
roundvalues | integer | |
| 32 | 2 |
0 | YES |
| 5
leaguetype | character | 5 |
20 | | |
| YES |
| 6
salary | integer | |
| 32 | 2 |
0 | YES |
| 7
benchplayers | integer | |
| 32 | 2 |
0 | YES |
| 8
(8 rows)
[/code]
Thank you.
On Sat, Apr 26, 2025 at 8:06 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, April 26, 2025, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> but the size comes out "" (empty)
>>
>
> What is the value you are expecting? What is the minimal table definition (i.e., a one or few column table) that
wouldproduce this expected value? What does psql show if you use it to output the query against that table?
>
> David J.
>
Running this query:
draft=# SELECT DISTINCT column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullabl
e, column_default, ordinal_position, CASE WHEN column_name IN (SELECT
ccu.column_name FROM information_schema.constraint_column_usage ccu,
information_schema.table_con
straints tc WHERE ccu.constraint_name = tc.constraint_name AND
tc.constraint_type = 'PRIMARY KEY' AND ccu.table_name = 'leagues' )
THEN 'YES' ELSE 'NO' END AS is_pk F
ROM information_schema.columns WHERE table_name='leagues' ORDER BY
ordinal_position;
still produces good results:
column_name | data_type | character_maximum_length |
character_octet_length | numeric_precision | numeric_precision_radix |
numeric_scale | is_nullable | col
umn_default | ordinal_position | is_pk
--------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+-------------+----
------------+------------------+-------
id | integer | |
| 32 | 2 |
0 | NO |
| 1 | YES
name | character varying | 100 |
400 | | |
| YES |
| 2 | NO
drafttype | integer | |
| 32 | 2 |
0 | YES |
| 3 | NO
scoringtype | integer | |
| 32 | 2 |
0 | YES |
| 4 | NO
roundvalues | integer | |
| 32 | 2 |
0 | YES |
| 5 | NO
leaguetype | character | 5 |
20 | | |
| YES |
| 6 | NO
salary | integer | |
| 32 | 2 |
0 | YES |
| 7 | NO
benchplayers | integer | |
| 32 | 2 |
0 | YES |
| 8 | NO
(8 rows)
lines 1-12/12 (END)
Thank you.
On Sat, Apr 26, 2025 at 8:06 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, April 26, 2025, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> but the size comes out "" (empty)
>>
>
> What is the value you are expecting? What is the minimal table definition (i.e., a one or few column table) that
wouldproduce this expected value? What does psql show if you use it to output the query against that table?
>
> David J.
>