IonicWind Software

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

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

Bill,

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

Brian

Try http://www.ionicwind.com/forums/index.php?topic=2869.msg23892#msg23892

It is Creative Basic, but it may help.

Bill

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

Thanks anyway,
Bill

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

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

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,

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

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.

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.

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

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

SUCCESS!!!

The connection was made and data read.

Thanks so much Larry and Bill.

Great!

Can you show us what the dbConnect line that worked looked like?

Sure.

pdb=dbConnect("SQL Server", "", "SERVER=s06.winhost.com;DATABASE=DB_44925_test2;UID=DB_44925_test2_user;PWD=*******")

Did you have to define the connection to windows?

Bill

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,
Worked great for me.  I added my name to DB.

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!

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

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.

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.