Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Ms SQL Server and perl.

by hacker_j99 (Beadle)
on Jul 28, 2001 at 00:40 UTC ( [id://100452]=perlquestion: print w/replies, xml ) Need Help??

hacker_j99 has asked for the wisdom of the Perl Monks concerning the following question:

Who would have thought that such a narrow subject would intail such a lot of work.. Recently I decided to jump on this issue with prodding from my boss.... little did I know that the project had taken me 4 months with little headway... I am on a Sun Solaris sparc machine and have found that the most of my troubles... Linex versions can connect with OpenSource Sybase for free but Sybase isnt free for other boxes. ( money glutton hoards) This helps to complicate things greatly in companies who dont want to spend money ( profit hungry money gluttons aka my boss )
So far I have installed sucessfully FreeTDS (both .51 and .52) and have got it to connect to a MS SQL server. This was after a lot of work and trying new things untill I finally annoyed the freetds personel into helping a bit.
This connection is only in Unittests so dont get your hopes up.. corrently I also have perl 5.6.1 installed and have attempted to install DBD::Sybase but have had it fail numerous times( fail in test and a g_tds_err_handeler problem)..
Needless to say I am Not alone cause I have talked to 30+ people in the last week with the same problems. I am currently working on a way around this by using the Unittest and seeing if it will make a file for me to pipe into perl (not the best but hey when you are grasping at straws it works)

If ANYONE has gotten a good connection with all column types in C or Perl please let me know... Also if someone could help me out in the following conversions that would be extremely helpfull.

Varbinary
binary
int4
int8
int2
int1
datetime
time
date
or practically any other types
below is some of my c code following with the DBD::Sybase errors
/* Script writen my Harry Franks taken as example from FreeTDS using FreeTDS as the backbone. */ #include <stdio.h> #include <tds.h> static char software_version[] = "$Id: t0005.c,v 1.1 2000/11/04 01: +35:51 mark Exp $"; static void *no_unused_var_warn[] = {software_version, no_unused_var_w +arn}; // Sets up the Socket and char/int to use with the SQl int run_query(TDSSOCKET *tds, char *query); char *value_as_string(TDSSOCKET *tds, int col_idx); int main() { // the login and socket is setup by some files setup by FreeTDS TDSLOGIN *login; TDSSOCKET *tds; int verbose = 0; int rc; int i; // Some arbitrary stup set up to test the quality of the server // (ability to handel large numbers) char *len200 = "012345678901234567890123456789012345678901234567890 +123456789012345678901234567890123456789012345678901234567890123456789 +012345678901234567890123456789012345678901234567890123456789012345678 +90123456789"; char large_sql[1000]; // print out a header to show its starting fprintf(stdout, "%s: Test show tables replies\n", __FILE__); // login rc = try_tds_login(&login, &tds, __FILE__, verbose); if (rc != TDS_SUCCEED) { fprintf(stderr, "try_tds_login() failed\n"); return 1; } // drop the old test_table so we dont get old values rc = run_query(tds, "DROP TABLE test_table"); if (rc != TDS_SUCCEED) { return 1; } // create the clean test table rc = run_query(tds, "CREATE TABLE test_table (id int, name varchar( +255))"); if (rc != TDS_SUCCEED) { return 1; } // insert large values and stuff into the table so we can check it sprintf(large_sql, "INSERT test_table (id, name) VALUES (0, 'A%s')" +, len200); rc = run_query(tds, large_sql); if (rc != TDS_SUCCEED) { return 1; } sprintf(large_sql, "INSERT test_table (id, name) VALUES (1, 'B%s')" +, len200); rc = run_query(tds, large_sql); if (rc != TDS_SUCCEED) { return 1; } sprintf(large_sql, "INSERT test_table (id, name) VALUES (2, 'C%s')" +, len200); rc = run_query(tds, large_sql); if (rc != TDS_SUCCEED) { return 1; } sprintf(large_sql, "INSERT test_table (id, name) VALUES (3, 'This i +s a test.. Greg I broke it')", len200); rc = run_query(tds, large_sql); if (rc != TDS_SUCCEED) { return 1; } /* * The heart of the test the SELECT statement */ rc = tds_submit_query(tds, "SELECT * FROM sysusers"); // while everything went fine print them out while ((rc=tds_process_result_tokens(tds))==TDS_SUCCEED) { while ((rc=tds_process_row_tokens(tds))==TDS_SUCCEED) { for (i=0; i<tds->res_info->num_cols; i++) { if (verbose == 0) { // print out the data printf("col %i is %s\n", i, value_as_string(tds, i)); } } } // the test failed.... Why? if (rc == TDS_FAIL) { fprintf(stderr, "tds_process_row_tokens() returned TDS_FAIL\n +"); return 1; } else if (rc != TDS_NO_MORE_ROWS) { fprintf(stderr, "tds_process_row_tokens() unexpected return\n +"); return 1; } } if (rc == TDS_FAIL) { fprintf(stderr, "tds_process_result_tokens() returned TDS_FAIL f +or SELECT\n"); return 1; } else if (rc != TDS_NO_MORE_RESULTS) { fprintf(stderr, "tds_process_result_tokens() unexpected return\n +"); } // logout of the database try_tds_logout(login, tds, verbose); return 0; } /* Run query for which there should be no return results */ int run_query(TDSSOCKET *tds, char *query) { int rc; rc = tds_submit_query(tds, query); if (rc != TDS_SUCCEED) { fprintf(stderr, "tds_submit_query() failed for query '%s'\n", qu +ery); return TDS_FAIL; } while ((rc=tds_process_result_tokens(tds))==TDS_SUCCEED) { if (tds->res_info->rows_exist) { fprintf(stderr, "Error: query should not return results\n"); return TDS_FAIL; } } if (rc == TDS_FAIL) { /* probably okay - DROP TABLE might cause this */ /* fprintf(stderr, "tds_process_result_tokens() returned TDS_FAI +L for '%s'\n", query); */ } else if (rc != TDS_NO_MORE_RESULTS) { fprintf(stderr, "tds_process_result_tokens() unexpected return\n +"); return TDS_FAIL; } return TDS_SUCCEED; } // the types that this expects or wants in a sql statement // varchar and int are only excepted at this time char *value_as_string( TDSSOCKET *tds, int col_idx) { static char result[256]; const int type = tds->res_info->columns[col_idx]->column_type +; const char *row = tds->res_info->current_row; const int offset = tds->res_info->columns[col_idx]->column_offs +et; const void *value = (row+offset); switch(type) { case SYBVARCHAR: strncpy(result, (char *)value, sizeof(result)-1); result[sizeof(result)-1] = '\0'; break; case SYBINT4: sprintf(result, "%d", *(int *)value); break; default: sprintf(result, "Unexpected column_type %d", type); break; } return result; }
the perl error
install_driver(Sybase) failed: Can't load '/var/tmp/hfranks/lib/perl5/site_perl/ 5.6.1/sun4-solaris/auto/DBD/Sybase/Sybase.so' for module DBD::Sybase: ld.so.1: / var/tmp/hfranks/bin/perl: fatal: relocation error: file /home/top/hfranks/hfrank s2/hfranks2/lib/libct.so.0: symbol g_tds_err_handler: referenced symbo +l not foun d at /var/tmp/hfranks/lib/perl5/5.6.1/sun4-solaris/DynaLoader.pm line +206. at (eval 4) line 3 Compilation failed in require at (eval 4) line 3. Perhaps a required shared library or dll isn't installed where expecte +d at newest.pl line 8

Replies are listed 'Best First'.
Re: Ms SQL Server and perl.
by Moonie (Friar) on Jul 28, 2001 at 11:03 UTC
    Check out MSSQL::Sqllib - SQL Library for Microsoft SQL Server from Perl
      Unfortunately, MSSQL::Sqllib has several limitations when it comes to MSSQL Server 7.0 and greater. Be sure to check the limitations listed at the bottom of the module description.

      Also, this is very odd, but the module Moonie suggests is not found in CPAN, but it is at the site he gives (a Canadian mirror.)

      Scott

        scain is correct, there are several limitations with MSSQL 7 and beyond with the new datatypes. Be sure to read ALL the documenation before you decide to use it. It may not be the best choice for your situation.

        If you do wish to download the module to just check it out, you'll find that you can download it from here on Cpan. Let me know if you have problems.
Re: Ms SQL Server and perl.
by aquacade (Scribe) on Jul 28, 2001 at 07:12 UTC
    Following is from DBI FAQ:
    3.4 Can I access Microsoft Access or SQL-Server databases with DBI? Yes, use the DBD::ODBC driver.

    I'm still a newbie here on Perl Monks, but I think you'd be well advised to save yourself the trouble and not post long C programs before asking short questions to which other Monks can provide short answers!

    If you're using ActiveState Perl on Windows platforms, might I also suggest using the Start Menu, Find Files, Containing Text feature on the c:\perl\html directory. This will search all online documentation and you just have to double-click each found file and read it. I've found this very useful myself. BTW, Unix users could use grep.

    Update: Sorry to suggest you should try to use a Windows platform! I wish I had a Sun box at work and wasn't forced to use Windows. I believe the DBI::ODBC advice was still valid. I did read your message carefully, but I guess you have no choice but to stay on a Sun box. Please forgive me if I offended you somehow. Was was trying to suggest an alternative approach and the ActiveState's HTML based documentation is very well organized overall IMO.

      this is not a ez question and yes I know what I am talking about... If you would have read I am NOT on a windows machine but rather a Sun Solaris 7 (sparc) machine and the C code is nessesary because it provides some ( not all due to data types) connection. this is a feat in itself if you would bother trying it. without the libraries and the c files (some created or hacked by me and some from freetds) the datastream out of the database would not be utilized..

      thanks for the input though.
Re: Ms SQL Server and perl.
by scain (Curate) on Jul 28, 2001 at 19:51 UTC
    I had heard the FreeTDS had problems with varchars over 256 bytes. I also heard the enoch fixed those problems, but I've not discussed it with him personally.

    I hope the other suggestions posted here work out. Let us know.

    Scott

      I have heard the same although I havent tested all the features... is this version of FreeTDS the .51 or the .52 that you speak of. I heard .52 is suppose to take care of that and some problems with BigEndian but I dont think its stable enough to use full time

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://100452]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2024-03-28 15:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found