SQL Connection to 4D Server v12 from MS Excel

[Jörg Knebel]

Hi Listers,

To be honest, I totally lost the plot. :-(

What do I have to do where to get MS Excel running on WindowsXP to be able to connect to a 4D Server v12 on Windows Server 2003 R2 and gather data via SQL-Requests/Queries?
In the test environment the 4D Server will run on MacOSX .6.8.

I realised that an ODBC Data source has to be defined on the XP-Workstation but I don't know what driver to use or where to get it from if its not included in Windows.

After establishing a connection the next question would be what SQL-command restrictions I will face.

Did someone already solved this / a similar problem?

As a 'bonus' question, how can I access a 4D Server via SQL from the Mac with a third party product?

Any help will be highly appreciated.

Thanks

Cheers
Jörg


Regards
Jörg Knebel - 4D Developer since 1991
TTT Data Systems Pty Ltd
Phone: [phone]
www.tttdatasystems.com.au



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Keisuke Miyako]

Hello,

I am sure more developer would chime in, so I will be brief;

1. The ODBC Driver is provided by 4D. You use the standard installer.
Now here is the wonderful catch.
The installer doesn't install the ODBC Driver. What it installs is the ODBC Driver Installer.
So you need to run the installer installed by the installer.

2. You define the ODBC Data Source using the standard windows control panel application.
Now here is the second catch.
If you are running a 64 bit flavour of Windows,
you need to explicitly launch the 32 bit version of Odbcad32.exe,
which can be located in the WOW64 directory.

3. You might have to configure the character set.
4D ODBC uses UTF-8 by default, but in some cases you might prefer "legacy" encodings.
See the documentation for SQL SET OPTION.
Legacy encoding in general implies the constant -2.

4. You can be more expressive with the new 12.3 Hotfix 4 ODBC Driver, which allows parameterized SQL.

5. You can query 4D SQL from the Mac OS version of FileMaker, via ODBC.

miyako

Post by Jörg Knebel
After establishing a connection the next question would be what SQL-command restrictions I will face.

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Jörg Knebel]

Miyako,

Post by Keisuke Miyako
I am sure more developer would chime in, so I will be brief;

Thanks for the tips I'll give it a swirl.

Cheers
Jörg

Regards
Jörg Knebel - 4D Developer since 1991
TTT Data Systems Pty Ltd
Phone: [phone]
www.tttdatasystems.com.au



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Jörg Knebel]

Miyako,

Again, thanks for responding.

Please don't take my answer personally.

Post by Keisuke Miyako
I am sure more developer would chime in,

I wish someone else, especially from the Windows-Guru-Fraction, would have done so...

All I've done/checked/tried so far I did in Windows environments (XPPSP3 in a VM on Mac 10.6.8; W7U 64) only!

I always used 4D 32 bit stuff and will do so until EVERYTHING 4D-related runs reliable 64-bit-ish on all supported platforms.


First of all, I experienced a 4D error on all the Windows-flavours I have my hands on and tried:

"This application has failed to start because icuuc42.dll was not found. Re-installing the application may fix this problem"

Well, this message came up again and again even after uninstalling and then Re-instaling everything '4D' including several Windows-restarts in between.

One more thing before reading on, the 4D structure runs interpreted SQL Server starts on launch and 'On SQL Authentication' has the 'Trace' command right at the top but never fired.

Post by Keisuke Miyako
1. The ODBC Driver is provided by 4D. You use the standard installer.

Done this and installed it finally as instructed.

Post by Keisuke Miyako
2. You define the ODBC Data Source using the standard windows control panel application.

Got a T-shirt for that one.

I set it to the running 4D-Serverv12.3-source when the server was running on the same machine or on a different one and got lots of 'nice' messages when I tried that little Button 'Connection test...' like:

----- Failed ---

