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

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



MateenAsked:
Who is Participating?
 
sandeep_patelCommented:
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
 
sandeep_patelCommented:
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
 
diasroshanCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
namasi_navaretnamCommented:
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
 
MateenAuthor Commented:
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
 
namasi_navaretnamCommented:
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
 
namasi_navaretnamCommented:
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
 
MateenAuthor Commented:
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
 
namasi_navaretnamCommented:
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
 
MateenAuthor Commented:
Thanx Namasi:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.