Re: make wxw & odbc

Kevin Gordon <[email protected]>
Newsgroups gmane.comp.lib.wxwindows.wxnet
Message-ID <[email protected]> 
On Sun, 18 Jul 2004 19:41, Alexander Olk wrote:
> You have to make a link in /usr/lib or /usr/local/lib to your libodbc.so
> (libodbc.so.1, libodbc.so.1.0.0 or whatever it is called)
>
> ln -s libodbc.so libodbc32.so
> or
> ln -s libodbc.so.1 libodbc32.so
>
> Otherwise mono can't find the odbc lib...
>
> For setting up unixODBC use a tool like gODBCConfig...
> (for postgresql use Driver=/usr/<local/>lib/postgresql/lib/psqlodbc.so
> and Setup=/usr/<local/>lib/odbc/libodbcpsqlS.so)
>
> Hope that helps...
******************************************************************************************
Many thanks
# ln -s libodbc.so libodbc32.so
enable me to use unixODBC.
I have attached my config files for unixODBC, a pgdump of my Postgresql 
database, and a modified dbgrid.cs. Any comments for improvements would be 
much appreciated.

How do I change the colours of:
Scroll bars
Menu bars
Status bars?

I wish to use your example to see how far I can go exploring ADO.NET in Linux 
using wx.NET.

Both Npgsql and ODBC compile and run in MonoDevelop without any problems.

Kevin Gordon
New Zealand
Dbgrid.cs (text/x-c++src, 11.1 KB) 
//-----------------------------------------------------------------------------
// wx.NET/Samples - Dbgrid.cs
//
// wx.NET "dbgrid" sample.
//
// Written by Alexander Olk ([email protected])
// (C) 2003 by Alexander Olk
// Licensed under the wxWidgets license, see LICENSE.txt for details.
//
// $Id: Dbgrid.cs,v 1.7 2004/02/28 03:13:39 malenfant Exp $
//-----------------------------------------------------------------------------

using System;
using System.Drawing;
using System.Data;
using System.Data.Odbc;
using wx.DataAccess;

namespace wx.Samples
{ 
	public class MyDbGrid : DbGrid
	{
		private MyFrame myFrame;
		
		//---------------------------------------------------------------------
		
		public MyDbGrid(MyFrame parent, int id, Point pos, Size size)
			: base(parent, id, pos, size)
		{
			myFrame = parent;  
			
			EVT_GRID_CELL_LEFT_CLICK(new EventListener(OnGridCellLeftClick));		
		}
		
		//---------------------------------------------------------------------
		
		public void OnGridCellLeftClick(object sender, Event e)
		{
			GridEvent ge = (GridEvent) e;
			DataRow dr = GetRow(ge.Row);
			
			myFrame.ClientIDText = dr["ClientID"].ToString();
			myFrame.LastNameText = dr["LastName"].ToString();
			myFrame.FirstNameText = dr["FirstName"].ToString();
			myFrame.CRMText = dr["CRM"].ToString();

			e.Skip();
		}
	}
	
	//---------------------------------------------------------------------

	public class MyFrame : wx.Frame
	{
		enum Cmd { About, Quit, Save, Row }
		
		private OdbcConnection dbcon;
		private OdbcDataAdapter dbadapter;
		private MyDbGrid grid;
		private DbGrid grid2;
		
		private TextCtrl ClientID_text;
		private TextCtrl LastName_text;
		private TextCtrl FirstName_text;
		private TextCtrl CRM_text;
		private Button Save_Btn;
		private Button New_Btn;
		private Colour pale_orange = new Colour(238,199,7);
		private Colour dark_orange = new Colour(204,171,6);
		private Colour pale_yellow = new Colour(255,255,155);

		//---------------------------------------------------------------------

