Question

LF Forms linked server to lookup MySQL data

Forms

Updated November 17, 2016

asked on November 2, 2016 • Show version history

Has anyone worked out trying to lookup data in Laserfiche Forms to MySQL data using the linked server set up in Microsoft SQL?

There are a few similar previous posts on this topic from a couple of years ago, but there is no definitive answer on how to do this.

I am new to SQL, so will try and explain what I have so far achieved. I have been googling for days trying to get this working, and feel that I am close.

1. Set up a user lfforms (PHPMyAdmin) in MySQL. Installed and configured the MySQL ODBC driver that tested successfully (see attached).

2. Modified the Server Objects / Linked Servers / Providers / MSDASQL properties (see attached)

3. In MS SQL Server Management Studio set up a linked server (following various different instructions on the net).

4. Set the new Linked Server properties (attached).

5. I can view the database and all the tables as well as query a simple table (attached).

6. In the LF Forms Administration Data Sources page, I am unable to set up a New Data Source to Microsoft SQL (using the MS SQL account) . The error I am receiving is "Cannot open database "data base name" requested by the login. The login failed".

The error log on the SQL Server reavealed:

"2016-11-03 10:37:28.84 Logon Error: 18456, Severity: 14, State: 38.
2016-11-03 10:37:28.84 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'catsfiscal'. [CLIENT: ip address of the SQL server is displayed here]"

I think there is something I am missing between Steps 5 & 6. Posts on the web all appear to talk about Stored Procedures, then it all goes over my head. If any one can please provide any advice or examples on how successfully have this working, it would be greatly appreciated.

cheers

MySQL ODBC.png (81.93 KB)

| Download

MSDASQL Properties.PNG (35.06 KB)

| Download

linked server properties.png (106.57 KB)

| Download

query.PNG (65.69 KB)

| Download

LF Forms Data Source.png (37.55 KB)

| Download

0 0

Answer

SELECTED ANSWER

replied on November 17, 2016

Here is an example:

Run the following in one of your mssql database (not in linked server):

CREATE PROCEDURE [dbo].[GetDataFromLinkedServer] @code varchar(2)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM OPENQUERY(catsfiscal, 'SELECT * FROM catsfiscal.Cats_Authorised_by') T1 WHERE T1.Authorised_By_Code = @code
END

Then add the database to Forms data source, and configure the lookup rule like this:

Then you could fill the Code field and expect values to be filled in Description field like this:

0 0

Replies

replied on November 3, 2016 • Show version history

Grant,

I've had some problems just getting a Forms Data Connection working to a Microsoft SQL server, so creating a Data Connection to MySQL might be anywhere from very difficult to impossible.

I think your best approach would be to instead set up a task that imports data from MySQL to a database on your MSSQL server, and then make the connection from Forms to the MSSQL database.

There's also a bug in Forms 10.1 where the account being used to access an MSSQL database also needs to have access to the Forms database, so that's going to be an insurmountable problem for you directly accessing data in MySQL until the third service pack update for Forms 10.1 comes out.

0 0

replied on November 6, 2016

Hi Glen,

Thanks for you reply. The issues with setting up a scheduled task to copy data from MySQL to SQL is the data would not be live, which is what we are really after. Also it is our IT departments preference to use linked servers VS having two copies of the same data across different db's.

Is anyone from Laserfiche able to confirm if MySQL is being considered to lookup data in future releases of LF forms? It would be a huge win for us as there would be hundreds of forms we could use to automate processes.

cheers

0 0

replied on November 15, 2016

Hi Grant,

It's not possible to use linked server as data source in Forms current release.

So for now, can you try use the linked server in a stored procedure and use that stored procedure for lookup?

0 0

replied on November 16, 2016

Hi Rui,

Unfortunately I am a novice with SQL, so I really wouldn't know how to set up stored procedures. Not sure if you can help or not with Glen's response (scheduled task) or Stored Procedure, otherwise I will look to research Glen's response and schedule a task to copy data nightly. If I could get either option working we will be far better off that what we have now.

cheers

0 0

SELECTED ANSWER

replied on November 17, 2016

Here is an example:

Run the following in one of your mssql database (not in linked server):

CREATE PROCEDURE [dbo].[GetDataFromLinkedServer] @code varchar(2)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM OPENQUERY(catsfiscal, 'SELECT * FROM catsfiscal.Cats_Authorised_by') T1 WHERE T1.Authorised_By_Code = @code
END

Then add the database to Forms data source, and configure the lookup rule like this:

Then you could fill the Code field and expect values to be filled in Description field like this:

0 0

replied on November 17, 2016

Thanks Rui for your response. I just tried this but the data did not populate into the form.

BUT....I finally managed to get it working a slightly different way that what you suggested by creating a db and a view. See my post here--> https://answers.laserfiche.com/questions/110851/Forms-can-lookup-data-ODBC-from-non-SQL-dbs--Mystery-Solved-Here-is-how-we-did-it

So I will consider thread as answered now that I have it working using my method. Appreciate your help.

cheers

0 0

You are not allowed to follow up in this post.

Question

Question

LF Forms linked server to lookup MySQL data

Answer

Replies

Sign in to reply to this post.