Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    26

    Answered: HOW to SELECT Tables From TWO DB

    Hi all
    I have a project Containing 2 Databases.
    Both DB have multiple tables Connecting to Each other.
    for Example
    database A
    CustID Name, Address, City,Country

    database B
    OrderID, custID, Order, Ship_date, Instal_Date

    How to Join Select these two Databases
    I Should also mention that this is a VB.net Application and that at the current time I have 2 DNS's one for each DB.
    Thanks in advance
    Last edited by Viper5646; 07-16-15 at 22:30.
    VIPER

  2. Best Answer
    Posted by mirtheil

    "There is not a way to connect to all DB's on a server. However, if you connect to one DB, you can access tables in another by using the syntax in my first response where the DB name is specified before the table name."


  3. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Are the databases on the same server?
    What version of PSQL are you using?

    You should be able to specify the database name on the query. For example:
    Code:
    select f1, f2 from db1.table1, db2.table2
    where db1 and db2 are the Pervasive Database Names, not the ODBC DSN.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  4. #3
    Join Date
    Mar 2010
    Posts
    26
    Thank you Mirthell
    I am able to do this in PCC.
    How can I make a connection to the server from VS so I can Select from both DB's and use the same SELECT statement as I did in PCC
    VIPER

  5. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You can use ODBC, OLEDB, or the Managed PSQL provider (depending on the version of PSQL you are using) to connect to the PSQL server from VS / VB.
    Here's a very basic sample using C# that should be translatable to VB.
    Code:
    using System;
    using System.Data;
    using Pervasive.Data.SqlClient;
    using System.IO;
    
    namespace SimpleADONetTest
    {
    	class Class1
    	{
    		[STAThread]
    		static void Main(string[] args)
    		{
    			try
    			{
    				PsqlConnection conn=new PsqlConnection("ServerDSN=demodata;UID=;PWD=;Server=localhost;");
    				conn.Open();
    				Console.WriteLine("ServerName: " + conn.ServerName.ToString());
    				Console.WriteLine("ServerDSN: " + conn.ServerDSN.ToString());
    				// Create a SQL command
    				string strSQL = "select * from class";
    				PsqlCommand DBCmd = new PsqlCommand(strSQL, conn);
    				PsqlDataReader myDataReader;
    				myDataReader = DBCmd.ExecuteReader();
    				Console.WriteLine("FieldCount: " + myDataReader.FieldCount.ToString());
    				while (myDataReader.Read())
    				{
    					for (int i=0;i<myDataReader.FieldCount;i++)
    					{
    						Console.WriteLine("Field " + i.ToString() + ": " + myDataReader[i].ToString());
    					}
    				}
    				myDataReader.Close();
    				conn.Close();
    				Console.WriteLine("Press Enter to continue");
    				Console.ReadLine();
    			}
    			catch (Exception ex)
    			{
    				Console.WriteLine(ex.Message);
    				Console.ReadLine();
    			}
    			
    		}
    	}
    }
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #5
    Join Date
    Mar 2010
    Posts
    26
    Thanks again
    My PCC is version 10
    Your sample connection string only connects to one DB 'demodata'
    but my issue is how can I Select Data from 2 DB using one Select statement when my connection string is connecting to one DB.
    Is there a way to connect to all DB's on the server so I can Select data from multiple DB's.
    VIPER

  7. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    There is not a way to connect to all DB's on a server. However, if you connect to one DB, you can access tables in another by using the syntax in my first response where the DB name is specified before the table name.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  8. #7
    Join Date
    Mar 2010
    Posts
    26
    THANK YOU Mirtheil for your help.
    I wasn't able to select the data from both db's but to solve my Issue I created a view in the second DB of the first DB.
    Now I can Selected all tables.
    VIPER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •