SQL Server using OLEDB

[yu217171]

Hi everyone,

I am new to PB but I am from VB/VB.net. I am attempting to connect to a SQL Server 2000 database and pull out some information (and eventually execute INSERTS, UPDATES, Functions and Stored Procs). I can connect to the database using the data profile using a ODBC interface but would like to connect using the OLEDB interface programatically.

Similiarly, are there PowerScript objects that are equivalent to the Connection, Recordset, Command objects in ADO? Much help is appreciated. I have looked everywhere for this information, but it seems that the net is lacking in PowerScripting tutorials.

Thnx in advance!

Keith Tang
BA, MCP, Net+

 

[mbalent]

These are the values we use to connect to sql2000 via OLE DB

SQLCA.DBMS = "OLE DB"
SQLCA.LogID = &quot;id&quot; < replace with appropriate value
SQLCA.LogPass = &quot;pword&quot; < replace with appropriate value
SQLCA.Lock = &quot;RC&quot;
SQLCA.AutoCommit = &quot;false&quot;
SQLCA.DBParm = &quot;PROVIDER='SQLOLEDB',DATASOURCE='sql01',PROVIDERSTRING='database=security_db;APP=''&quot;

Change the datasource and database values in the DBParm string appropriately.

To connect issue:

Connect using sqlca;

Then we send a series of statements to set various
database parameters (assuming the connect goes ok).

execute immediate &quot;set quoted_identifier off&quot;;
execute immediate &quot;set ansi_nulls off&quot;;
execute immediate &quot;set nocount on&quot;;
execute immediate &quot;set ansi_warnings off&quot;;
execute immediate &quot;set implicit_transactions off&quot;;

 

[yu217171]

mbalent,

Great! I can now connect to my SQL Server. It works like a charm. Now, how can I execute a query? Is there a command object? And how do I store my returning query? Is there a recordset object? And further, how do I iterate through the results of my query, once I've executed it?

ie: In ADO, I would do this. (using VB)

'Once connected to SQL Server

dim myrs as new recordset

set myrs = myconn.execute (&quot;Select * from tblEmployees&quot

if not myrecordset.EOF then
do while not myrecordset.eof
msgbox (myrecordset.fields (0).value)
loop
else
msgbox (&quot;No records returned&quot
end if
---

I dunno if I confused you with the VB code or not =)

I heard that the PB equivalent of a recordset object is a cursor. But aren't cursors native to the db? Or are these client-side cursor objects?

 

[yu217171]

I was experimenting with the connection string and I managed to mash everything into the SQLCA.DBParm property.

SQLCA.DBParm = &quot;Provider=SQLOLEDB.1;Password=mypassword;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=HP&quot;

connect using sqlca;

^^These two lines work like a charm. Just thought I'd let you guys know =)

Keith

 

[mbalent]

You really should read some of the PB help files specifically regarding the datawindow. This is the primary object for database interaction. Although you can issue sql commands directly and return result sets via cursors, the datawindow is better suited for this and easier to use.

 

[6112]

PB has a different but powerful and better way to handle all SQL statment... Study it pls. Not hard if you already know VB script. (Will be simpler, because of powerful)