		public MyFrame(string title, Point pos, Size size)
			: base(title, pos, size)
		{
			// Set the window icon 

			Icon = new wx.Icon("/root/CSharp/DbGrid/mondrian.png");

			// Set up a menu

			wx.Menu fileMenu = new wx.Menu();
			fileMenu.Append((int)Cmd.Quit, "E&xit\tAlt-X", "Quit this program");

			wx.Menu helpMenu = new wx.Menu();
			helpMenu.Append((int)Cmd.About, "&About...\tF1", "Show about dialog");
			
			wx.MenuBar menuBar = new wx.MenuBar();
			menuBar.Append(fileMenu, "&File");
			menuBar.Append(helpMenu, "&Help");

			MenuBar = menuBar;
			
			BoxSizer new_main_sizer = new BoxSizer( Orientation.wxHORIZONTAL );
			
			BoxSizer main_sizer = new BoxSizer( Orientation.wxVERTICAL );
			
			main_sizer.Add( new StaticText( this, -1, "MyDbGrid"), 0, Direction.wxALL, 5 );
			
			grid = new MyDbGrid( this, -1, wxDefaultPosition, new Size(580,200) );			
			
			main_sizer.Add(grid, 1, Direction.wxALL, 5 );
			
			main_sizer.Add( new StaticLine( this, -1, wxDefaultPosition, wxDefaultSize, StaticLine.wxLI_HORIZONTAL ), 0, Stretch.wxEXPAND | Direction.wxALL, 5 );
			main_sizer.Add( new StaticText( this, -1, "Second DbGrid CreateGridFromColumnMapping (does nothing, shows only creation with this method)"), 0, Direction.wxALL, 5 );
			
			grid2 = new DbGrid(this, -1, wxDefaultPosition, new Size(580,200));
			
			main_sizer.Add(grid2, 1, Direction.wxALL, 5 );
			
			new_main_sizer.Add(main_sizer, 0, Direction.wxALL, 0);
			
			FlexGridSizer fgs = new FlexGridSizer( 5, 2, 5, 5 );
			
			fgs.Add( new StaticText( this, -1, "ID:" ), 0, Direction.wxALL | Alignment.wxALIGN_RIGHT, 2 );
			
			ClientID_text = new TextCtrl( this, -1, "", wxDefaultPosition, new Size( 100, 21 ) );
			ClientID_text.BackgroundColour = pale_yellow;
			fgs.Add( ClientID_text, 0, Direction.wxALL | Alignment.wxALIGN_LEFT, 2);
			
			fgs.Add( new StaticText( this, -1, "Surname:" ), 0, Direction.wxALL | Alignment.wxALIGN_RIGHT, 2 );
			
			LastName_text = new TextCtrl( this, -1, "", wxDefaultPosition, new Size( 100, 21 ) );
			LastName_text.BackgroundColour = pale_yellow;
			fgs.Add( LastName_text, 0, Direction.wxALL | Alignment.wxALIGN_LEFT, 2);
			
			fgs.Add( new StaticText( this, -1, "First Name:" ), 0, Direction.wxALL | Alignment.wxALIGN_RIGHT, 2 );
			
			FirstName_text = new TextCtrl( this, -1, "", wxDefaultPosition, new Size( 100, 21 ) );
			FirstName_text.BackgroundColour = pale_yellow;
			fgs.Add( FirstName_text, 0, Direction.wxALL | Alignment.wxALIGN_LEFT, 2);
			
			fgs.Add( new StaticText( this, -1, "CRM:" ), 0, Direction.wxALL | Alignment.wxALIGN_RIGHT, 2 );
			
			CRM_text = new TextCtrl( this, -1, "", wxDefaultPosition, new Size( 100, 21 ) );
			CRM_text.BackgroundColour = pale_yellow;
			fgs.Add( CRM_text, 0, Direction.wxALL | Alignment.wxALIGN_LEFT, 2);		

			Save_Btn = new Button(this, (int)Cmd.Save, "Save");
			Save_Btn.BackgroundColour = dark_orange;
			fgs.Add( Save_Btn, 0, Direction.wxALL, 2 );
			New_Btn = new Button(this, (int)Cmd.Row, "New Row");
			New_Btn.BackgroundColour = dark_orange;
			fgs.Add( New_Btn, 0, Direction.wxALL, 2 );
			
			new_main_sizer.Add( new StaticLine( this, -1, wxDefaultPosition, wxDefaultSize, StaticLine.wxLI_VERTICAL ), 0, Stretch.wxEXPAND | Direction.wxALL, 5 );			
			new_main_sizer.Add(fgs, 0, Direction.wxALL, 5);
			
			AutoLayout = true;
			SetSizer( new_main_sizer, true );
			new_main_sizer.Fit( this );
			new_main_sizer.SetSizeHints( this );

			// Set up a status bar

			CreateStatusBar(2);
			StatusText = "Welcome to wxWidgets!";

			// Set up Npgsql and DbGrid
			try
				{
				dbcon = new OdbcConnection();
				//dbcon = new NpgsqlConnection("Server=10.1.1.12;Port=5432;User Id=kevin;Password=;Database=kgdb;");

				dbcon.ConnectionString ="DSN=kgdbremote;UID=kevin";   //;DATABASE=kgdb
				
				dbcon.Open();
				
				//dbadapter = new NpgsqlDataAdapter("SELECT * FROM Client order by LastName, FirstName", dbcon);			
				dbadapter = new OdbcDataAdapter("SELECT * FROM Client order by LastName, FirstName", dbcon);			
													
				dbadapter.Fill(grid.dataSet, "Client");
				dbadapter.Fill(grid2.dataSet, "Client");
						
				// do the mapping before CreateGrid
				// db columnname, new columnname, optional columnwidth
				grid.AddColumnMapping("ClientID", "ID", 40);						
				grid.AddColumnMapping("LastName", "Surname", 120);
				grid.AddColumnMapping("FirstName", "First Name",120);
				grid.AddColumnMapping("CRM", "Account Manager", 120);
				grid.SetDefaultRowSize(20, true);
				grid.LabelBackgroundColour = dark_orange;
				grid.CellBackgroundColour = pale_yellow; //Colour.wxCYAN;
				
				grid.CreateGridFromDataSet("Client");  // creates grid with the data in grid.dataSet

				grid2.AddColumnMapping("CRM", "Account Manager", 120);
				grid2.AddColumnMapping("FirstName", "First Name",120);			
				//grid2.AddColumnMapping("id", "ID", 40);						
				grid2.AddColumnMapping("LastName", "Surname", 120);
				grid2.SetDefaultRowSize(20, true);
				grid2.LabelBackgroundColour = dark_orange;
				grid2.CellBackgroundColour = pale_yellow; //Colour.wxCYAN;
				
				grid2.CreateGridFromColumnMapping("Client");  // creates grid with the data in grid.dataSet
				
				DataRow dr = grid.GetRow(0);
				
				if ( dr != null )
				{
					ClientID_text.Value = dr["ClientID"].ToString();
					LastName_text.Value = dr["LastName"].ToString();
					FirstName_text.Value = dr["FirstName"].ToString();
					CRM_text.Value = dr["CRM"].ToString();
				}

				}
			catch(OdbcException e)
				{
				Console.WriteLine("An Exception was thrown");
				Console.WriteLine("Message = " + e.Message);
				Console.WriteLine("StackTrace:\n" + e.StackTrace);
				Console.WriteLine("Source = " + e.Source);
				}
			catch (Exception ex)
				{
				MessageDialog.ShowModal(this, ex.ToString(), "Error", Dialog.wxOK | Dialog.wxICON_ERROR);						
				}
			// Set up the event table

			EVT_MENU((int)Cmd.Quit,    new EventListener(OnQuit));
			EVT_MENU((int)Cmd.About,   new EventListener(OnAbout));
			EVT_BUTTON((int)Cmd.Save, new EventListener(OnSave));
			EVT_BUTTON((int)Cmd.Row, new EventListener(OnNewRow));
		} 
		
		//---------------------------------------------------------------------
		
		public string ClientIDText
		{
			set { ClientID_text.Value = value; }
		}
		
		//---------------------------------------------------------------------
		
		public string LastNameText
		{
			set { LastName_text.Value = value; }
		}
		
		//---------------------------------------------------------------------
		
		public string FirstNameText
		{
			set { FirstName_text.Value = value; }
		}
		
		//---------------------------------------------------------------------
		
		public string CRMText
		{
			set { CRM_text.Value = value; }
		}
		
		//---------------------------------------------------------------------

		public void OnQuit(object sender, wx.Event e)
		{
			dbcon.Close();
			Close();
		}

		//---------------------------------------------------------------------

		public void OnAbout(object sender, wx.Event e)
		{
			string msg = "This is the About dialog of the DbGrid sample.";
			wx.MessageDialog.ShowModal(this, msg, "About DbGrid", Dialog.wxOK | Dialog.wxICON_INFORMATION);
		}

		//---------------------------------------------------------------------
		
		public void OnSave(object sender, Event e)
		{
			System.Data.DataSet ds = grid.dataSet.GetChanges();
			if (ds == null ) return;
			
			DataTable table = ds.Tables["Client"];
			
			foreach (DataRow row in table.Rows) {
			
				int i = 0;
				string[]  parm = new string[4];
				
				foreach (DataColumn col in table.Columns) {
					parm[i]=row[col].ToString();
					i++;
				}
				
				string dbstring = "";
				
				switch (row.RowState)
				{
					case DataRowState.Added:
						dbstring = "INSERT INTO Client(ClientID, LastName, FirstName, CRM) " +
								"VALUES ( '"+parm[0]+"', '"+parm[1]+"', '"+parm[2]+"', '"+parm[3]+"')";
					break;
					
					case DataRowState.Modified:
						dbstring = "UPDATE Client SET "+
								"LastName = '"+parm[1]+"', FirstName = '"+parm[2]+"', CRM = '"+parm[3]+"' WHERE ClientID = '"+parm[0]+"'";
					break;
				}
				
				if (dbstring.Length > 0)
				{
					//Npgsql.NpgsqlCommand updatecmd = new NpgsqlCommand(dbstring, dbcon);
					OdbcCommand updatecmd = new OdbcCommand(dbstring, dbcon);
					try
					{
						updatecmd.ExecuteNonQuery();
					} 
					catch (Exception ex)
					{
						MessageDialog.ShowModal(this, ex.ToString(), "Error", Dialog.wxOK | Dialog.wxICON_ERROR);						
					}
					grid.dataSet.AcceptChanges();
				}
			}
		}		

