Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: Update TSQL by PSQL database lookup

    I know virtually nothing about PSQL other than that which I have read here and googled...so I am asking if someone here could perhaps give me some guidance on code structure to accomplish the following:

    A record is inserted into TSQL (2008 64bit)
    after insert, query the PSQL (2011) database table using a TSQL column data
    retrieve row data from PSQL based on the TSQL column data
    insert (update the row) the retrieved data back into the TSQL record

    Any help would be greatly appreciated. Thank you.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I think the code structure is pretty close to what you describe but the bigger question is what language do you want to do this in?

    I'm curious, why are you doing this sequence? What is your end goal?
    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.

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    Thank you for your reply. I probably would want it to reside on the TSQL server in the form of a trigger.

    The end goal would be that when a record is indexed (inserted) into a TSQL table, in this case purchase_order, using the TSQL column po_number, query the PSQL purchase_order table by the po_number value that was just inserted into the TSQL column, grab other PSQL column values so those values could be then updated in the TSQL purchase_order table.

    I'm probably not explaining very well, but basically a database lookup to update values in a purchase order based on a key field. The idea is to only have a person have to manually enter in the purchase order number in an application with a TSQL 2008 database and have the rest of that data, which resides in a PSQL 2011 database, to auto populate. It is a canned application with no programming interface.

    Again, thank you.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    That clears up a lot.
    Within your trigger on the MS SQL side, you may (don't know, haven't tried) be able to use a Linked Server. If you can access the Linked Server, you can create a Linked Server to the PSQL database using the PSQL ODBC driver (I wouldn't use the OLEBD driver). Once the Linked Server is set up, you can then query the PSQL database and modify the MS SQL database.
    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.

  5. #5
    Join Date
    Apr 2011
    Posts
    4

    PSQL ODBC Driver

    I contacted Pervasive today today to see if there was a download of the ODBC driver, but their tech support said no and we never have released one. According to them you need a complete installation of PSQL. I cant have PSQL installed on this server. Any ideas which files, reg keys or whatever to enable the ODBC driver?

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What version of PSQL do you have? All versions since PSQL 2000 (PSQL 2000, v8, v9, v10, and v11) have included the ODBC driver in both the client and engine (server or workgroup) installs. You automatically get the driver if you run the PSQL install.

    With later versions of PSQL (v9 and later), you can disable parts of the install to make it smaller but you still need all of the client level components to get either the ODBC or OLEDB driver. The ODBC driver was released as a separate install back in the Btrieve 6.15 days (1996-ish) when it was first introduced. It hasn't been separate since though. Why can't PSQL be installed?
    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.

  7. #7
    Join Date
    Apr 2011
    Posts
    4

    PSQL ODBC Driver

    I appreciate your reply. The version is: Pervasive.SQL 10.20.0020.000. I have it installed on a development server here, but won't be allowed to be installed on the production server. That's why I'm trying to figure out how to enable ODBC here somehow. I've looked at the dsn for the demodata database and it uses w3odbcci.dll, but I can't register that as it has dependencies. I've tried locating 3rd party PSQL ODBC drivers, but can't seem to find any. So I'm looking for some suggestions or advice.

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    [quote]I have it installed on a development server here, but won't be allowed to be installed on the production server.[quote]
    Why won't you be allowed to installed it on the production server? And if you can't install PSQL, why would you be able to install third party drivers (which will still need the client components to communicate to the engine).

    You cannot just copy the W3ODBCCI.DLL and get it working. There are a number of other files and registry entries required. The best (and only supported) way is to run the install.
    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.

Posting Permissions

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