NetTalk Central

Author Topic: MSSQL - multiple databases ?  (Read 7736 times)

bramkip

  • Jr. Member
  • **
  • Posts: 61
    • View Profile
    • Intellisoft
    • Email
MSSQL - multiple databases ?
« on: February 23, 2013, 06:52:14 AM »
Hi,

I want to connect to different ms-sql databases based on a variable on the loginscreen. I'm using FM3. Is this possible?

Best regards,

Bram Kip

Rene Simons

  • Hero Member
  • *****
  • Posts: 650
    • View Profile
Re: MSSQL - multiple databases ?
« Reply #1 on: February 23, 2013, 11:07:31 AM »
Hi,
Provided these databases are based on the same dct, I guess you can adjust the GLO:owner string in the process link embed (before parent) in the webserver procedure.
The personalised glo:owner string mus be determined at login time. Also the glo:owner string must be thread safe and auto login must be active. This is no problem since you are using FM3.

Please correct me if i am wrong about this or if I Forgot something.

Rene
Rene Simons
NT14.14

kevin plummer

  • Hero Member
  • *****
  • Posts: 1195
    • View Profile
    • Production Accounting and Software Payroll
Re: MSSQL - multiple databases ?
« Reply #2 on: February 24, 2013, 02:27:50 AM »
that' what I do and it works well.

bramkip

  • Jr. Member
  • **
  • Posts: 61
    • View Profile
    • Intellisoft
    • Email
Re: MSSQL - multiple databases ?
« Reply #3 on: February 24, 2013, 07:17:19 AM »
Thanks Rene (and Kevin) for your reply.

Filling in the GLO:dbOwner String I understand. But default when I run the program, FM3 connects to a database based on the DB name in fm3.ini.
I don't know how I can connect to a DB after the user filled in a variable. Do I have to call the SQL_Connect procedure again?

Regards,
Bram


kevin plummer

  • Hero Member
  • *****
  • Posts: 1195
    • View Profile
    • Production Accounting and Software Payroll
Re: MSSQL - multiple databases ?
« Reply #4 on: February 24, 2013, 02:06:45 PM »
Make GLO:dbOwner threaded and then just populate this variable in the process link embed. Nothing else needed. I store each users dbOwner details in the session Q which I set at the login screen.

bramkip

  • Jr. Member
  • **
  • Posts: 61
    • View Profile
    • Intellisoft
    • Email
Re: MSSQL - multiple databases ?
« Reply #5 on: February 27, 2013, 11:31:55 PM »
Thanks Kevin, I will try this.

Best regards,
Bram

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #6 on: April 21, 2015, 07:21:42 PM »
Hey Kevin (apologies Bram for hi-jacking the thread!),

So I'm a little stuck.

1. Global Owner string is THREAD flagged.
2. Files have !ownerstr set in the dictionary.
2. FM3 is in the data dll.
3. SQL Connect is there also (MSSQL db).
4. There is a base db, owner string set at startup.
5. In Webhandler, ProcessLink I set the "Client" Owner string into the global Owner string variable.

I'm getting "File Access Error - File Not Found (2)" messages from then on (once because the app ends on the OK click, but I've experimented with a bit of code here and there).

So. Even though the global owner string is being set, it looks like my app won't recognise that. Am trying to work out why.

Also, originally, I didn't have FM3 in the app at all (use a converter backend app). But because I was getting the errors, tried putting it in .. Didn't make a difference at this point.

Any thoughts?

Cheers,

Stu
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #7 on: April 21, 2015, 07:28:17 PM »
Bah. Nevermind.

File didn't exist because the table didn't exist BECAUSE I turned files to "CREATE NONE" .. Because there's an issue with sql where a file can (in some wierd rare circumstances that just happened to happen to us) be overwritten.

That's why FM3 didn't just auto-work and create the files when accessed. And that's why it didn't work without FM3.

Stu
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #8 on: April 21, 2015, 07:48:05 PM »
Nope. Do mind :). Scratch the nevermind.

So there is still definitely something going on.

The database for the global owner string definitely exists, and is definitely up to date (runtimefilemanager run on it from backend exe).

But in Webhandler, ProcessLink, I'm still getting File Not Found errors.

Something further:

* The PROP:Owner value for one of the sql files is the ORIGINAL/BASE prop:owner string, not the new one which is set just above the Debug() line that reports on the prop:owner value.
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #9 on: April 21, 2015, 09:27:56 PM »
On further testing, the problem seems to be that the PROP:Owner of a sql file is not being updated after setting the global owner string (in Webhandler->ProcessLink embed).

When I set CREATE ALL for the files in the webserver app, the code to open the file (underneath the code to set global owner string) works fine. No "file not found" errors.

But it's the original db, not the one set in the global owner string.
« Last Edit: April 21, 2015, 09:29:33 PM by Stu »
Cheers,

Stu Andrews

kevin plummer

  • Hero Member
  • *****
  • Posts: 1195
    • View Profile
    • Production Accounting and Software Payroll
Re: MSSQL - multiple databases ?
« Reply #10 on: April 21, 2015, 10:07:01 PM »
Hi Stu,

are you doing any file access on your master DB before changing the dbowner, running create all and opening the file?


Kev

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #11 on: April 21, 2015, 10:20:45 PM »
Hi Kevin,

With the errors no, I don't think so.

I was, but commented it out when I got the errors, just to make sure.

Should I be?

** Am accessing a couple of tps files in the login page (as per your posts on this). But not the SQL files.
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #12 on: April 21, 2015, 11:17:08 PM »
Got it!

Fiddled around with CREATE settings in dictionary files and made sure the apps (data + webserver) were set to "whatever is in the file".

Now working.

Thanks Kevin!
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #13 on: April 26, 2015, 10:25:54 PM »
Hi Kevin,

I've got another question for you (figured replying in the forum is the best place, keeps it relatively open for science <g>).

In implementing this multi-db architecture, did you run into the following issue?

1. I use a lot of "memory form wraps browse" for advance filtering and functions.
2. At the moment, when I change the global db owner string at login time, the following happens:
  - the form->browse displays no records (even if there are some).
  - after adding a record the browse displays ALL records.
  - if you load JUST the browse (url), then all records show.

It's a strange one. I've asked Bruce about it.

Does it twig anything for you?
Cheers,

Stu Andrews

Stu

  • Hero Member
  • *****
  • Posts: 510
    • View Profile
    • Email
Re: MSSQL - multiple databases ?
« Reply #14 on: April 26, 2015, 11:06:19 PM »
Bah. Nevermind.

I must have fixed something or changed something (whatever the bug was), and now after upgrading to nettalk 8.45 this no longer happens.

Thanks anyway!

Stu
Cheers,

Stu Andrews