		//---------------------------------------------------------------------

		public void OnNewRow(object sender, Event e)
		{
				grid.AddRow(); 
		}
	}
	
	//---------------------------------------------------------------------

	public class DBGRID : wx.App
	{
		private Colour pale_orange = new Colour(238,199,7);
		private Colour dark_orange = new Colour(204,171,6);
		private Colour pale_yellow = new Colour(255,255,155);
		
		public override bool OnInit()
		{
			MyFrame frame = new MyFrame("DbGrid wxWidgets App", new Point(50,50), new Size(700,400));
			frame.Show(true);
			frame.BackgroundColour = pale_orange;

			return true;
		}

		//---------------------------------------------------------------------

		[STAThread]
		static void Main()
		{
			DBGRID app = new DBGRID();
			app.Run();
		}
	}
}
odbc.ini (text/plain, 640 B) 
[kgdbsource]
Description		= psqlODBC Data Source
Driver		= psqlODBC
Trace		= Yes
TraceFile		= sql.log
Database		= kgdb
Servername		= localhost
UserName		= kevin
Password		= 
Port		= 5432
Protocol		= 6.4
ReadOnly		= No
RowVersioning		= Yes
ShowSystemTables		= Yes
ShowOidColumn		= No
FakeOidIndex		= No
ConnSettings		= 

[kgdbremote]
Description		= psqlODBC Data Source
Driver		= psqlODBC
Trace		= Yes
TraceFile		= sql.log
Database		= kgdb
Servername		= 10.1.1.12
Username		= kevin
Password		= 
Port		= 5432
Protocol		= 6.4
ReadOnly		= No
RowVersioning		= Yes
ShowSystemTables		= Yes
ShowOidColumn		= No
FakeOidIndex		= No
ConnSettings		=
kgdb.sql (text/x-c++src, 5.4 KB) 
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 4 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


SET SESSION AUTHORIZATION 'kevin';

SET search_path = public, pg_catalog;

--
-- TOC entry 5 (OID 25669)
-- Name: client_clientid_seq; Type: SEQUENCE; Schema: public; Owner: kevin
--

CREATE SEQUENCE client_clientid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 11 (OID 25671)
-- Name: client; Type: TABLE; Schema: public; Owner: kevin
--

CREATE TABLE client (
    clientid integer DEFAULT nextval('Client_ClientID_seq'::text) NOT NULL,
    lastname character varying(50) NOT NULL,
    firstname character varying(50),
    crm character varying(50)
);


--
-- TOC entry 7 (OID 25676)
-- Name: clientreporttype_reporttypeid_seq; Type: SEQUENCE; Schema: public; Owner: kevin
--

CREATE SEQUENCE clientreporttype_reporttypeid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 12 (OID 25678)
-- Name: clientreporttype; Type: TABLE; Schema: public; Owner: kevin
--

CREATE TABLE clientreporttype (
    reporttypeid integer DEFAULT nextval('ClientReportType_ReportTypeID_seq'::text) NOT NULL,
    clientid integer NOT NULL,
    typename character varying(2) NOT NULL,
    startdate date,
    enddate date,
    frequency character(1)
);


--
-- TOC entry 9 (OID 25687)
-- Name: reportdate_reportdatesid_seq; Type: SEQUENCE; Schema: public; Owner: kevin
--

CREATE SEQUENCE reportdate_reportdatesid_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 13 (OID 25689)
-- Name: reportdate; Type: TABLE; Schema: public; Owner: kevin
--

CREATE TABLE reportdate (
    reportdatesid integer DEFAULT nextval('ReportDate_ReportDatesID_seq'::text) NOT NULL,
    reporttypeid integer NOT NULL,
    duedate date,
    receiveddate date,
    checkeddate date,
    completeddate date
);


