March 28, 2024, 01:19:48 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


MS SQL Server 2005 Question

Started by billhsln, January 27, 2012, 05:56:04 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

billhsln

January 27, 2012, 05:56:04 PM Last Edit: February 03, 2012, 04:40:09 AM by billhsln
Is it possible under IWB to create a new table in MS SQL Server 2005?  Or does it have to be created by MS SQL Server?

It might also be good to know how to open that database also.

I really need to know how to do this, need for Work.

Thanks,
Bill
When all else fails, get a bigger hammer.

billhsln

I would be happy with just knowing how to OPEN  the Database.  I really need this for my Work.  If I can't use IWB I will have to learn VB, since it will do it.

Thanks,
Bill
When all else fails, get a bigger hammer.

Brian

Bill,

I'm sure I have seen the SQL Server driver question asked before - I'll look it up
when I get home

Brian

Bill-Bo


billhsln

That works for MySQL, not MS SQL.  Different Database.

Thanks anyway,
Bill
When all else fails, get a bigger hammer.

Rock Ridge Farm (Larry)

If I remember, you need to use ODBC to access a My Sql db.
At one time I had code to do that  - will look around and see what I can find.

Larry

billhsln

I have tried using an ODBC code to load it.  When it compiles it complains that there is no endif in SQL.INC on line 12.  I looked at SQL.INC and found 5 $IFNDEFs with 5 $ENDIFs, so not sure what the problem is.

I have also modified the program that shows all of the 'database_test.iwb' to try to also open each driver with the file pointed explicitly to where I have the MS SQL file saved.  It goes thru each drive and tries to open the file, no luck.  I have the file created and the tables defined, just have no data in the tables yet.  That is what I would like to get to work.  So, I can transfer the data from the current Access DB to MS SQL DB.  Then later, when I get that to work I will need to create a utility to remove duplicates (have sql to find them) and if info is in old record but not in new, then I want to add to new record.

Thanks,
Bill
When all else fails, get a bigger hammer.

Bruce Peaslee

Hi,

Did you ever solve the problem of how to open the database? I have been playing around for a couple of days now without success.

Thanks,
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

billhsln

Turned out that my problem was with the way I defined the DB.  The code worked fine.  Has to be correctly defined under ODBC.  One of my co-workers figured it out and sent me screen shots of what to do.

OPENCONSOLE

DEF pdb:POINTER
DEF Server, FileName, Options:STRING

Server = "SQL Server Express"
FileName = ""
Options = ""
pdb = dbConnectDSN(Server,FileName,Options)
if pdb <> NULL
PRINT Server," worked"
dbDisconnect(pdb)
else
PRINT "pdb = ", pdb
ENDIF

PRINT "Press Any Key To Close"
DO
UNTIL INKEY$ <> ""
CLOSECONSOLE

END


If you need the setup, I can forward the E-Mail to you.

Bill
When all else fails, get a bigger hammer.

Bruce Peaslee

July 16, 2012, 03:26:09 PM #9 Last Edit: July 16, 2012, 03:28:09 PM by peaslee
I'm not sure dbConnectDSN is right for me. I want to provide the IWBasic program to a number of people who can then get the data off of the internet.

I'll PM my email and you can send what you have. Maybe it will provide what I need. Thanks for the response.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

Bruce Peaslee

So close!

If I go through the control panel and set up a DSN named "Test", this works:

Server = "Test"
Options = "UID=DB_44925_test2_user;PWD=******"  'not really my password ;)
pdb = dbConnectDSN(Server,Options)


This won't work for my users unless they do the same, something that is not practical. The same control panel app allows me to create a "File DSN" which one is supposed to be able to ship with the program to make the connection. I made the file and named it "connection.dsn".

This doesn't work:


Server = getstartpath + "connection.dsn"
Options = "UID=DB_44925_test2_user;PWD=*******"
pdb = dbConnectDSN(Server,Options)


I'll search around to see if I can devine how to make this work, but if anyout there has a solution...

Thanks to billhsln for getting me this far.

Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

billhsln

Not sure this is 100% of what you are looking for, but this might be able to be translated to IWB.

http://www.petri.co.il/forums/showthread.php?t=12533

It is writing to the Registry to setup the ODBC.

Bill
When all else fails, get a bigger hammer.

LarryMc

Internally the dbConnect and dbConnectDSN commands are using the SQLDriverConnect API.