[01000] General warning
[08004+1109] On SQL Authentication failed /*- (see above)

OR

[01000] General warning
[08001] Is the SQL Server Started? /* what the f*fi%
Client unable to establish connection

OR

[01000] General warning
[HYT01] Connection timeout expired
[08001] Is the SQL Server Started?
Client unable to establish connection

FYI:
The TCP/IP settings for the machines involved have been checked over and over again.

FYI the 2nd:
I'm able to connect / USE a MS-SQL-Server on WinServer 2003 running in a VMware on that very W7U64 mentioned above from my Mac using 'Navicat'.

Post by Keisuke Miyako
3. You might have to configure the character set.

The database is set to UTF and I tried 'Connection test...' both ways 'UTF' and 'System'.

Error messages: see under 2.) !

Post by Keisuke Miyako
4. You can be more expressive with the new 12.3 Hotfix 4 ODBC Driver, which allows parameterized SQL.

That will be considered as soon as I get a connection first / at all.

Post by Keisuke Miyako
5. You can query 4D SQL from the Mac OS version of FileMaker, via ODBC.

See the answer to 4.) !


It seems to me that the 4D-propaganda 'We Have SQL' is everything but.


So, on Monday I'll inform my customer that there is no way to get data out of 4D via external SQL-requests and I'll tell them to stop any purchases regarding an update of their 4D licenses.

I hope I can convince them to follow my alternative suggestion otherwise I'll lose a big business.

Thanks '4D SQL'.



Regards
Jörg Knebel - 4D Developer since 1991
TTT Data Systems Pty Ltd
Phone: [phone]
www.tttdatasystems.com.au



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Spencer Hinsdale]

Post by Jörg Knebel
[01000] General warning
[08004+1109] On SQL Authentication failed /*- (see above)

See if you have a blank On SQL Authentication method. To connect with a 4D Name and Password, you need to Delete the On SQL Authentication method (or have the method call Change User and return True).

-spencer
**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Jörg Knebel]

Post by Spencer Hinsdale
See if you have a blank On SQL Authentication method. To connect with a 4D Name and Password, you need to Delete the On SQL Authentication method (or have the method call Change User and return True).

I knew I forgot something in my explanations - sorry my fault - mea culpa . :-((

Here are the 'On SQL Authentication' used AT ALL TIMES:



// On SQL Authentication( ; ; ;) --> xResult
// Created: 31/03/09, 10:56:52
// © 2000 - 2008 TTT Data Systems Pty Ltd, Jörg Knebel
// Modified :31/03/09, 10:56:52


C_TEXT(${1})
C_BOOLEAN($0)
TRACE // HERE IS THE TRACE
$0:=False
QUERY([xPasswords];[xPasswords]Password= My_CryptRoutine ($2);*)
QUERY([xPasswords]; & ;[xPasswords]EmailLogInIdentifier=$1;*)
QUERY([xPasswords]; & ;[xPasswords]xStatus=1)
If (Records in selection([xPasswords])=1)
CHANGE CURRENT USER("CommonUser";"")
$0:=OK=1
If ($0)
SO_WriteProtocol (1;"SQL-LogIn: "+$1;[xPasswords]ID)
End if
End if



Please tell me what I'm doing wrong!!!!


Regards
Jörg Knebel - 4D Developer since 1991
TTT Data Systems Pty Ltd
Phone: [phone]
www.tttdatasystems.com.au



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************

[Jörg Knebel]

Hi,

I have to apologise for blaming 4D for the failed ODBC-trials.

As it turned out it was WinXPP in the sandbox which was not able to establish any ODBC-connection. :-(

However ODBC-connections between Win7(real box) and Mac OSX.6.8 were possible in both direction.

The gathering of data from a 4D server v12 with a third party product will be the next set of tests.

Thanks

Regards
Jörg Knebel - 4D Developer since 1991
TTT Data Systems Pty Ltd
Phone: [phone]
www.tttdatasystems.com.au



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-Unsubscribe-d2/MUvgItPNWk0Htik3J/***@public.gmane.org
**********************************************************************