Solved

Connect to SQL Server Personal edition through ODBC from PB7 script.

Posted on 2004-04-01
10
17,045 Views
Last Modified: 2013-12-26
When Sql Server 2000 Enterprise Edition (NOt personal)

disconnect;
ls_database='siddiqsons'
ls_srv_name= 'sdmsrv'
ls_latest_dsn = 'siddiqsons'

sqlca.dbms  = "ODBC"
sqlca.dbparm = "Connectstring='DSN="
sqlca.dbparm = sqlca.dbparm + ls_latest_dsn+"',disablebind=1"
connect;

IF SQLCA.SqlCode <> 0 THEN
   MessageBox ("Cannot Connect to Database", sqlca.sqlerrtext)
  RETURN
END IF

So Far So Good.

Now I want to do the same thing in
SQL Sever 2000 Personal Edition to be run in window 98 where window authentication
is not supported.

I create datasource name [siddiqsons] through odBC manually with sql authentication
with  user name = sa and  password = 'ali' and the connection is successful.

Bu when I run the program it says
Not associated with trusted sql connection etc.
Login failed for user null.
A window pops up asking user id and password when provided then it connects.
I want to eliminate this pop up error message.
How to do so



0
Comment
Question by:Mateen
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 14

Accepted Solution

by:
sandeep_patel earned 250 total points
Comment Utility
hi,
can u change your code like below and try

ls_database='siddiqsons'
ls_srv_name= 'sdmsrv'
ls_latest_dsn = 'siddiqsons'

sqlca.dbms  = "ODBC"
sqlca.dbparm = "Connectstring='DSN="
sqlca.dbparm = sqlca.dbparm + ls_latest_dsn+";UID=sa;PWD='ali';disablebind=1"
connect;

IF SQLCA.SqlCode <> 0 THEN
   MessageBox ("Cannot Connect to Database", sqlca.sqlerrtext)
  RETURN
END IF

or if u don't want to use odbc then

// Profile test_profit1
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"
SQLCA.Database = "siddiqsons"
SQLCA.ServerName = "sdmsrv"
SQLCA.LogId = "sa"
SQLCA.LogPass="ali"
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

IF SQLCA.SqlCode <> 0 THEN
   MessageBox ("Cannot Connect to Database", sqlca.sqlerrtext)
  RETURN
END IF

-sandeep

0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
sorry,
there is one mistake in first option..it's of single quot
use this

ls_database='siddiqsons'
ls_srv_name= 'sdmsrv'
ls_latest_dsn = 'siddiqsons'

sqlca.dbms  = "ODBC"
sqlca.dbparm = "Connectstring='DSN="
sqlca.dbparm = sqlca.dbparm + ls_latest_dsn+";UID=sa;PWD=ali',disablebind=1"
connect;

IF SQLCA.SqlCode <> 0 THEN
   MessageBox ("Cannot Connect to Database", sqlca.sqlerrtext)
  RETURN
END IF

0
 
LVL 18

Expert Comment

by:diasroshan
Comment Utility
Mateen,
make a database profile in Powerbuilder... from the preview tab copy the database connection syntax....

paste this in the appication event  or wherever u want it....

try it this way....

i use ODBC... never faced such a problem...
nothing to do with Personal Edition.....

Cheers,
Rosh
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 250 total points
Comment Utility
Visit http://www.connectionstrings.com and expand sql server section to see examples of connectio strings.

Build the connection string so that it includes user id and password. You can obtain from your logon window.


This code works.

SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = True
SQLCA.DBParm = "ConnectString='DSN=mss;UID=nn;PWD=nn',DelimitIdentifier='No',InsertBlock=22,DisableBind=1,StaticBind=0"
sqlca.database="pubs"

CONNECT using sqlca;

if sqlca.sqlcode <> 0 then
      Messagebox("debug", "Test")
end if      

regards-
0
 

Author Comment

by:Mateen
Comment Utility
Hi Sandeep:

Thanks for your correct answer.

If I use this

// Profile test_profit1
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"
SQLCA.Database = "siddiqsons"
SQLCA.ServerName = "sdmsrv"
SQLCA.LogId = "sa"
SQLCA.LogPass="ali"
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

then I assume that I will not need manual creattion of DSN ODBC.

Now How to check from within pb7 which sql server version is running.
When Professional edition then I would connect through window authentication
and
when personal edition I would connect through
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"

Mateen:



0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
Mateen,

MSS is the native interface to connect to SQL Server. Did you previously use ODBC and trying to convert to use Native drivers.?

Will you need to use the same datawindows using ODBC or Native Drivers? SQL Syntax may be lillte different in each cases. I would personally use either ODBC or Native Interface, but not mix them up.

If you need to use ODBC and connect to SQL server using windows authentication. You will not need to supply user_id, passwords. But need to check on 'Integrated Security' check box. That would translate to ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON' within DBPARM.

// Profile team
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = True
SQLCA.DBParm ="ConnectString='DSN=mss',DelimitIdentifier='No',InsertBlock=22,DisableBind=1,StaticBind=0,ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON'"

I am not sure if this is what you are trying to do. But this s one way you can obtain version info.

select @@Version should provide more specific info on which version you are running.
You can create a dw out of 'select @@Version' and read the first row first column. Call the dw be d_version

datastore lds_ds
lds_ds = create datastore
long ll_rowcount
string ls_version

lds_ds.dataobject = 'd_version'
lds_ds.SetTransObject(SQLCA)
ll_rowcount = lds_ds.retrieve()

if ll_rowcount > 0 then
  lds_ds.GetItemString(1,1)
else
 MessageBox("Error", "Unable to determine MS Version")
end if

regards-
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
BTW, you will not need to create ODBC DSN if you connect thru Nativer Driver Interface MSS. But you will need to distribute PBMSSXX.dll with your application. Where XX is the version number.

Regards-
0
 

Author Comment

by:Mateen
Comment Utility
Sandeep Patel comments is accepted as answer as he provided the solution first as per my initial question.

Namase: U gave me remarkably beautiful link http://www.connectionstrings.com. I really liked that.

Mostly our systems are run in multi environment (Locally Connected).
When our system goes to outside the city (two to three case so far) to be run in one computer which has window 98 and sql personal edition installed and where "WINDOW AUTHENTICATION " is not possible..

I asked my question in this scenario.

BTW.<You can create a dw out of 'select @@Version' and read the first row first column. >
I have never been able to create such dw.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
Mateen:

>BTW.<You can create a dw out of 'select @@Version' and read the first row first column. >
>I have never been able to create such dw.

You can do this. I created a datawindow out of 'select @@version' and called it d_version. Once the datawindow is created i renamed 'compute_001' to 'version'. I will not be able to do this using graphical interface. When you get a dislog to select tables during dw creating, cancel 'select tables' dialog box and the select 'design/convert to syntax' menu. Type 'select @@version'. Click on the 'Return' toolbaritem and the dw will be created.

$PBExportHeader$d_version.srd
release 8;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=84 color="536870912" )
table(column=(type=char(300) updatewhereclause=yes name=version dbname="compute_0001" )
 retrieve="select @@Version" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="5" y="4" height="76" width="3511" format="[general]" html.valueishtml="0"  name=version visible="1" edit.limit=300 edit.case=any edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" netscapelayers="0" )

regards-
 
0
 

Author Comment

by:Mateen
Comment Utility
Thanx Namasi:
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now