Re: [compgeneral] I cant get the description or default value of a field

Forwarding to official PostgreSQL's ODBC mailing list...



-----Original Message-----
From: compgeneral@googlegroups.com [mailto:compgeneral@googlegroups.com]
On Behalf Of serkane
Sent: Monday, June 12, 2006 5:56 PM
To: compgeneral
Subject: [compgeneral] I cant get the description or default value of a
field


Hi everyone

excuse me for my english is not very well :(

I use the asp, vb 6 (with sp6) and .net vb to development. And I use
the postgres_odbc driver to connect the postgres server.

I need to know a field description of a table. But I can not get the
neither field description or field default value.

to learn is the code wrong or right, I've tried it another db
connection (ex. SQL2K, ORA, Foxpro). And there wasnt error. the code
has run corectly.

I wonder if the postgres doesnt provide these properties by the ADO.

thanks in advance


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "compgeneral" group.
To post to this group, send email to compgeneral@googlegroups.com
To unsubscribe from this group, send email to
compgeneral-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/compgeneral
-~----------~----~----~----~------~----~------~--~---


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: [compgeneral] I cant get the description or default value of a field

> excuse me for my english is not very well :(

That's no such problem in english. But I see few information from you :-(
What's PgSQL version, psqlodbc version, how you try get the informations?

> I use the asp, vb 6 (with sp6) and .net vb to development. And I use
> the postgres_odbc driver to connect the postgres server.
>
> I need to know a field description of a table. But I can not get the
> neither field description or field default value.

Could you send us code snipset (how you try to get the information)?
BTW mylog output could be interesting too.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: I cant get the description or default value of a field

I Use the version 8.1.4 of PostgreSQL on Windows 2003 Server as OS.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: I cant get the description or default value of a field

Set Rs = Cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName,
Empty))
Do While Not Rs.EOF
Debug.Print Rs("ORDINAL_POSITION")
Debug.Print Rs("COLUMN_NAME")
Debug.Print Rs("DATA_TYPE")
Debug.Print Rs("COLUMN_DEFAULT")
Debug.Print Rs("IS_NULLABLE")
If IsNull(Rs("CHARACTER_MAXIMUM_LENGTH")) = False Then
Debug.Print Rs("CHARACTER_MAXIMUM_LENGTH")
Debug.Print "0" '** Num_scale=0
Else
Debug.Print Rs("NUMERIC_PRECISION")
Debug.Print Rs("NUMERIC_SCALE")
End If
Debug.Print Rs("DESCRIPTION")
Debug.Print Rs("DISPLAY_SIZE")
Debug.Print Rs("COLUMN_HASDEFAULT"), Rs("COLUMN_DEFAULT")
Rs.MoveNext
Loop
Call RsClose


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: [compgeneral] I cant get the description or default valueof a field

This is a multi-part message in MIME format.
--------------080208010607090203000101
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

There is a technique at the ADO (VB) level. I do not know if the lower level ADO calls and the driver
support what your are trying to do.

Code Level:
============================================================ ========================================
Dim strConn as String
Dim conn As ADODB.Connection
Dim rs As Recordset
Dim fld As Field


strConn = _
"DRIVER={PostgreSQL};SERVER=my_sever_address;port=5432;DATAB ASE=my_database_name;UID=my_user_id;PWD=my_password;"

Set conn = New Connection
conn.Open strConn

Debug.Print "The connection is open"

'this will query for the schema for a table named employees
'see MSDN for specification on parameters and the array
'return a recordset contain information about the schema
'there is a record for each schema item , in this case 1 per field
Set rs = conn.OpenSchema(adSchemaColumns, Array("", "", "employees"))

If Not rs.EOF Then
Debug.Print "There are records"
Do While Not rs.EOF
'each field is a different schema descriptor for the current employee field
For Each fld In rs.Fields
'just comment out the If and End If so see all available schema descriptor field
If (fld.Name = "COLUMN_NAME") Or _
(fld.Name = "COLUMN_DEFAULT") Or _
(fld.Name = "FIELD_TYPE") Or _
(fld.Name = "DESCRIPTION") Then

Debug.Print fld.Name & ":" & vbTab & fld.Value

End If
Next
rs.MoveNext 'each record is a field in employee
Debug.Print "-----------------------------"
Loop
Else
Debug.Print "No records found"
End If

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing
============= end of code snippet =========================================================
This code should provide the schema information, particularly the column name, default, field_type ( a
number which must be interpreted using the ADO DataType Enum see MSDN or use Object Browser),
and the Description.

In practice the driver I am using did not return the COLUMN_DEFAULT value, and I do not have many tables
with a description attached to the field. I am rather ruthless about using obvious column names. If you
want to dig into what gets returned into the schema, you can of course turn on ODBC tracing, and the
Postgresql Driver setting for MyLog. You may uncover an ODBC driver feature that has a bug or is not
supported in the existing drivers.



Gurjeet Singh wrote:

> Forwarding to official PostgreSQL's ODBC mailing list...
>
>
>
> -----Original Message-----
> From: compgeneral@googlegroups.com [mailto:compgeneral@googlegroups.com]
> On Behalf Of serkane
> Sent: Monday, June 12, 2006 5:56 PM
> To: compgeneral
> Subject: [compgeneral] I cant get the description or default value of a
> field
>
>
> Hi everyone
>
> excuse me for my english is not very well :(
>
> I use the asp, vb 6 (with sp6) and .net vb to development. And I use
> the postgres_odbc driver to connect the postgres server.
>
> I need to know a field description of a table. But I can not get the
> neither field description or field default value.
>
> to learn is the code wrong or right, I've tried it another db
> connection (ex. SQL2K, ORA, Foxpro). And there wasnt error. the code
> has run corectly.
>
> I wonder if the postgres doesnt provide these properties by the ADO.
>
> thanks in advance
>
>
> --~--~---------~--~----~------------~-------~--~----~
> You received this message because you are subscribed to the Google
> Groups "compgeneral" group.
> To post to this group, send email to compgeneral@googlegroups.com
> To unsubscribe from this group, send email to
> compgeneral-unsubscribe@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/compgeneral
> -~----------~----~----~----~------~----~------~--~---
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--------------080208010607090203000101
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------080208010607090203000101
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--------------080208010607090203000101--