--
-- TOC entry 14 (OID 25700)
-- Name: cts2view; Type: VIEW; Schema: public; Owner: kevin
--

CREATE VIEW cts2view AS
    SELECT client.lastname, client.firstname, client.crm, clientreporttype.typename, clientreporttype.clientid, clientreporttype.startdate, clientreporttype.enddate, clientreporttype.frequency FROM (client JOIN clientreporttype ON ((client.clientid = clientreporttype.clientid)));


--
-- TOC entry 15 (OID 25703)
-- Name: ctsview1; Type: VIEW; Schema: public; Owner: kevin
--

CREATE VIEW ctsview1 AS
    SELECT client.lastname, client.firstname, clientreporttype.clientid, clientreporttype.typename, client.crm, reportdate.reporttypeid, reportdate.duedate, reportdate.receiveddate, reportdate.checkeddate, reportdate.completeddate FROM ((client JOIN clientreporttype ON ((client.clientid = clientreporttype.clientid))) JOIN reportdate ON ((clientreporttype.reporttypeid = reportdate.reporttypeid)));


--
-- Data for TOC entry 19 (OID 25671)
-- Name: client; Type: TABLE DATA; Schema: public; Owner: kevin
--

INSERT INTO client VALUES (1, 'smith', 'john', 'don');
INSERT INTO client VALUES (2, 'brown', 'jerry', 'kevin');
INSERT INTO client VALUES (3, 'gordon', 'kelly', 'plh');
INSERT INTO client VALUES (4, 'woods', 'tiger', 'kg1');
INSERT INTO client VALUES (5, 'knows', 'He', 'don');
INSERT INTO client VALUES (6, 'Knoel', 'Tim', 'kg1');
INSERT INTO client VALUES (7, 'Muir', 'Frank', 'kg1');


--
-- Data for TOC entry 20 (OID 25678)
-- Name: clientreporttype; Type: TABLE DATA; Schema: public; Owner: kevin
--



--
-- Data for TOC entry 21 (OID 25689)
-- Name: reportdate; Type: TABLE DATA; Schema: public; Owner: kevin
--



--
-- TOC entry 16 (OID 25674)
-- Name: client_pkey; Type: CONSTRAINT; Schema: public; Owner: kevin
--

ALTER TABLE ONLY client
    ADD CONSTRAINT client_pkey PRIMARY KEY (clientid);


--
-- TOC entry 17 (OID 25681)
-- Name: clientreporttype_pkey; Type: CONSTRAINT; Schema: public; Owner: kevin
--

ALTER TABLE ONLY clientreporttype
    ADD CONSTRAINT clientreporttype_pkey PRIMARY KEY (reporttypeid);


--
-- TOC entry 18 (OID 25692)
-- Name: reportdate_pkey; Type: CONSTRAINT; Schema: public; Owner: kevin
--

ALTER TABLE ONLY reportdate
    ADD CONSTRAINT reportdate_pkey PRIMARY KEY (reportdatesid);


--
-- TOC entry 22 (OID 25683)
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: kevin
--

ALTER TABLE ONLY clientreporttype
    ADD CONSTRAINT "$1" FOREIGN KEY (clientid) REFERENCES client(clientid);


--
-- TOC entry 23 (OID 25694)
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: kevin
--

ALTER TABLE ONLY reportdate
    ADD CONSTRAINT "$1" FOREIGN KEY (reporttypeid) REFERENCES clientreporttype(reporttypeid);


--
-- TOC entry 6 (OID 25669)
-- Name: client_clientid_seq; Type: SEQUENCE SET; Schema: public; Owner: kevin
--

SELECT pg_catalog.setval('client_clientid_seq', 5, true);


--
-- TOC entry 8 (OID 25676)
-- Name: clientreporttype_reporttypeid_seq; Type: SEQUENCE SET; Schema: public; Owner: kevin
--

SELECT pg_catalog.setval('clientreporttype_reporttypeid_seq', 4, true);


--
-- TOC entry 10 (OID 25687)
-- Name: reportdate_reportdatesid_seq; Type: SEQUENCE SET; Schema: public; Owner: kevin
--

SELECT pg_catalog.setval('reportdate_reportdatesid_seq', 1, false);


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';
odbcinst.ini (text/plain, 190 B) 
[ODBC]
Trace		= No
Trace File		= /tmp/sql.log
Pooling		= Yes

[psqlODBC]
Description		= Postgresql Driver
Driver		= /usr/lib/libodbcpsql.so
Setup		= /usr/lib/libodbcpsqlS.so
FileUsage		= 1
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.