POINTER = dbConnect(driver as STRING,filename as STRING,options as STRING,OPT parwnd as POINTER)

strConnect = "DRIVER={"+Driver+"}"
IF LEN(filename) THEN strConnect += ";DBQ="+filename
IF LEN(options)THEN strConnect += ";" + options

pointer = SQLDriverConnect(hdbc, parwnd.hwnd, strConnect,LEN(strConnect), notused, 1023, notused, SQL_DRIVER_NOPROMPT)

This is supposedly a valid strConnect to connect to a SQLServer:
"DRIVER={SQL Server};SERVER=testsvr;DATABASE=master;UID=testuser;PWD=friday;"
so the IWB command would look something like this:
dbConnect("SQL Server","","SERVER=testsvr;DATABASE=master;UID=testuser;PWD=friday;")
notice the filename is blank so we don't have that DBQ string internally
also testsvr can be an ip address among other things

The above may help or it may be totally useless info.

Thanks to Alyce this is a link to some connection strings
http://www.connectionstrings.com/sql-server-2005
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Bruce Peaslee

SUCCESS!!!

The connection was made and data read.

Thanks so much Larry and Bill.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

LarryMc

Great!

Can you show us what the dbConnect line that worked looked like?
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Bruce Peaslee

Sure.

pdb=dbConnect("SQL Server", "", "SERVER=s06.winhost.com;DATABASE=DB_44925_test2;UID=DB_44925_test2_user;PWD=*******")
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

billhsln

Did you have to define the connection to windows?

Bill
When all else fails, get a bigger hammer.

Bruce Peaslee

July 17, 2012, 09:58:22 AM #17 Last Edit: July 17, 2012, 10:13:24 AM by peaslee
I don't understand the question.

Here is the whole program:


'
AutoDefine "off"

pointer pDB = null
string  error = ""
string  lastName
string  firstName
uint    hStmt
'
' attempt connection
pDB=dbConnect("SQL Server", "", "SERVER=s06.winhost.com;DATABASE=DB_44925_test2;UID=DB_44925_test2_user;PWD=*******")
If pDB <> null 'success
Print "Connection Success"
Print
If (PrintValues() = true)
AddRecord()
PrintValues()
EndIf
Else
Print "Connection Failure"
EndIf
' all done
Print "Press any key to continue."
WaitCon
dbDisconnect(pDB)
End
'
Sub PrintValues(), int
hStmt = dbExecSQL(pdb,"SELECT * FROM Users")
error = dbGetErrorCode(hStmt)
If error <> "" 'oops
Print "Error in Sub PrintValues()"
Print "Error Code: " + error
Print "Error Text: " + dbGetErrorText(hStmt)
Return false
Else
dbBindVariable(hstmt, 1, lastName)
dbBindVariable(hstmt, 2, firstName)
While dbGet(hStmt)
Print lastName, ", ", firstName
EndWhile
Print "---------------"
dbFreeSQL(hStmt)
Return true
EndIf
EndSub
'
Sub AddRecord()
hStmt = dbExecSQL(pdb,"INSERT INTO Users VALUES('Smith', 'John')")
error = dbGetErrorCode(hStmt)
If error <> "" 'oops
Print "Error in Sub PrintValues()"
Print "Error Code: " + error
Print "Error Text: " + dbGetErrorText(hStmt)
Else
dbFreeSQL(hStmt)
EndIf
EndSub



Note that I am told one cannot change the structure of the online database this way, but I haven't tested that.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

LarryMc

Bruce,
Worked great for me.  I added my name to DB.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Bruce Peaslee

Quote from: LarryMc on July 17, 2012, 10:04:46 AM
Bruce,
Worked great for me.  I added my name to DB.

I posted the password in plain sight! It's a good thing I'm not storing credit card numbers!
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

billhsln

You could always encrypt any fields that need to be private.

What I was wondering about is, do you have to go to ODBC to define the table or does the IWB handle everything?

Bill
When all else fails, get a bigger hammer.

Bruce Peaslee

Quote from: billhsln on July 17, 2012, 10:16:03 AM

What I was wondering about is, do you have to go to ODBC to define the table or does the IWB handle everything?

Bill

I have not had time to test it with IWB. Software like Microsoft Access, that links to the data, cannot change the table structure. In my case I defined the table with separately.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

Bruce Peaslee

I have successfully added and deleted a column of a table through IWB.

I think this is a pretty powerful capability - that is, using IWB to connect to an online database.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles